Отправляет 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
Автор
Сергей

Статистика

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

Открыто о СУБД Oracle на русском : IN и EXISTS - продолжаем тему...


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

Выпуск 19

IN и EXISTS - продолжаем тему...

Уважаемые подписчики! Что-то не хватает у меня времени и терпения на изложение формального синтаксиса, так что, продолжим тему, поднятую в предыдущем выпуске. Дадим слово Тому Кайту еще раз...

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

Вы объяснили условия in и exists настолько хорошо (еще никто так этого не делал, - вы, должно быть, счастливый человек, - вы верите в то, что знаниями надо делиться)...

а теперь вы оставляете нам открытый вопрос...

"Помните - это ПРОСТОЕ ПРАВИЛО, а из простых правил есть бесконечно много исключений."

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

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

Таких факторов - бесконечно много, - ввод/вывод может повлиять на результат. Цель оптимизации тоже может повлиять (например, запрос с WHERE EXISTS, в общем случае, будет находить первую строку быстрее, чем IN, но IN будет давать ПОСЛЕДНЮЮ строку (все строки) быстрее, чем where exists). Если цель оптимизации - максимально быстрое получение первой строки, exists может по всем статьям давать преимущество над IN. Если же запрос выполняется пакетным процессом (и принципиально важно быстрее добраться до ПОСЛЕДНЕЙ строки)...

и так далее. Вывод: учитывайте различия этих условий, при настройке попробуйте оба варианта, разберитесь концептуально, что они делают, и тогда сможете маскимально эффективно их использовать.

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

Остаются ли ваши объяснения в силе для запросов с условиями NOT IN и NOT EXISTS?

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

Нет, отчасти потому, что NOT IN и NOT EXISTS не заменяют друг друга! Они имеют разную семантику и могут/будут давать разные результаты. См.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:442029737684

например.

Комментарий В.К.
Можете почитать перевод этого обсуждения в предыдущем выпуске.
Конец комментария

Комментарий читателя от 1 октября 2001 года

Блестящее обсуждение!

Я попытаюсь обобщить то, что вы говорите.

Условия in и exists - взаимозаменяемы, и мы должны использовать то или иное в зависимости от количества строк, возвращаемых внешним и внутренним запросом.

С другой стороны:

Условия not in и not exists - не взаимозаменяемы, и вообще никак не взаимосвязаны. Их использование зависит от того, что именно нужно.

Подводя окончательные итоги, не могли бы вы дать примеры, когда использование not in - оправдано, а not exists - нет.

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

Вы правы в первом утверждении (in/exists).

Второе утверждение - слишком сильное. not in и not exists не вполне взаимозаменяемы. NOT IN отличается от NOT EXISTS, но их результаты совпадают, если подзапрос, используемый в условии NOT IN, не содержит значений NULL.

И not in, и not exists могут быть очень эффективны, когда данные не содержат значения null (а условие not in ПРИ ИСПОЛЬЗОВАНИИ СТОИМОСТНОГО ОПТИМИЗАТОРА - особенно эффективно за счет использования "антисоединения". Подробнее об этом см. в руководстве по настройке производительности). При наличии значений NULL условие not in может быть крайне неэффективным, и многие заменяют его условием not exists (не понимая, что ОТВЕТ просто изменится!!!)

Чудеса с not in и not exists. Комментарий читателя от 1 октября 2001 года

"И not in, и not exists могут быть очень эффективны, когда данные не содержат значения null (а условие not in ПРИ ИСПОЛЬЗОВАНИИ СТОИМОСТНОГО ОПТИМИЗАТОРА - особенно эффективно за счет использования "антисоединения". Подробнее об этом см. в руководстве по настройке производительности). При наличии значений NULL условие not in может быть крайне неэффективным, и многие заменяют его условием not exists (не понимая, что ОТВЕТ просто изменится!!!)"

  1. not in и not exists имеют одинаковую производительность, если в результатах нет значений null.
  2. not in особенно эффективно при использовании стоимостного оптимизатора и осутствии значений null.
  3. А при наличии значений null предпочтительнее использовать not exists, а не not in.

