Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 cardinality и условие or  [new]
Г_е_с_т
Guest
11gR2. Статистика собрана.
Запрос примерно такой (возвращает несколько десятков тыс. записей)

select COL1, COL2, COL3 from
(select
a.B_ID B_ID,
a.A_COL1,
a.A_COL2,
b.B_COL3,
b.B_FLAG
from a, b
where a.B_ID = b.B_ID(+))
where
(B_ID is null or B_FLAG = 0)

b.B_ID - primary_key
a.B_ID - есть форин кей и индекс (впрочем, здесь он не используется)

Общий смысл - 2 таблицы соединяются по форин кею, а получить хотим либо записи, у которых нет ссылки либо ссылка есть, но флаг в той таблице, на которую ссылаются, равен 0.

В плане в cardinality результирующего оператора вранье - в два раза меньше реального
SELECT 19752
....FILTER
.......HASH JOIN RIGHT OUTER 19752
...........TABLE ACCESS FULL b 287
............TABLE ACCESS FULL a 43689

Точней говоря, на уровне FTS - правда, а начиная с HASH JOIN - неправда.

Если оставлять по одному условию - все в порядке, если разбить на 2 запроса и соединить через union - тоже. Хинты пробовал. План меняют, но не cardinality 19752

Как понять причину неправильной оценки оптимизатором числа строк?
31 мар 10, 21:45    [8561614]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
Г_е_с_т
Guest
Забыл сказать, вместо 19752 правильное значение 42867 строк - т.е. отсеивается в результате условий совсем немного.
31 мар 10, 21:51    [8561638]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Селективность (B_ID is null or B_FLAG = 0) = (селективность B_ID is null) + (селективность B_FLAG = 0) - (селективность B_ID is null and B_FLAG = 0). Оптимизатор не может понять, что (B_ID is null and B_FLAG = 0) ничего не возвращает. Скорее всего нужная cardinality будет при хинте /*+ use_concat */. Но я бы не советовал его использовать, т.к. план будет менее эффективным. Несколько помочь может гистограмма по B_FLAG (если ее еще нет).
Вообще надо определиться, а нужна ли такая точность. Приводит ли эта погрешность к неоптимальному плану?
1 апр 10, 08:49    [8562544]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
mrBampo
Member

Откуда: Киев
Сообщений: 55
Г_е_с_т,

а если запрос переписать так :

select a.A_COL1,
       a.A_COL2,
       b.B_COL3
  from a,
       b
 where a.B_ID = b.B_ID(+)
   and b.B_FLAG(+) = 0

это не удовлетворит вашу потребность?
1 апр 10, 09:46    [8562807]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
JaRo
Member

Откуда:
Сообщений: 1659
wurdu
Оптимизатор не может понять, что (B_ID is null and B_FLAG = 0) ничего не возвращает.
На самом деле странно, почему бы ему не понять, ведь он знает, что B_ID - это PK...
1 апр 10, 09:52    [8562852]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
JaRo
Member

Откуда:
Сообщений: 1659
mrBampo
а если запрос переписать так :
 where a.B_ID = b.B_ID(+)
   and b.B_FLAG(+) = 0
только не так, а так:
where a.B_ID = b.B_ID(+)
  and b.B_FLAG(+) != 0 -- ну если NULL-able, то не забываем NVL
  and b.B_ID is null
1 апр 10, 09:57    [8562890]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
JaRo
wurdu
Оптимизатор не может понять, что (B_ID is null and B_FLAG = 0) ничего не возвращает.
На самом деле странно, почему бы ему не понять, ведь он знает, что B_ID - это PK...
А PK тут не при чем. Он не может понять, что B_ID is null and B_FLAG = 0 это взаимоисключающие условия в связи с outer join. Просто разработчики не заложили такую схему, хотя это очевидно. use_concat развалит запрос на два и там он поймет.
1 апр 10, 10:00    [8562899]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
JaRo
Member

