LEFT JOIN и условие OR

Есть две таблицы A и B (достаточно большие 100-200 тысяч записей в каждой). Очень тормозит вот такой запрос:

select *
from A left join B on (A.Id = b.RefA)
where (A.DocNumber = 128) or (B.DocNumber = 128)

<план>
FILTER
  HASH JOIN
    FULL SCAN A
    FULL SCAN B

A.Id - первичый ключ в таблице A, b.RefA - ссылка на A (проиндексирована). Oracle пытается вначале приджойнить таблицы причем не по индексу а HASH JOIN, а потом их фильтровать.
А вот такой запрос не тормозит (т.к. там сперва проверка условий, а потом join по индексу)

select *
from A left join B on (A.Id = b.RefA)
where (A.DocNumber = 128)
union
select *
from A left join B on (A.Id = b.RefA)
where (B.DocNumber = 128)

<план>
UNIQUE SORT
  UNION ALL
    LOOP OUTER
       FULL SCAN A
       объединение с B по индексу
    LOOP
       FULL SCAN B
       объединение с A по индексу

Насколько я понимаю эти запросы эквивалентны. Кто-нибудь знает в чем тут может быть дело? (Oracle вроде умеет преобразовывать запросы с or к union, только вот почему-то делает он это только при inner join, а при left не делает, но в данном случае left join принципиален и его нелзя заменнить на inner).
Версия Oracle 9.2.0.4, 10.2.0.1. Писать (+)= пробовал - не помогает. Наличие или отсутствие индексов на полях DocNumber на сокорость работы существенного влияния не оказывают. Кстати условие ограничения довольно сильное, т.к. в результате получается всего около 100-200 записей.

Вечный вопрос: а таблицы проанализированы ?

dmidek

Вечный вопрос: а таблицы проанализированы ?

Конечно - сбор статистики это у нас регулярная практика (и в планах у индексов пишет везде analyzed). Кроме того - это в разных БД наблюдается.

Эти запросы логически неэквивалентны. Если имеем
(A.DocNumber = 128) and (B.DocNumber = 128)
, то первый запрос вернёт одну сторку, тогда как второй две.
Короче, операции UNION ALL и CONCATENATION это две большие разницы.

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

В первом случае оракл выбрал наиболее удачный с его точки зрения план. Его можно изменить, если добавить хинт use_concat, тем самым свалить запрос на CONCATENATION. Или поиграть со статистикой. Но не факт, что другой план будет лучше.

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

В любом случае в первом запросе у оракла или у администратора больше возможности выбрать лучший план.

eml78

Oracle пытается вначале приджойнить таблицы причем не по индексу а HASH JOIN, а потом их фильтровать.

Предлагаю не выпендриваться с left join, а постаринке написать все условия соединения в секции where.

Внутренний голос мне подсказывает, что на HASH JOIN попадают уже не все записи, а отфильтрованные по A.DocNumber = 128 и B.DocNumber = 128 соответственно. Это можно увидеть в плане со статистикой выполнения.
А FILTER добавлен на всякий случай.

mcureenab

Эти запросы логически неэквивалентны. Если имеем (A.DocNumber = 128) and (B.DocNumber = 128) , то первый запрос вернёт одну сторку, тогда как второй две. Короче, операции UNION ALL и CONCATENATION это две большие разницы.

У автора не UNION ALL, a UNION.
ИМХО эквивалентны.

Попробуй так:

select *
from A left join B on (A.Id = b.RefA)
where (A.DocNumber+0 = 128) or (B.DocNumber+0 = 128)

Сделал testcase и наблюдал отмеченный Вами эффект
Удалось его "сломать" так

select *
from scott.A left join scott.B on (A.Id = b.RefA) 
where a.id > 0 and b.refA > 0
and (A.DocNumber = 128 or B.DocNumber = 128)

План при этом принял такой вид

  Statement Id=3   Type=TABLE ACCESS
  Cost=2  TimeStamp=31-05-06::11::51:56
  
       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 2  Cost: 70
    CONCATENATION
           (5)  NESTED LOOPS 
                Est. Rows: 1
               (2)  TABLE ACCESS FULL SCOTT.B  [Analyzed] 
               (2)   Blocks: 205 Est. Rows: 1 of 99.999  Cost: 2 
                    Tablespace: SYSTEM
               (4)  TABLE ACCESS BY INDEX ROWID SCOTT.A  [Analyzed] 
               (4)   Blocks: 205 Est. Rows: 1 of 99.999  Cost: 33 
                    Tablespace: SYSTEM
                   (3)  UNIQUE INDEX UNIQUE SCAN SCOTT.CST_A  [Analyzed]
           (9)  TABLE ACCESS BY INDEX ROWID SCOTT.B  [Analyzed] 
           (9)   Blocks: 205 Est. Rows: 1 of 99.999  Cost: 2 
                Tablespace: SYSTEM
               (8)  NESTED LOOPS 
                    Est. Rows: 1  Cost: 35
                   (6)  TABLE ACCESS FULL SCOTT.A  [Analyzed] 
                   (6)   Blocks: 205 Est. Rows: 1 of 99.999  Cost: 33 
                        Tablespace: SYSTEM
                   (7)  NON-UNIQUE INDEX RANGE SCAN SCOTT.IDX_B  [Analyzed] 
                        Est. Rows: 1  Cost: 1

и время уменьшилось до 0,25 секунд, стало таким же как при UNION.
В оригинальном варианте было в районе 5 секунд...

mcureenab

eml78 Oracle пытается вначале приджойнить таблицы причем не по индексу а HASH JOIN, а потом их фильтровать. Предлагаю не выпендриваться с left join, а постаринке написать все условия соединения в секции where.

а может наоборот все условия в условия соединения записать?

dmidek

Сделал testcase и наблюдал отмеченный Вами эффект Удалось его "сломать" так select * from scott.A left join scott.B on (A.Id = b.RefA) where a.id > 0 and b.refA > 0 and (A.DocNumber = 128 or B.DocNumber = 128)

тогда уберите left join, он выбирает b.refA is null