Помоему, все это я понял правильно. Теперь:

  1. Почему not in неэффективно при наличии значений null? Не могли бы вы это продемонстрировать?
  2. И как это not exists "лучше", чем not in при наличии значений null.
  3. Что вы имеете ввиду, утверждая:

    "и многие заменяют его условием not exists (не понимая, что ОТВЕТ просто изменится!!!)"

    Вы хотите сказать, что вместо not in при наличии значений null надо использовать not exists???????

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

1а) not in может выполняться быстрее, чем not exists (или медленнее). Это действительно разные условия. not exists во многом аналогично EXISTS. not in, когда подзапрос не возвращает значений NULL, может быть во многом аналогично IN (выполняется как антисоединение, а не как соединение). Так что, IN иногда эффективнее, чем EXISTS, и наоборот, а NOT IT иногда эффективнее, чем NOT EXISTS (а иногда - нет).

2а) Да, в этом случае это условие наиболее эффективно.

3а) Обычно, да...

1б) Ладно, время продемонстрировать NOT IN и NOT EXISTS (Я подозревал, что рано или поздно это придется сделать!) Поехали:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table big as select * from all_objects;

Table created. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table small as select * from all_objects where rownum < 10;

Table created. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table big modify object_id null;

Table altered. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table small modify object_id null;

Table altered. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> update small set object_id = null where rownum = 1; 

1 row updated. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> create unique index big_idx on big(object_id);

Index created. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> create unique index small_idx on small(object_id);

Index created. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table big compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns;

Table analyzed. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table small compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns;

Table analyzed.

Итак, все готово - таблицы у нас есть. Таблица SMALL будет использоваться в подзапросе. Давайте посмотрим, что теперь будет при использовании условий NOT IN и NOT EXISTS:

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from big
  2   where NOT EXISTS (select null
  3                       from small
  4                      where small.object_id = big.object_id)
  5  / 
  
16886 rows selected. 
  
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=845 Bytes=81965)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'BIG' (Cost=7 Card=845 Bytes=81965)
   3    1     INDEX (UNIQUE SCAN) OF 'SMALL_IDX' (UNIQUE) 
   
Statistics
----------------------------------------------------------
        305  recursive calls
         15  db block gets
      18278  consistent gets
          0  physical reads
          0  redo size
    1961046  bytes sent via SQL*Net to client
     125305  bytes received via SQL*Net from client
       1127  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      16886  rows processed

Итак, для каждой строки в BIG был выполнен поиск по индексу в таблице SMALL, чтобы проверить, существует в ней данная строка или нет. Неплохо... 18 тысяч операций consistent reads (много), но не настолько много:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from big
  2   where object_id NOT IN ( select object_id
  3                              from small )
  4  /

no rows selected 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=845 Bytes=81965)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'BIG' (Cost=7 Card=845 Bytes=81965)
   3    1     TABLE ACCESS (FULL) OF 'SMALL' (Cost=1 Card=1 Bytes=3) 

Statistics
----------------------------------------------------------
          0  recursive calls
     202743  db block gets
      84706  consistent gets
          0  physical reads
          0  redo size
        862  bytes sent via SQL*Net to client
        319  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

Ого, что тут происходит? Итак, для каждой строки в BIG мы полностью просматривали таблицу SMALL. Да, для каждой строки, - мы полностью просмотрели таблицу small около 16000 раз (а если бы она была не такой маленькой...). Обратите внимание также, что запрос вернул 0 строк - данные не найдены. Предыдущий пернул более 16 тысяч - вот вам и влияние значений NULL в подзапросе на условие NOT IN.

Итак, давайте избавимся от этого безобразия (это не обязательно, можно просто использовать соответствующее условие, и мы сделаем это для таблицы BIG в целях демонстрации):

ops$tkyte@ORA817DEV.US.ORACLE.COM> update small set object_id = -1 where object_id is null;

1 row updated. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table small modify object_id NOT NULL;

Table altered. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table small compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns;

Table analyzed. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> Select * from big
  2   where NOT EXISTS (select null
  3                       from small
  4                      where small.object_id = big.object_id)
  5  / 

16886 rows selected. 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=845 Bytes=81965)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'BIG' (Cost=7 Card=845 Bytes=81965)
   3    1     INDEX (UNIQUE SCAN) OF 'SMALL_IDX' (UNIQUE) 

Statistics
----------------------------------------------------------
         71  recursive calls
         15  db block gets
      18270  consistent gets
          0  physical reads
          0  redo size
    1961046  bytes sent via SQL*Net to client
     125305  bytes received via SQL*Net from client
       1127  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      16886  rows processed

