Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Октябрь 2002   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : антисоединения хешированием, IN и EXISTS


Информационный Канал Subscribe.Ru

Выпуск 18

Антисоединение хешированием, IN и EXISTS, или настройка одного запроса

Уважаемые подписчики! Этот выпуск, как и было обещано, посвящен хитрым соединениям и запросам. Том Кайт отвечал на исходный вопрос последний раз 27 сентября 2002 года. В обсуждении было затронуто еще несколько тем, сокращенный перевод дискуссий по которым также предлагается.

Настройка SQL-запроса

Привет, Том!

У меня есть текущая таблица (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

Надеюсь, вы сможете использовать антисоединение хешированием и запрос будет выполняться не более нескольких минут.

Комментарий В.К.
Я позволил себе выкинуть восторги вопрошавшего после того, как, применив антисоединение хешированием, он ускорил выполнение запроса в 270 раз, - теперь результат получается за 40 секунд...
Конец комментария

Комментарий читателя от 27 сентября 2002 года

Блестяще, Том!

А что это за hash_aj? Как оно работает? Это новая возможность 9i?

Ответ Тома Кайта

Это возможность стоимостного оптимизатора, поддерживается уже некоторое время - пример был для версии 8.1.7:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76992/hints.htm#21060

Оригинал обсуждения этого вопроса можно найти здесь.

IN или EXISTS, NOT IN или NOT EXISTS

Привет, Том!

Не мог бы ты объяснить отличие 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)

Комментарий читателя от 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

Том,

Не мог бы ты представить пример, в каких ситуациях 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:

  1. увеличивая и уменьшая количество строк в каждой таблице
  2. удаляя и добавляя индексы для обеих таблиц

Так твое объяснение было бы более убедительным. Сделай это, пожалуйста.

Ответ Тома Кайта

Я не собираюсь использовать 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

В следующем выпуске

Думаю снова сделать авторский выпуск, посвященный синтаксису. Впрочем, есть еще одна идея, - описать, как из СУБД Oracle обратиться к источнику данных ODBC. Как оказалось, сделать это вовсе не сложно. Тема эта была затронута в гостевой книге сайта OpenXS Initiative.

Выпуск выйдет в конце следующей недели. Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное