Oracle 10g - оптимизируйте ГДЕ НЕ НУЛЛ

Ответ 1

Оптимизатор считает, что полное сканирование таблицы будет лучше.

Если есть только несколько строк NULL, оптимизатор прав.

Если вы абсолютно уверены, что доступ к индексу будет быстрее (т.е. у вас будет больше 75% строк с col1 IS NULL), тогда подскажите свой запрос:

SELECT  /*+ INDEX (t index_name_on_col1) */
        *
FROM    mytable t
WHERE   col1 IS NOT NULL

Почему 75%?

Поскольку использование INDEX SCAN для извлечения значений, не охватываемых индексом, подразумевает скрытое соединение на ROWID, которое стоит около 4 раз больше, чем сканирование таблицы.

Если диапазон индексов содержит более чем 25% строк, сканирование таблицы обычно выполняется быстрее.

Как упоминалось в Tony Andrews, фактор кластеризации является более точным методом измерения этого значения, но 25% по-прежнему является хорошим правилом.

Ответ 2

Оптимизатор будет принимать решение на основе относительной стоимости полного сканирования таблицы и использования индекса. Это в основном сводится к тому, сколько блоков нужно будет прочитать для удовлетворения запроса. 25%/75% правило большого пальца, упомянутое в другом ответе, упрощено: в некоторых случаях полное сканирование таблицы имеет смысл даже для того, чтобы получить 1% строк - т.е. Если эти строки будут распространяться по многим блокам.

Например, рассмотрим эту таблицу:

SQL> create table t1 as select object_id, object_name from all_objects;

Table created.
SQL> alter table t1 modify object_id null;

Table altered.

SQL> update t1 set object_id = null
  2  where mod(object_id,100) != 0
  3  /

84558 rows updated.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select count(*) from t1 where object_id is not null;

  COUNT(*)
----------
       861    

Как вы можете видеть, только около 1% строк в T1 имеют ненулевой объект_ид. Но из-за того, как я построил таблицу, эти 861 строки будут распределены более или менее равномерно по таблице. Поэтому запрос:

select * from t1 where object_id is not null;

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

Ключевой статистикой, помогающей идентифицировать эту ситуацию, является фактор кластеризации индекса:

SQL> select clustering_factor from user_indexes where index_name='T1_IDX';

CLUSTERING_FACTOR
-----------------
              460

Это значение 460 довольно велико (по сравнению с 861 строками в индексе) и предполагает, что будет использоваться полное сканирование таблицы. См. эту статью DBAZine о факторах кластеризации.

Ответ 3

Если вы делаете select *, тогда имеет смысл делать сканирование таблицы, а не использовать индекс. Если вы знаете, какие столбцы вам интересны, вы можете создать охваченный индекс с этими коллами плюс тот, который вы используете условие IS NOT NULL.

Ответ 4

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

В большинстве индексов B-дерева не хранятся нулевые записи. Растровые индексы do сохраняют нулевые записи.

Итак, если у вас есть:

выберите * из таблицы где mycolumn имеет значение null

и у вас есть стандартный индекс B-дерева на mycolumn, тогда запрос не может использовать индекс, так как "null" не находится в индексе.

(Если индекс относится к нескольким столбцам, а один из индексированных столбцов не равен нулю, тогда в индексе будет запись).

Ответ 5

Создайте индекс в этом столбце.

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

ocdecio ответил:

Если вы делаете select *, тогда имеет смысл делать сканирование таблицы, а не использовать индекс.

Это не строго верно; индекс будет использоваться, если есть индекс, который соответствует вашему предложению where, и оптимизатор запросов решает использовать этот индекс быстрее, чем выполнять сканирование таблицы. Если индекс отсутствует или нет подходящего индекса, только тогда необходимо выполнить сканирование таблицы.

Ответ 6

Также стоит проверить, актуальна ли статистика Oracle в таблице. Возможно, он не знает, что полное сканирование таблицы будет медленнее.

Ответ 7

База данных Oracle не индексирует нулевые значения вообще в обычных (b-tree) индексах, поэтому не может ее использовать, и вы не можете заставить Oracle использовать ее.

BR

Ответ 8

Использование подсказок должно выполняться только как работа, а не решение.

Как упоминалось в других ответах, нулевое значение недоступно в индексах B-TREE.

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

Это действительно зависит от вашего столбца и характера ваших данных, но обычно, если ваш столбец является типом даты, например:

where mydatecolumn is not null Может быть переведен в правило, говорящее: я хочу, чтобы все строки имели дату.

Тогда вы можете определенно сделать это: где mydatecolumn <= sysdate (in orlele)

Это приведет к возврату всех строк с датой и пропуском нулевых значений при использовании индекса в этом столбце без каких-либо намеков.

Ответ 9

См. http://www.oracloid.com/2006/05/using-index-for-is-null/

Если ваш индекс находится в одном отдельном поле, он НЕ будет использоваться. Попробуйте добавить фиктивное поле или константу в индекс:

create index tind on t(field_to_index, 1);