Итак, особой разницы нет... Но:

ops$tkyte@ORA817DEV.US.ORACLE.COM> Select * from big b0
  2   where object_id IS NOT NULL
  3     and object_id NOT IN ( select object_id
  4                              from small )
  5  / 

16886 rows selected. 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=845 Bytes=81965)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'BIG' (Cost=7 Card=845 Bytes=81965)
   3    1     INDEX (UNIQUE SCAN) OF 'SMALL_IDX' (UNIQUE) 

Statistics
----------------------------------------------------------
          0  recursive calls
         15  db block gets
      18243  consistent gets
          0  physical reads
          0  redo size
    1961046  bytes sent via SQL*Net to client
     125305  bytes received via SQL*Net from client
       1127  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      16886  rows processed

Ага, уже неплохо. Теперь результаты аналогичны NOT EXISTS. Но подождите, - можно и лучше. Начнем использовать антисоединения...

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set always_anti_join = MERGE;

Session altered. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> Select * from big b1
  2   where object_id is not null
  3     and object_id NOT IN ( select object_id
  4                              from small )
  5  / 

16886 rows selected. 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=269 Card=16885 Bytes=1857350)
   1    0   MERGE JOIN (ANTI) (Cost=269 Card=16885 Bytes=1857350)
   2    1     SORT (JOIN) (Cost=267 Card=16894 Bytes=1638718)
   3    2       TABLE ACCESS (FULL) OF 'BIG' (Cost=7 Card=16894 Bytes=1638718)
   4    1     SORT (UNIQUE) (Cost=3 Card=9 Bytes=117)
   5    4       VIEW OF 'VW_NSO_1' (Cost=1 Card=9 Bytes=117)
   6    5         INDEX (FULL SCAN) OF 'SMALL_IDX' (UNIQUE) (Cost=1 Card=9 Bytes=36) 

Statistics
----------------------------------------------------------
          0  recursive calls
         19  db block gets
        237  consistent gets
        236  physical reads
          0  redo size
    1863304  bytes sent via SQL*Net to client
     125305  bytes received via SQL*Net from client
       1127  SQL*Net roundtrips to/from client
          4  sorts (memory)
          1  sorts (disk)
      16886  rows processed

А вот теперь работы намного меньше - с 18 тысяч значение consistent gets уменьшилось до 237, - неплохо. Антисоединение хэшированием дает аналогичные результаты:

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set always_anti_join = HASH;

Session altered. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> Select * from big b2
  2   where object_id is not null
  3     and object_id NOT IN ( select object_id
  4                              from small )
  5  / 

16886 rows selected. 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=145 Card=16885 Bytes=1857350)
   1    0   HASH JOIN (ANTI) (Cost=145 Card=16885 Bytes=1857350)
   2    1     TABLE ACCESS (FULL) OF 'BIG' (Cost=7 Card=16894 Bytes=1638718)
   3    1     VIEW OF 'VW_NSO_1' (Cost=1 Card=9 Bytes=117)
   4    3       INDEX (FULL SCAN) OF 'SMALL_IDX' (UNIQUE) (Cost=1 Card=9 Bytes=36) 

Statistics
----------------------------------------------------------
          0  recursive calls
         15  db block gets
        237  consistent gets
         31  physical reads
          0  redo size
    2350779  bytes sent via SQL*Net to client
     125305  bytes received via SQL*Net from client
       1127  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      16886  rows processed

2б) - см. 1б)

3б) - см. 1б), где NOT IN возвращает 0 строк, а NOT EXISTS - 16 тысяч. Они не одинаковы, если имеются значения NULL.

Антисоединение и запросы с условиями ANY, SOME, ALL. Комментарий читателя от 2 октября 2001 года

Оптимизация антисоединений (Anti-Joins) и полусоединений (Semi-Joins)

Антисоединение возвращает строки слева от предиката, для которых нет соответствующей строки справа от предиката. То есть, оно возвращает строки, не соответствующие (NOT IN) подзапросу справа. Например, антисоединение может выбрать список сотрудников, не работающих в определенной группе отделов:

SELECT * FROM emp
  WHERE deptno NOT IN
    (SELECT deptno FROM dept
      WHERE loc = 'HEADQUARTERS');