Откуда:
Сообщений: 1659
wurdu
JaRo
wurdu
Оптимизатор не может понять, что (B_ID is null and B_FLAG = 0) ничего не возвращает.
На самом деле странно, почему бы ему не понять, ведь он знает, что B_ID - это PK...
А PK тут не при чем. Он не может понять, что B_ID is null and B_FLAG = 0 это взаимоисключающие условия в связи с outer join.
Как это ни при чем? B_FLAG - это поле той же таблицы, что и B_ID. Мы делаем outer join, что при этом означает B_ID is null - понятно, и также понятно, что при этом с остальными полями той же таблицы. Где тут что-то неочевидное? Я что-то упускаю?
1 апр 10, 10:08    [8562959]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
JaRo
wurdu
JaRo
wurdu
Оптимизатор не может понять, что (B_ID is null and B_FLAG = 0) ничего не возвращает.
На самом деле странно, почему бы ему не понять, ведь он знает, что B_ID - это PK...
А PK тут не при чем. Он не может понять, что B_ID is null and B_FLAG = 0 это взаимоисключающие условия в связи с outer join.
Как это ни при чем? B_FLAG - это поле той же таблицы, что и B_ID. Мы делаем outer join, что при этом означает B_ID is null - понятно, и также понятно, что при этом с остальными полями той же таблицы. Где тут что-то неочевидное? Я что-то упускаю?
Так в запросе автора B_ID из условия это поле из таблицы a. Может конечно автор ошибся, но я исходил из запроса :)
1 апр 10, 10:12    [8562988]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
JaRo
Member

Откуда:
Сообщений: 1659
wurdu
Так в запросе автора B_ID из условия это поле из таблицы a. Может конечно автор ошибся, но я исходил из запроса :)
Блин, вот что значит выкладывать неаккуратно, я так просто решила, что второе where - это опечатка, а должно быть and, а алиас просто забыт, ну а каким он должен быть - вроде было очевидно.

В общем понятно - разобрались.

Но ИМХО автор именно ошибся и ему стоит переписать иначе. Либо так, либо (что ИМХО прозрачнее) так:
select
a.A_COL1,
a.A_COL2,
b.B_COL3
from a, b
where a.B_ID = b.B_ID(+)
and (b.B_ID is null or b.B_FLAG = 0)
ИМХО тут оптимизитор не будет путаться, а смысл запроса остается как и был (если я чего не упускаю)
1 апр 10, 10:23    [8563081]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
Г_е_с_т
Guest
wurdu Так в запросе автора B_ID из условия это поле из таблицы a


Поскольку это поле есть в обеих таблицах (FK <---> PK), я пробовал оба варианта.

В общем понятно - разобрались.


Везет вам. А я так нет. Но все равно за ответы спасибо.

Вариант
where a.B_ID = b.B_ID(+)
and b.B_FLAG(+) != 0 -- ну если NULL-able, то не забываем NVL
and b.B_ID is null
действительно возвращает нужное кол-во строк. Но при этом кардиналити в хэш джойне - ОДЫН.
А должно быть 40 с чем-то тыщ.

В общем, смоделирую на тестовых таблицах (именно что A и B). Но если там будет нормально, для оригинальных таблиц это, скорей всего, не поможет. Тогда просто забуду обо всем этом. Данная непонятка оптимизатора к потере скорости вроде не приводит. Это была часть чужого запроса, который мне дали "глянуть". Я там и так уж отшлифовал все, а эту часть видимо придется оставить пока как есть, так как все равно грядут переделки.
1 апр 10, 13:38    [8565180]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
JaRo
Member

Откуда:
Сообщений: 1659
Г_е_с_т
действительно возвращает нужное кол-во строк. Но при этом кардиналити в хэш джойне - ОДЫН.
А должно быть 40 с чем-то тыщ.
А этот вариант чего показывает?

На всякий случай - Оракл-то в курсе? То бишь статистика собиралась?

ЗЫ: Для выкладывания кода используйте кнопку SRC.
1 апр 10, 14:44    [8565826]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
Г_е_с_т
Guest
1) ни один из предложенных вариантов не возвращает похожее на правду число

2) статистика - так она ж автоматическая, ну я еще dbms_stats.gather_table_stats к указанным таблицам перед проверкой плана применял
1 апр 10, 15:14    [8566134]     Ответить | Цитировать Сообщить модератору
 Re: cardinality и условие or  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Г_е_с_т
...
from a, b
where a.B_ID = b.B_ID(+))
...

b.B_ID - primary_key
a.B_ID - есть форин кей и индекс (впрочем, здесь он не используется)

А можно для самых маленьких нафига внешнее соединение по первичному ключу, если имеется внешний?
2 апр 10, 17:28    [8573452]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить