Как не странно, но план выполнения запроса на основе индекса может быть хуже, чем TABLE FULL SCAN. Это связано с тем, что при работы с индексом мы несем дополнительные затраты на зачитывание блоков данных индекса, это первое. Второе, даже блоки данных самих таблиц могут зачитываться несколько раз за время выполнения запроса. Например, в одном блоке данных лежат записи из начала таблицы (по индексу), и из конца таблицы. Блок будет зачитан и помещен в буфер для работы с первой записью по индексу. Ко времени обращения к записи из конца таблицы, блок из буфера уже может уйти, поэтому потребуется еще раз его зачитывать.
Оптимизатор выполнения запросов оценивает селективность запросов. Селективность запроса обозначает кол-во записей, который вернет запрос. Запрос возвращает одну запись - высокая селективность, половину таблицы - низкая селективность. Самая высокая селективность - запрос по первичному ключу.
Кроме того, оптимизатор оценивает селективность условия запроса по значению. Именно для этих целей собирается статистика и строятся гистограммы распределения значений. Если мы задаем условие по значению поля, которое содержится в скажем в 50% записей в таблице, то селективность этого условия низкая. Если даже по такому полю есть индекс, он использоваться не будет - мы и так зачитаем практически все блоки данных таблицы, а с индексом - еще и блоки данных индекса.
Теперь вернемся к запросу
Code:
SELECT
"HEADER","OBJECT_TYPE","PARTNER_NO"
FROM
"CRMD_ORDER_INDEX"
WHERE
"CLIENT"=:A0 AND "HEADER" IN
(:A1,:A2,:A3,:A4,:A5,:A6,:A7,:A8,:A9,:A10,:A11,:A12,:A13,:A14,:A15,:A16,:A17,:A18,:A19,:A20)
Вот что здесь может определить оптимизатор? Тут нет конкретных значений, строить план на основе конкретных значений не получиться. Тем не менее, скорее всего можно определить, что селективность выборка по полю CLIENT будет очень низкая. По полю HEADER идет перечисление. Оптимизатор каким-то образом прогнозирует кол-во возвращаемых записей, и на основе этой информации решает что проще использовать TABLE FULL SCAN.
Кстати, обратите внимание, что прогнозируемая IO-Costs во втором и третьем случае одинаковая. То есть, оптимизатор не видит ухудшения операций чтения от перехода на TABLE FULL SCAN.
Цитата:
почему так резко меняется поведение запроса при вводе 10 guid'ов вместо 9.
Вроде в самом ORACLE есть какая настройка, которая управляет разбором перечислений от количества. Мне кажется я видел что-то подобное в доках. Но ручаться не буду, давно это было.