Оптимизатор стандартно использует для подзапросов NOT IN алгоритм вложенных циклов, если только параметр инициализации ALWAYS_ANTI_JOIN не имеет значения MERGE или HASH, и не выполнен ряд обязательных условий, позволяющих преобразовать подзапрос NOT IN в антисоединение сортировкой слиянием или хешированием. Можно поместить подсказку MERGE_AJ или HASH_AJ в подзапрос NOT IN, чтобы указать, какой алгоритм должен использовать оптимизатор.

Полусоединение возвращает строки, соответствующие подзапросу EXISTS, не дублируя строки слева от предиката, если критериям подзапроса соответствует несколько строк справа. Например:

SELECT * FROM dept
  WHERE EXISTS
    (SELECT * FROM emp
      WHERE dept.ename = emp.ename
        AND emp.bonus > 5000);

В этом запросе из dept надо возвращать только одну строку, даже если несколько строк в emp соответствуют подзапросу. Если по столбцу bonus в таблице emp нет индекса, то полусоединение может использоваться для повышения производительности запроса.

Ну и что изменится, если по столбцу bonus есть индекс?

Полусоединение и так выполнялось в представленном выше запросе, правильно?

Как использование подсказок MERGE_SJ или HASH_SJ в подзапросе EXISTS повысит производительность?

О чем говорят оптимизатору эти подсказки?

И, наконец, не могли бы вы дать примеры использования конструкций ANY, ALL и SOME, а именно, когда и как их использовать. Мы эти конструкции никогда не использовали.

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

ANY/SOME во многом аналогично IN.

where x in (select y from t)
то же самое, что: 
where x = ANY(select y from t)

SOME - это псевдоним ANY, ANY - то же самое, что SOME.

Условия any/some могут пригодиться в неравенствах:

scott@ORA817DEV.US.ORACLE.COM> select ename, sal, comm from emp where sal < some(select comm from emp); 

ENAME             SAL       COMM
---------- ---------- ----------
A                 800
WARD             1250        500
MARTIN           1250       1400
ADAMS            1100
JAMES             950
MILLER           1300 

6 rows selected.

Этот запрос находит сотрудников, таких, что у кого-то есть комиссионные, превышающие их зарплату...

ALL - классная штука. Я использую эту конструкцию для поиска наиболее часто встречающихся значений, например:

scott@ORA817DEV.US.ORACLE.COM> select to_char(hiredate,'mm')
  2  from emp
  3  group by to_char(hiredate,'mm')
  4  having count(*) >= ALL (select count(*) from emp group by to_char(hiredate,'mm')); 

TO
--
12

Этот запрос показывает, в каком месяце чаще всего принимали на работу... Он говорит: выбрать месяц, количество записей для которого не меньше ВСЕХ количеств записей по месяцам...

Комментарий читателя от 3 октября 2001 года

Возвращаясь к твоему примеру:

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set always_anti_join = MERGE
  2  /

Session altered. 

ops$tkyte@ORA817DEV.US.ORACLE.COM> Select * from big b1
  2   where object_id is not null
  3     and object_id NOT IN ( select object_id
  4                              from small )
  5  / 

16886 rows selected. 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=269 Card=16885 Bytes=1857350)
   1    0   MERGE JOIN (ANTI) (Cost=269 Card=16885 Bytes=1857350)
   2    1     SORT (JOIN) (Cost=267 Card=16894 Bytes=1638718)
   3    2       TABLE ACCESS (FULL) OF 'BIG' (Cost=7 Card=16894 Bytes=1638718)
   4    1     SORT (UNIQUE) (Cost=3 Card=9 Bytes=117)
   5    4       VIEW OF 'VW_NSO_1' (Cost=1 Card=9 Bytes=117)
   6    5         INDEX (FULL SCAN) OF 'SMALL_IDX' (UNIQUE) (Cost=1 Card=9 Bytes=36)

 Statistics
