Индексы 379

О При наличии конструкции ON DELETE CASCADE. Например, таблица ЕМР является подчиненной для таблицы DEPT. Оператор DELETE FROM DEPT WHERE DEPTNO = 10 должен вызвать каскадное удаление в таблице ЕМР. Если столбец DEPTNO в таблице ЕМР не проиндексирован, для этого придется выполнить полный просмотр таблицы ЕМР. Этот полный просмотр нежелателен; кроме того, при удалении большого количества строк из главной таблицы подчиненная будет каждый раз полностью просматриваться.

При выполнении запроса от главной таблицы к подчиненной. Рассмотрим пример с таблицами EMP/DEPT еще раз. Очень часто таблица ЕМР запрашивается с условием по столбцу DEPTNO. Если приходится часто выполнять запрос:

select *

from dept, emp

where emp.deptno = dept.deptno and dept.dname = :X;

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

Итак, когда не нужно индексировать внешний ключ? Если выполнены следующие условия:

данные из главной таблицы не удаляются;

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

не выполняется соединение от главной таблицы к подчиненной, т.е. столбцы внешнего ключа не обеспечивают важный способ доступа к подчиненной таблице (как в случае таблиц DEPT и ЕМР).

Если все три условия выполняются, индекс можно не создавать: он только замедлит выполнение операторов ЯМД. Если же какие-то из перечисленных действий выполняются, помните о последствиях.

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

ALTER TABLE <имя подчиненной таблицы> DISABLE TABLE LOCK;

Теперь оператор UPDATE или DELETE, примененный к главной таблице и вызывающий блокирование подчиненной таблицы, приводит к выдаче сообщения:

ERROR at line 1:

ORA-00069: cannot acquire lock - table locks disabled for <имя подчиненной таблицы>

380 Глава 7

Это пригодится при поиске фрагмента кода, делающего то, что не должен (например, изменять или удалять первичный ключ), поскольку пользователи моментально сообщат вам об этом.

Почему мой индекс не используется?

Для этого может быть много причин; мы рассмотрим наиболее типичные.

Случай 1

Используется индекс на основе В*-дерева, и в условии не используются начальные столбцы ключа индекса. В этом случае есть таблица Т и индекс по Т(х,у). Выполняется запрос SELECT * FROM T WHERE Y =5. Оптимизатор скорее всего не будет использовать этот индекс, поскольку в условии не упоминается столбец X, - пришлось бы просматривать все записи индекса. Скорее всего будет выбран полный просмотр таблицы Т. Это не исключает использования индекса в принципе. Если бы выполнялся запрос SELECT X,Y FROM T WHERE Y = 5, оптимизатор учел бы, что для получения значений X и Y обращаться к таблице не придется (эти столбцы входят в индекс), и выбрал бы быстрый просмотр самого индекса, поскольку листовой уровень индекса обычно намного меньше базовой таблицы. Учтите также, что этот способ доступа поддерживает только оптимизатор, основанный на стоимости.

Случай 2

Используется запрос SELECT COUNT(*) FROM T (или аналогичный), и есть индекс на основе В*-дерева по таблице Т. Однако оптимизатор выбирает полный просмотр таблицы вместо подсчета записей индекса (намного меньших). В этом случае индекс, очевидно, создан по столбцам, которые могут содержать пустые значения. Поскольку полностью состоящие из пустых значений записи в индекс не попадают, количество строк в индексе не будет совпадать с количеством строк в таблице. В данном случае оптимизатор поступает правильно: если бы для подсчета строк использовался индекс, результат был бы неправильным.

Случай 3

Запрос обращается к проиндексированному столбцу:

select * from t where f (indexed column) = value

и оказывается, что индекс по столбцу INDEX COLUMN не используется. Это происходит потому, что используется функция от столбца. Индексировались значения столбца INDEX COLUMN, а не значения F(INDEXED COLUMN). Индекс здесь не поможет. Если надо, можно проиндексировать функцию от столбца.

Случай 4

Проиндексирован столбец символьного типа. Этот столбец содержит только числовые данные. Выполняется следующий запрос:

select * from t where indexed column = 5

Индексы

Обратите внимание на число 5 в запросе: это числовой литерал 5 (а не символьная строка). Индекс по столбцу INDEXED COLUMN не используется. Дело в том, что представленный выше запрос эквивалентен следующему:

select * from t where to number(indexed column) = 5

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

ops$tkyte@ORA8I.WORLD> create table t (x char(l) primary key) ; Table created.

ops$tkyte@ORA8I.WORLD> insert into t values (5); 1 row created.

ops$tkyte@ORA8I.WORLD> set autotrace on explain ops$tkyte@ORA8I.WORLD> select * from t where x = 5;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (FULL) OF I

ops$tkyte@ORA8I.WORLD> select * from t where x X

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 INDEX (UNIQUE SCAN) OF SYS C0038216

(UNIQUE)

Как бы то ни было, неявных преобразований надо избегать всегда. Всегда сравнивайте яблоки с яблоками, а апельсины - с апельсинами. Часто такая ситуация наблюдается с датами. Вы пытаетесь выполнить запрос:

- найти see записи за сегодня

select * from t where trunc(date col) = trunc(sysdate);

Оказывается, что индекс по столбцу DATE COL не используется. Можно либо проиндексировать функцию TRUNC(DATE COL), либо, что проще, переписать запрос с помощью оператора сравнения BETWEEN. Следующий пример демонстрирует использование условия BETWEEN для дат. Достаточно понять, что условие:

TRUNC (DATE COL) = TRUNC (SYSDATE)