Привет, Том!
У меня есть текущая таблица
(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,
первый вернет один ответ (ни
одной строки), а остальные
два могут какие-то строки
вернуть.
Так что, я думаю,
последние два запроса вам
подойдут (если в столбце
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
Надеюсь, вы сможете
использовать антисоединение
хешированием и запрос будет
выполняться не более
нескольких минут.
- Комментарий В.К.
- Я позволил себе
выкинуть восторги
вопрошавшего после того,
как, применив
антисоединение
хешированием, он ускорил
выполнение запроса в 270
раз, - теперь результат
получается за 40
секунд...
- Конец комментария
Комментарий читателя от
27 сентября 2002 года
Блестяще, Том!
А что это за hash_aj?
Как оно работает? Это новая
возможность 9i?
Ответ Тома Кайта
Это возможность
стоимостного оптимизатора,
поддерживается уже некоторое
время - пример был для
версии 8.1.7
Привет, Том!
Не мог бы ты объяснить
отличие IN от
EXISTS и NOT IN
от NOT EXISTS. Просто
я читал, что EXISTS
будет работать лучше, чем
IN, а NOT EXISTS
- лучше, чем NOT IN
(читал это в руководстве по
настройке производительности
сервера Oracle).
Ответ Тома Кайта:
На самом деле, что лучше
- зависит от запроса и
данных. Учтите, однако, что
в общем случае 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)
Комментарий читателя от
2 октября 2002 года
Привет, Том!
Отличный ответ. Не мог бы ты
объяснить, почему по условию
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 обычно проверяется
эффективнее.
Если и подзапрос, и
внешняя таблица - огромны,
лучше может быть и один
подход, и другой, - в
зависимости от наличия
индексов и других факторов.
Комментарий читателя от
28 сентября 2001 года
Том,
Не мог бы ты
проиллюстрировать свой ответ
на примере таблиц 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 может быть весьма
эффективным.
Комментарий читателя от
30 сентября 2001 года
А что изменится, если
удалить индексы по таблицам
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. Помните -
это ПРОСТОЕ ПРАВИЛО, а из
простых правил есть
бесконечно много исключений.
- Комментарий В.К.
- Здесь придется
прерваться. Выпуск и так
получается слишком
большим... Мы еще
обязательно вернемся к
обсуждению IN,
EXISTS и
антисоединений
хешированием в одном из
ближайших выпусков.
- Конец комментария
Copyright © 2002 Oracle
Corporation
С наилучшими пожеланиями
