То, как использовать функциональный индекс на столбце, который содержит, АННУЛИРУЕТ в Oracle 10 +?
Позволяет просто говорят, что у Вас есть таблица в Oracle:
CREATE TABLE person (
id NUMBER PRIMARY KEY,
given_names VARCHAR2(50),
surname VARCHAR2(50)
);
с этими функциональными индексами:
CREATE INDEX idx_person_upper_given_names ON person (UPPER(given_names));
CREATE INDEX idx_person_upper_last_name ON person (UPPER(last_name));
Теперь, given_names не имеет никаких Нулевых значений, но для пользы аргумента last_name делает. Если я делаю это:
SELECT * FROM person WHERE UPPER(given_names) LIKE 'P%'
объяснить план говорит мне свое использование индекса, но измените его на:
SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%'
это не делает. В документах Oracle говорится, что для использования функционального индекса будет только использоваться, когда несколько условий будут соблюдать, один из которых удостоверяется, нет никаких Нулевых значений, так как они не индексируются.
Я попробовал эти запросы:
SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%' AND UPPER(last_name) IS NOT NULL
и
SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%' AND last_name IS NOT NULL
В последнем случае я даже добавил индекс на last_name, но независимо от того, что я пробую его, использует полное сканирование таблицы. Принятие я не могу избавиться от Нулевых значений, как я заставляю этот запрос использовать индекс на ВЕРХНЕМ (last_name)?
5 ответов
Индекс может использоваться, хотя оптимизатор, возможно, принял решение не использовать его для Вашего конкретного примера:
SQL> create table my_objects
2 as select object_id, object_name
3 from all_objects;
Table created.
SQL> select count(*) from my_objects;
2 /
COUNT(*)
----------
83783
SQL> alter table my_objects modify object_name null;
Table altered.
SQL> update my_objects
2 set object_name=null
3 where object_name like 'T%';
1305 rows updated.
SQL> create index my_objects_name on my_objects (lower(object_name));
Index created.
SQL> set autotrace traceonly
SQL> select * from my_objects
2 where lower(object_name) like 'emp%';
29 rows selected.
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 510 | 355 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 17 | 510 | 355 (1)|
|* 2 | INDEX RANGE SCAN | MY_OBJECTS_NAME | 671 | | 6 (0)|
------------------------------------------------------------------------------------
Документация, которую Вы читаете, по-видимому, указывала, что точно так же, как любой другой индекс все-пустые ключи не хранятся в индексе.
В Вашем примере Вы создали тот же индекс дважды - это дало бы ошибку, таким образом, я предполагаю, что это было ошибкой во вставке, не фактическим кодом, который Вы попробовали.
Я попробовал его
CREATE INDEX idx_person_upper_surname ON person (UPPER(surname));
SELECT * FROM person WHERE UPPER(surname) LIKE 'P%';
и это произвело ожидаемый план запросов:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=67)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PERSON' (TABLE) (Cost=1
Card=1 Bytes=67)
2 1 INDEX (RANGE SCAN) OF 'IDX_PERSON_UPPER_SURNAME' (INDEX)
(Cost=1 Card=1)
Для ответа на вопрос да, это должно работать. Попытайтесь проверить это дважды, Вам действительно создавали второй индекс правильно.
Также попробуйте явную подсказку:
SELECT /*+INDEX(PERSON IDX_PERSON_UPPER_SURNAME)*/ *
FROM person
WHERE UPPER(surname) LIKE 'P%';
Если это работает, но только с подсказкой, то она, вероятно, связана со статистикой CBO, пошедшей не так, как надо, или CBO связанные init параметры.
Вы уверены, что хотите, чтобы индекс использовался? Полные сканирования таблицы не плохи. В зависимости от размера таблицы могло бы быть более эффективно сделать сканирование таблицы, чем использование индекс. Это также зависит от плотности и распределения данных, которые являются, почему статистические данные собраны. Основанному на стоимости оптимизатору можно обычно доверять для совершения правильного выбора. Если у Вас нет проблемы реального исполнения, я не волновался бы слишком много об этом.
Oracle будет все еще использовать функциональные индексы со столбцами, которые содержат пустой указатель - я думаю, что Вы неправильно истолковали документацию.
Необходимо поместить nvl в функциональный индекс, если Вы хотите проверить на это все же.
Что-то как...
create index idx_person_upper_surname on person (nvl(upper(surname),'N/A'));
Можно затем запросить использование индекса с
select * from person where nvl(upper(surname),'N/A') = 'PIERPOINT'
Хотя, все немного ужасные. Так как у большинства людей есть фамилии, возможно, "не пустой" является соответствующим :-).
Можно обойти проблему нулевых значений, являющихся неиндексируемым в этом или других ситуациях, также индексировав на основе литерального значения:
CREATE INDEX idx_person_upper_surname ON person (UPPER(surname),0);
Это позволяет Вам использовать индекс для таких запросов как:
Select *
From person
Where UPPER(surname) is null;
Этот запрос обычно был бы не США индекс, за исключением растровых индексов или индексов включая не допускающий NULL-значения реальный столбец кроме фамилии.