----------------------------------------------------------
          0  recursive calls
         19  db block gets
        237  consistent gets
        236  physical reads
          0  redo size
    1863304  bytes sent via SQL*Net to client
     125305  bytes received via SQL*Net from client
       1127  SQL*Net roundtrips to/from client
          4  sorts (memory)
          1  sorts (disk)
      16886  rows processed
  1. Будет ли результат таким же, если не будет индекса по столбцу object_id таблицы small?
  2. Ты используешь стоимостной оптимизатор, постоянно, а какие будут результаты сравнения "in и exists" и "not in и not exists" при использовании оптимизатора, основанного на правилах?
  3. После анализа таблиц как переключиться, при желании, на использование оптимизатора, основанного на правилах?
  4. Может ли пригодиться подсказка push_sq (push sub query) в рассмотренных примерах? Когда вообще эта подсказка имеет смысл.
  5. Не по теме: не мог бы ты дать нам ссылку на руководство с описанием ошибок Oracle.
  6. Если конструкция where запроса включает 5 подзапросов, для третьего подзапроса в конструкции where что будет внешним запросом? Как это решается?

    Для пятого подзапроса в конструкции where, мне кажется, внешним будет весь остальной запрос?

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

  1. Да, более-менее, - РЕЗУЛЬТАТ будет таким же (обработано 16886 строк). План будет немного отличаться - FULL SCAN таблицы small вместо index full scan. (У вас есть ВЕСЬ код, чтобы повторить мои тесты - попробуйте!)
  2. Я не рекомендую использовать оптимизатор, основанный на правилах, с 8i - слишком много возможностей будет недоступно. Но, блин, у вас же есть код, попробуйте, попытайтесь (только запаситесь терпением - оптимизатор, основанный на правилах, не настолько "умный", как стоимостной, когда дело доходит до таких запросов, - перечитайте обсуждение, я подчеркивал, что для эффективного выполнения таких запросов НАДО использовать стоимостной оптимизатор!)
  3. Используйте оператор alter session set optimizer_goal или подсказку /*+ RULE */ в запросе.
  4. Подсказка push_subq имеет смысл при оценке способов выполнения подзапросов (по умолчанию, этот способ проверяется последним, если попросите - раньше). Так что, да, она может повлиять на план в данном случае.
  5. Вся документация доступна на сайте:

    http://otn.oracle.com/,
    Oracle error messages manual для разных версий различна. Рекомендую сходить на сайт, пойти по ссылке на документацию, и перейти к версии, которая вас интересует.
  6. Я не понимаю, о какой конструкции идет речь.
Комментарий В.К.
Я выбросил часть обсуждения, связанную с распараллеливанием запросов вообще и подобных подзапросов в частности, как не имеющую отношения к основной теме. Если вам это интересно - читайте оригинал..
Конец комментария

Подзапрос. Комментарий читателя от 6 октября 2001 года

Том,

Предположим, имеется конструкция where с десятью условиями, и каждое условие получает значение из подзапроса. В каком порядке будут выполняться эти подзапросы?

select a from b,c
were b.x=c.x
and b.t=(select t from some ttablle where something)
and c.l=(select l from some xtable where something)

И еще, при обработке c.l=(select l from some xtable where something), будет ли

select a from b,c
were b.x=c.x
and b.t=(select t from some ttablle where something)

считаться внешним запросом, или только

select a from b,c
were b.x=c.x

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

При использовании IN, внешний запрос - это результирующее множество, которое будет соединяться с результатом выполнения IN.

Если подзапрос b.t = (select t ... ) обрабатывается ПЕРВЫМ (до c.l = ( select l...)), он и будет "внешним запросом" по отношению к c.1 = ( select l...).

Однако нельзя полагаться на порядок проверки условий!!! Мы можем и будем изменять его при необходимости.

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

Том и команда,

Большое спасибо. Я люблю ваш сайт.

Я работаю с Oracle 8.1.7.3. Мне нужна помощь при написании запроса с консрукцией NOT IN. Вот как выглядят базовые таблицы:

CREATE TABLE SKU
    (ITEM_ID  NUMBER NOT NULL,
    SKU_CODE  VARCHAR2(10) NOT NULL,
    SKU_DESC  VARCHAR2(255) NOT NULL
  ,
  CONSTRAINT PK_SKU
  PRIMARY KEY (ITEM_ID, SKU_CODE)
  USING INDEX)
/

