SQL Multiple минус против производительности нескольких соединений
Я надеюсь, что кто-то может объяснить производительность объединения нескольких таблиц по сравнению с использованием MINUS для удаления записей. Я посмотрел на несколько других вопросов о переполнении стека, но не увидел, что искал.
Я думал, что эти два запроса приведут к одному и тому же результату, и я всегда слышал "используй соединения, используй соединения!", Особенно из сообщений stack overflow, что они должны были быть быстрее...
Это первый запрос, который я запустил, который, по моему мнению, будет намного медленнее, но для его выполнения требуется всего несколько минут...
select some_id
from table1
MINUS
select some_id
from table2
where table2.value = 'some_value'
MINUS
select some_id
from table3
where table3.value = 'some_value'
group by some_id
Это второй запрос, который я думал, будет быстрее, но он работает уже более 3 часов (без конца видно?)
select some_id
from table1
join table2 on table1.id=table2.id
join table3 on table1.id=table3.id
where table2.value = 'some_value'
or table3.value = 'some_value'
group by some_id
Я должен отметить, что все 3 таблицы имеют> 1 миллион записей, до 15 миллионов записей каждая.
РЕДАКТИРОВАТЬ:
Извините - я хотел сообщить вам, что я избегал использования NOT EXISTS в этом вопросе в качестве ответа, так как мне действительно любопытно только эти два сценария.
2 ответа
Во-первых, убедитесь, что у вас есть индексы,
чтобы увидеть план, если он использует полное сканирование таблицы, приступим к созданию индексов, иначе это займет очень много времени.
если у вас есть разработчик plsql, вставьте запрос в окно in sql и нажмите F5, чтобы получить план объяснения.
или может сделать это также,
SCOTT@research 17-APR-15> EXPLAIN PLAN FOR
2 select empno
3 from emp
4 MINUS
5 select empno
6 from empp
7 where empp.empno = '7839'
8 MINUS
9 select empno
10 from emppp
11 where emppp.empno = '7902'
12 group by empno
13 ;
Explained.
SCOTT@research 17-APR-15> SET LINESIZE 130
SCOTT@research 17-APR-15> SET PAGESIZE 0
SCOTT@research 17-APR-15> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 4222598102
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 82 | 10 (90)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | MINUS | | | | | |
| 3 | SORT UNIQUE NOSORT | | 14 | 56 | 2 (50)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
| 5 | SORT UNIQUE NOSORT | | 1 | 13 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMPP | 1 | 13 | 3 (0)| 00:00:01 |
| 7 | SORT UNIQUE NOSORT | | 1 | 13 | 4 (25)| 00:00:01 |
| 8 | SORT GROUP BY NOSORT| | 1 | 13 | 4 (25)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | EMPPP | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("EMPP"."EMPNO"=7839)
9 - filter("EMPPP"."EMPNO"=7902)
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
или если вы хотите использовать автоматическую трассировку, сделайте,
set autotrace on explain
Вот так это будет выглядеть,
SCOTT@research 17-APR-15> select empno
2 from emp
3 MINUS
4 select empno
5 from empp
6 where empp.empno = '7839'
7 MINUS
8 select empno
9 from emppp
10 where emppp.empno = '7902'
11 group by empno
12 ;
EMPNO
----------
234
7499
7521
7566
7654
7698
7782
7788
7844
7876
7900
7934
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4222598102
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 82 | 10 (90)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | MINUS | | | | | |
| 3 | SORT UNIQUE NOSORT | | 14 | 56 | 2 (50)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
| 5 | SORT UNIQUE NOSORT | | 1 | 13 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMPP | 1 | 13 | 3 (0)| 00:00:01 |
| 7 | SORT UNIQUE NOSORT | | 1 | 13 | 4 (25)| 00:00:01 |
| 8 | SORT GROUP BY NOSORT| | 1 | 13 | 4 (25)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | EMPPP | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("EMPP"."EMPNO"=7839)
9 - filter("EMPPP"."EMPNO"=7902)
Note
-----
- dynamic sampling used for this statement (level=2)
SCOTT@research 17-APR-15>
SCOTT@research 17-APR-15> select emp.empno
2 from emp
3 join empp on emp.empno=empp.empno
4 join emppp on emp.empno=emppp.empno
5 where empp.empno = '7839'
6 or emppp.empno = '7902'
7 group by emp.empno
8 ;
EMPNO
----------
7839
7902
Execution Plan
----------------------------------------------------------
Plan hash value: 1435156579
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 8 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 30 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 30 | 7 (15)| 00:00:01 |
| 3 | NESTED LOOPS | | 6 | 102 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPPP | 6 | 78 | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMPP | 10 | 130 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."EMPNO"="EMPP"."EMPNO")
filter("EMPP"."EMPNO"=7839 OR "EMPPP"."EMPNO"=7902)
5 - access("EMP"."EMPNO"="EMPPP"."EMPNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Попробуйте эту версию:
select some_id
from table1
where not exists (select 1 from table2 t2 on t1.id = t2.id and t2.value = 'some_value') or
not exists (select 1 from table3 t3 on t1.id = t3.id and t3.value = 'some_value')
Для лучшей производительности вы хотите индексы на table2(id, value) а также table3(id, value),