Stumped - Oracle не будет использовать индекс, когда значение указано, но будет, когда функция возвращает то же значение

Question

В настоящее время я работаю с базой данных, которая имеет два индекса для конкретной таблицы. У индекса, который я хочу, есть два столбца: "Имя" (varchar2) и "Время" (число). Когда я пишу запрос

SELECT SOMETHING
  FROM MYTABLE
 WHERE NAME = 'SOME-NAME'
   AND TIME BETWEEN STARTVALUE AND ENDVALUE

(где STARTVALUE и ENDVALUE являются числами) он не использует индекс. Однако, если я использую следующий запрос вместо

SELECT SOMETHING
  FROM MYTABLE
 WHERE NAME = 'SOME-NAME'
   AND TIME BETWEEN MY_FUNC('STARTQUAL') AND MY_FUNC('ENDQUAL')

оно делает.

Единственное различие, о котором я могу подумать, заключается в том, что MY_FUNC явно возвращает значение типа NUMBER - возможно ли, что оптимизатор запросов смущен типом данных для STARTVALUE и ENDVALUE, указанным явно, и отказывается использовать индекс (я видел несколько похожих потоков что упомянул тип конфликт был причиной).

Замечания:

1. Возвращаемое MY_FUNC значение ТОЧНО совпадает со значением, указанным в первом запросе.

2. Рассматриваемый индекс НЕОБХОДИМО (абсолютно не вызывает сомнений) правильный индекс, который нужно использовать, а время выполнения на несколько порядков быстрее, чем при использовании.

3. Я даже указал подсказку запроса с первым запросом, и он отказывается использовать индекс.

Я знаю, что должно быть что-то глупое / простое, что я пропускаю, но я просто не вижу этого.

Заранее спасибо за вашу помощь.

Answer 1

В качестве альтернативы Oracle может оптимизировать запросы по-разному в зависимости от того, включает ли запрос буквальные или связанные значения.

SELECT SOMETHING
  FROM MYTABLE
 WHERE NAME = 'SOME-NAME'
   AND TIME BETWEEN 7 AND 41;

Держу пари, что Oracle что-то знает о распределении данных в TIME столбец, и делает предположение - возможно, используя устаревшую статистику - относительно того, какой процент строк и блоков (то есть селективность) этого столбца. Проверьте, есть ли в этом столбце гистограмма.

Тем не менее, такой запрос:

SELECT SOMETHING
  FROM MYTABLE
 WHERE NAME = 'SOME-NAME'
   AND TIME BETWEEN MY_FUNC('7') AND MY_FUNC('41');

скорее всего будет оптимизирован как семантически эквивалентный:

SELECT SOMETHING
  FROM MYTABLE
 WHERE NAME = 'SOME-NAME'
   AND TIME BETWEEN :some_bind AND :some_other_bind;

Потому что Oracle не знает, что MY_FUNC('7') делает - или даже это MY_FUNC('7') всегда будет возвращать одно и то же значение 7 - если вы не указали Oracle детерминированную функцию. Таким образом, мой опыт показывает, что Oracle в большинстве случаев принимает удар в темноте и предпочитает индекс с высоким коэффициентом кластеризации. Кажется, кажется, что даже если индекс не лучший выбор, по крайней мере, он сводит к минимуму риск падения, посещая как можно меньше блоков данных.

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

alter session set events = '10053 trace name context forever;
run sql
alter session set events = '10053 trace name context off;

Answer 2

SELECT SOMETHING
FROM MYTABLE
WHERE NAME = 'SOME-NAME'
AND TIME BETWEEN STARTVALUE AND ENDVALUE

Здесь у вас есть TIME который является НОМЕРОМ, и STARTVALUE а также ENDVALUE которые являются строками (согласно вашему комментарию). Поэтому выполняется неявное преобразование, т. Е. Ваш запрос эффективно:

SELECT SOMETHING
FROM MYTABLE
WHERE NAME = 'SOME-NAME'
AND TO_CHAR(TIME) BETWEEN STARTVALUE AND ENDVALUE

Если у вас нет индекса на основе функций TO_CHAR(TIME), он не будет использовать индекс.

Поэтому вы должны сказать Oracle, что вы всегда ожидаете, что строковые параметры будут преобразованы в числа, то есть:

SELECT SOMETHING
FROM MYTABLE
WHERE NAME = 'SOME-NAME'
AND TIME BETWEEN TO_NUMBER(STARTVALUE) AND TO_NUMBER(ENDVALUE)

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