CREATE TABLE SKU_SOLD_OUT
    (ITEM_ID        NUMBER NOT NULL,
    SKU_CODE        VARCHAR2(10) NOT NULL,
    SOLD_OUT_DATE   DATE NOT NULL,
    DELETED         CHAR(1) NOT NULL,
    SOLD_OUT_TEXT   VARCHAR2(1000) NOT NULL
  ,
  CONSTRAINT PK_SKU_SOLD_OUT
  PRIMARY KEY (ITEM_ID, SKU_CODE, SOLD_OUT_DATE)
  USING INDEX)
/

Для пары item_id, sku_code будет о или много строк в таблице sku_sold_out, но в каждый момент времени активной будет только одна (deleted = 'N').

Мне нужно получить список item-ов, которые не полностью распроданы. Т.е. если для Item_id есть четыре sku_codes, и три уже продано, вернуть item_id. Если все четыре sku_codes проданы, не возвращать item_id.

Вот какой запрос я сейчас использую.

select distinct item_id
from sku
where item_id||'-'||sku_code not in (
    select item_id||'-'||sku_code
    from sku_sold_out
    where deleted = 'N'
     and  sold_out_date <= sysdate)

В sku - около 10000 строк

В sku_sold_out - около 120000 строк.

Нет ли способа получше? Схема зафиксирована и реально используется, так что я не могу менять базу данных, разве что, создать представление и т.п.

Спасибо за помощь.

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

Ну, еще можно вот так:

select distinct item_id
  from sku
  where (item_id,sku_code) not in ( select item_id, sku_code
                                      from sku_sold_out
                                     where deleted = 'N'
                                       and  sold_out_date <= sysdate)
/ 

select distinct sku.item_id
  from sku, sku_sold_out
 where sku.item_id = sku_sold_out.item_id (+)
   and sku.sku_code = sku_sold_out.sku_code (+)
   and nvl(sku_sold_out.deleted,'Y') = 'Y'
   and nvl(sku_sold_out.sold_out_date,sysdate) <= sysdate
/

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

Привет, Том!

В первоначальном объяснении ты написал, что:

"Подзапрос выполняется, выбираются неповторяющиеся значения, результат индексируется (или хешируется, или сортируется) и затем..."

  1. Не мог бы ты объяснить, как может индексироваться результат подзапроса. Создается временный индекс или что-то вроде этого? Что мы увидим в результатах explain plan в этом случае? Никогда о таком не слышал.
  2. Некоторые из показанных тобой планов выполнения запросов имеют вид:
    FILTER
    набор строк 1
    набор строк 2

Я понял, что FILTER - это своего рода "фильтр", убирающий строки, не соответствующие определенному условию. В данном случае, это, очевидно, не так.

Не мог бы ты объянить (или дать ссылку на описание), как работает эта фильтрация двух наборов строк? Мне кажется, что этого рода FILTER работает аналогично вложенным циклам и не допускает распараллеливания, так ли это?

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

1) Они сохраняются отсортированными или хешированными. Сама структура является, своего рода, неявным "индексом". Пусть делается соединение сортировкой слиянием - вы увидите две сорировки (out to temp), а затем слияние. При соединении хешированием, хешируется один из наборов.

Что касается пуyкта 2, я предполагаю, вы имеете ввиду примерно следующее:

ops$tkyte@ORA817DEV.US.ORACLE.COM> Select * from big b0
  2   where object_id IS NOT NULL
  3     and object_id NOT IN ( select object_id
  4                              from small )
  5  /

16886 rows selected. 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=845 Bytes=81965)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'BIG' (Cost=7 Card=845 Bytes=81965)
   3    1     INDEX (UNIQUE SCAN) OF 'SMALL_IDX' (UNIQUE)

FILTER в данном случае, это действительно аналого соединения вложенными циклами. Выполняется полный просмотр таблицы и проверяется условие "is not null". Затем каждая строка должна быть профильтрована через NOT IN, и это делается по индексу.

Это можно распараллелить, если удастся распараллелить полный просмотр.

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

Copyright © 2002 Oracle Corporation

Ну, что, не устали? Я уже немного устал. Обсуждение продолжается (в ноябре посыпались новые вопросы, по теме), так что следите за ним на сайте AskTom. Возможно, мы к нему еще вернемся в будущем.

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

Думаю все же сделать еще один авторский выпуск, посвященный синтаксису. Выпуск выйдет на следующей неделе. Следите за новостями на сайте проекта Open Oracle и пишите ваши комментарии в гостевую книгу сайта OpenXS Initiative.

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

  В.К.

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