Уважаемые подписчики! Этот выпуск, как и было обещано, посвящен хитрым соединениям и запросам. Том Кайт отвечал на исходный вопрос последний раз 27 сентября 2002 года. В обсуждении было затронуто еще несколько тем, сокращенный перевод дискуссий по которым также предлагается.
Привет, Том!
У меня есть текущая таблица (Investor) с записями клиентов и другая, итоговая,
таблица (Invdatew), с теми же записями, содержащими дополнительный столбец даты.
В конце каждого дня я, с помощью пакета dbms_job, вставляю данные из
текущей таблицы в итоговую, добавляя в качестве даты значение sysdate.
Но это выполняется настолько долго, что на вставку 36 строк (новые записи за день)
уходит примерно три часа. Не мог бы ты помочь мне настроить этот запрос.
SQL> insert into invdatew
select inv, name, nama, type, clas, stat, act, rbrk, rest, reme,
adde, adda, cnum, dload, sysdate from investor
where inv not in
(select inv from invdatew);
36 rows created.
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=170 Card=3672 Bytes=168912)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'INVESTOR' (Cost=170 Card=3672 Bytes=168912)
3 1 TABLE ACCESS (FULL) OF 'INVDATEW' (Cost=196 Card=2614 Bytes=33982)
Statistics
----------------------------------------------------------
0 recursive calls
284958 db block gets
47270538 consistent gets
42091341 physical reads
4528 redo size
886 bytes sent via SQL*Net to client
808 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
36 rows processed
Спасибо
Ну, при отсуствии определений таблиц приходится гадать, но я думаю, что столбец inv задан как допускающий значения NULL, что не позволяет серверу выполнять ряд оптимизаций. Ниже представлен пример, в котором я сравниваю результаты трех способов вставки и их производительность. Они должны вам помочь. Я рекомендую метод антисоединения хешированием (hash anti-join), как, вероятно, наиболее оптимальный. Фактически, все зависит от размера таблиц. Я также предполагаю наличие индекса по таблице invdatew для запроса NOT EXISTS, - если индекса нет, лучше и не пытайтесь его выполнять!
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table invdatew 2 as 3 select 1 inv, a.*, sysdate dt from all_objects a where 1=0; Table created. ops$tkyte@ORA817DEV.US.ORACLE.COM> create table investor 2 as 3 select 1 inv, a.* from all_objects a where 1=0; Table created. ops$tkyte@ORA817DEV.US.ORACLE.COM> create index invdatew_idx on invdatew(inv); Index created. ops$tkyte@ORA817DEV.US.ORACLE.COM> exec gen_data( 'INVDATEW', 5000); PL/SQL procedure successfully completed. ops$tkyte@ORA817DEV.US.ORACLE.COM> exec gen_data( 'INVESTOR', 5000); PL/SQL procedure successfully completed. ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table invdatew compute statistics for table for all indexes for all indexed columns; Table analyzed. ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table investor compute statistics for table for all indexes for all indexed columns; Table analyzed. ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true; Session altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into invdatew 2 select a.*, sysdate from investor a 3 where inv not in (select inv from invdatew); 5000 rows created. ops$tkyte@ORA817DEV.US.ORACLE.COM> rollback; Rollback complete. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into invdatew 2 select a.*, sysdate from investor a 3 where inv is not null 4 and inv not in (select /*+ HASH_AJ */ inv from invdatew where inv is not null); 5000 rows created. ops$tkyte@ORA817DEV.US.ORACLE.COM> rollback; Rollback complete. ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into invdatew 2 select a.*, sysdate from investor a 3 where not exists ( select * 4 from invdatew 5 where invdatew.inv = a.inv ); 5000 rows created. ops$tkyte@ORA817DEV.US.ORACLE.COM> rollback; Rollback complete. ops$tkyte@ORA817DEV.US.ORACLE.COM>
Все три запроса - различны, в том смысле, что если столбец INV в таблице invdatew имеет значения NULL, первый вернет один ответ (ни одной строки), а остальные два могут какие-то строки вернуть. Причины этого описаны здесь:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:442029737684(см. сокращенный перевод этого обсуждения далее - В.К.)
Так что, я думаю, последние два запроса вам подойдут (если в столбце inv таблицы invdatew окажется значение NULL, вы всегда будете получать НОЛЬ строк, - не то, что нужно, правда?)
Посмотрев на результаты tkprof для этих вставок, можно увидеть весьма различающиеся характеристики производительности:
insert into invdatew select a.*, sysdate from investor a where inv not in (select inv from invdatew) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.02 0 0 0 0 Execute 1 85.64 86.08 0 1974190 31754 5000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 85.66 86.10 0 1974190 31754 5000 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 511 Rows Row Source Operation ------- --------------------------------------------------- 5001 FILTER 5001 TABLE ACCESS FULL INVESTOR 5000 TABLE ACCESS FULL INVDATEW
Этот запрос обрабатывался следующим образом:
для каждой строки в investor
loop
полный просмотр INVDATEW, чтобы проверить, что INV там нет
end loop
другими словами, 5000 полных просмотров таблицы INVDATEW (5000 = количество строк в таблице investor!)
insert into invdatew select a.*, sysdate from investor a where inv is not null and inv not in (select /*+ HASH_AJ */ inv from invdatew where inv is not null ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.67 3.84 0 550 10631 5000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.68 3.85 0 550 10631 5000 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 511 Rows Row Source Operation ------- --------------------------------------------------- 5001 HASH JOIN ANTI 5000 TABLE ACCESS FULL INVESTOR 5000 VIEW VW_NSO_1 5000 INDEX FAST FULL SCAN (object id 44573)
Ух ты, вот это разница: вместо 85 секунд процессорного времени - всего .6, вместо 86 секунд выполнения - менее 4 секунд.
Это запрос обрабатывался примерно так:
для каждой строки во внешнем соединении investor с invdatew
если invdatew.inv is null, то вставить эту запись
Намного эффективнее...
insert into invdatew
select a.*, sysdate from investor a
where not exists ( select *
from invdatew
where invdatew.inv = a.inv )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.72 4.32 0 10672 10623 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.72 4.32 0 10672 10623 5000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 511
Rows Row Source Operation
------- ---------------------------------------------------
5001 FILTER
5001 TABLE ACCESS FULL INVESTOR
5000 INDEX RANGE SCAN (object id 44573)
Да, не так хорошо, как hash_aj, но... очень и очень близко. Я бы избегал этого подхода по причине большого количества логических операций ввода/вывода, - лучше выполнять 550 операций, чем 10672. Запрос обрабатывался следующим образом:
для каждой записи в investor
loop
выполнить подзапрос, использующий индекс для поиска строка в invdatew
if не найдено
then вставить строку
end if;
end loop
Надеюсь, вы сможете использовать антисоединение хешированием и запрос будет выполняться не более нескольких минут.
Блестяще, Том!
А что это за hash_aj? Как оно работает? Это новая возможность 9i?
Это возможность стоимостного оптимизатора, поддерживается уже некоторое время - пример был для версии 8.1.7:
Оригинал обсуждения этого вопроса можно найти здесь.
Привет, Том!
Не мог бы ты объяснить отличие IN от EXISTS и NOT IN от NOT EXISTS. Просто я читал, что EXISTS будет работать лучше, чем IN, а NOT EXISTS - лучше, чем NOT IN (читал это в руководстве по настройке производительности сервера Oracle).
См.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:953229842074(см. сокращенный перевод этого обсуждения далее - В.К.)
На самом деле, что лучше - зависит от запроса и данных. Учтите, однако, что в общем случае NOT IN и NOT EXISTS - не одно и то же!!!
SQL> select count(*) from emp where empno not in ( select mgr from emp );
COUNT(*)
----------
0
просто НЕТ строк, таких что сотрудник не явлется менеджером: все - менеджеры (не так ли?)
SQL> select count(*) from emp T1
2 where not exists ( select null from emp T2 where t2.mgr = t1.empno );
COUNT(*)
----------
9
Ага, а теперь, оказывается, 9 сотрудников менеджерами не являются. Учитывайте особенности обработки значений NULL в условиях NOT IN!! (вот почему условий NOT IN иногда избегают).
NOT IN может быть не менее эффективно, чем NOT EXISTS, - и даже на несколько порядков лучше, - если можно использовать "антисоединение" (если подзапрос точно не возвращает значений NULL)
Привет, Том!
Отличный ответ. Не мог бы ты объяснить, почему по условию NOT IN запись
со значением NULL не выбирается?
Потому что NULL означает... хм..., не знаю, что. (Буквально, null означает Неизвестно).
Поэтому предикат
where x not in (NULL)
не возвращает ни TRUE, ни FALSE.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dual where dummy not in (NULL); no rows selected ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dual where NOT(dummy not in (NULL)); no rows selected
(вы могли бы подумать, что хоть один из двух запросов должен вернуть строку, но у булева типа в sql есть третье значение - "не знаю")
Оригинал обсуждения этого вопроса можно найти здесь.
Том,
Не мог бы ты представить пример, в каких ситуациях IN лучше,
чем EXISTS, и наоборот.
Запросы с этими условиями выполняются очень по-разному.
Select * from T1 where x in (select y from T2)
select * from t1, (select distinct y from t2) t2 where t1.x = t2.y;
Обычно выполняется подзапрос, выбираются уникальные значения, индексируются (или хешируются, или сортируются), а затем результат соединяется с исходной таблицей.
В отличие от запроса:
select * from t1 where exists (select null from t2 where y = x)
for x in (select * from t1)
loop
if (exists (select null from t2 where y = x.x))
then
ВЫДАТЬ ЗАПИСЬ
end if
end loop
Он всегда приводит к полному просмотру таблицы T1, тогда как первый запрос может использовать индекс по T1(x).
Итак, когда же использовать exists, а когда - in?
Пусть результат выполнения подзапроса
(select y from T2)
"большой" и получается долго. А таблица T1 - сравнительно маленькая, и (select null from t2 where y = x.x) выполняется очень-очень быстро (есть хороший индекс по t2(y)). Тогда запрос с exists будет выполняться быстрее, потому что время на полный просмотр таблицы T1 и выбор из T2 по индексу может быть меньше, чем время полного просмотра T2 для построения подзапроса, по которому мы получаем уникальные значения.
Если результат подзапроса - небольшой, то условие IN обычно проверяется эффективнее.
Если и подзапрос, и внешняя таблица - огромны, лучше может быть и один подход, и другой, - в зависимости от наличия индексов и других факторов.
Том,
Не мог бы ты проиллюстрировать свой ответ на примере таблиц emp и
dept:
Так твое объяснение было бы более убедительным. Сделай это, пожалуйста.
Я не собираюсь использовать EMP и DEPT, поскольку для иллюстрации сказанного пришлось бы генерировать массу данных для этих таблиц (если хотите, сделайте это сами ;)
Я буду использовать таблицы BIG и SMALL для иллюстрации.
Я выполнил:
create table big as select * from all_objects; insert /*+ append */ into big select * from big; commit; insert /*+ append */ into big select * from big; commit; insert /*+ append */ into big select * from big; create index big_idx on big(object_id); create table small as select * from all_objects where rownum < 100; create index small_idx on small(object_id); analyze table big compute statistics for table for all indexes for all indexed columns / analyze table small compute statistics for table for all indexes for all indexed columns /
Так что в таблице small - 99 строк, а в big - более 133000
select count(subobject_name)
from big
where object_id in (select object_id from small)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 0 993 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.03 0 993 0 1
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
792 MERGE JOIN
100 SORT (JOIN)
100 VIEW OF 'VW_NSO_1'
99 SORT (UNIQUE)
792 INDEX GOAL: ANALYZED (FULL SCAN) OF 'SMALL_IDX'
(NON-UNIQUE)
891 SORT (JOIN)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BIG'
select count(subobject_name)
from big
where exists (select null from small where small.object_id = big.object_id)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 4.12 4.12 0 135356 15 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 4.12 4.12 0 135356 15 1
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
792 FILTER
135297 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BIG'
133504 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'SMALL_IDX'
(NON-UNIQUE)
Это показывает, что если внешний запрос - "большой", а внутренний - "маленький", IN обычно эффективнее, чем EXISTS.
Теперь:
select count(subobject_name)
from small
where object_id in (select object_id from big)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.51 0.82 50 298 22 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.52 0.83 50 298 22 1
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
99 MERGE JOIN
16913 SORT (JOIN)
16912 VIEW OF 'VW_NSO_1'
16912 SORT (UNIQUE)
135296 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'BIG_IDX'
(NON-UNIQUE)
99 SORT (JOIN)
99 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SMALL'
select count(subobject_name)
from small
where exists (select null from big where small.object_id = big.object_id)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 204 12 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 204 12 1
EGATE)
99 FILTER
100 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SMALL'
99 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'BIG_IDX' (NON-UNIQUE)
показывает, что если внешний запрос - "маленький", а внутрениий - "большой", условие WHERE EXISTS может быть весьма эффективным.
А что изменится, если удалить индексы по таблицам small и big?
Попробуйте, и узнаете. Весь необходимый код уже есть - такого рода проверки теперь выполнить будет легко.
Результат, однако, легко предположить.
Запрос "select * from big where object_id in (select object_id from small)", скорее всего, будет один раз сортировать BIG, один раз сортировать SMALL, а потом соединять результаты (это называется соединение сортировкой слиянием - sort merge join).
Запрос "select * from big where exists (select null from small where small.object_id = big.object_id)", скорее всего, будет выполняться путем однократного полного просмотра big, а ДЛЯ КАЖДОЙ СТРОКИ big будет полностью просматриваться таблица small.
(После проверки: я проверил, и оказалось, что простое правило" работает. Большой внешний запрос и маленький внутренний = IN. Маленький внешний запрос и большой внутренний = EXISTS. Помните - это ПРОСТОЕ ПРАВИЛО, а из простых правил есть бесконечно много исключений.
Оригинал обсуждения этого вопроса можно найти здесь.
Copyright © 2002 Oracle Corporation
Думаю снова сделать авторский выпуск, посвященный синтаксису. Впрочем, есть еще одна идея, - описать, как из СУБД Oracle обратиться к источнику данных ODBC. Как оказалось, сделать это вовсе не сложно. Тема эта была затронута в гостевой книге сайта OpenXS Initiative.
Выпуск выйдет в конце следующей недели. Следите за новостями на сайте проекта Open Oracle.
С наилучшими пожеланиями,
В.К.
|
|