370 Глава 7

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

tkyte@TKYTE816> analyze table emp compute statistics

2 for table

3 for all indexed columns

4 for all indexes; Table analyzed.

tkyte@TKYTE816> alter session set QUERY REWRITE ENABLED=TRUE; Session altered.

tkyte@TKYTE816> alter session set QUERY REWRITE INTEGRITY=TRUSTED; Session altered.

а затем выполним запрос:

tkyte@TKYTE816> REM reset our counter tkyte@TKYTE816> exec stats.cnt := 0 PL/SQL procedure successfully completed.

tkyte@TKYTE816> set timing on

tkyte@TKYTE816> select ename, hiredate

2 from emp

3 where substr(my soundex(ename),1,6) = my soundex(Kings)

ENAME HIREDATE

King 17-NOV-81

Elapsed: 00:00:00.10

tkyte@TKYTE816> set timing off

tkyte@TKYTE816> set serveroutput on

tkyte@TKYTE816> exec dbms output.put line(stats.cnt);

PL/SQL procedure successfully completed.

Если сравнить оба примера (без индекса и с индексом), окажется:

Действие Без индекса При наличиииндекса Различие Время выполнения

Insert 1.02 5.07 4.05 примерно в 5 раз медленнее

Select 4.57 0.10 4.47 примерно в 46 раз быстрее

Отметим следующие существенные моменты.

Для вставки 10000 записей понадобилось примерно в пять раз больше времени. Индексирование заданной пользователем функции обязательно снизит производительность выполнения вставок и некоторых изменений. Надо понимать, что любой индекс снижает производительность: я выполнил тот же тест без функции MYSOUNDEX, проиндексировав столбец ENAME. При этом операторы INSERT

Индексы 371

выполнялись примерно 2 секунды (дополнительный расход ресурсов связан не только с использованием PL/SQL-функции). Поскольку большинство приложений вставляет и изменяет по одной записи, а для вставки строки необходимо всего 5/10000 секунды, замедление в типичном приложении никто скорее всего и не заметит. Поскольку вставляется строка только один раз, выполнять функцию придется только один раз, а не тысячи раз при выполнении запросов к данным.

Хотя вставка и выполняется в пять раз медленнее, запрос выполняется примерно

в 47 раз быстрее. Функция MYSOUNDEX вычислялась всего два раза вместо 20000. Производительность запроса при наличии индекса и без него несравнима. Кроме того, с ростом размера таблицы, запрос с полным просмотром будет выполняться все дольше. Запрос по индексу всегда будет выполняться примерно за одно и то же время, независимо от размера таблицы.

В запросе приходится использовать функцию SUBSTR. Это не так удобно, как написать WHERE MY SOUNDEX(ename)=MY SOUNDEX( King ), но проблему эту легко обойти, как будет показано ниже.

Итак, вставки замедлились, но запрос выполняется удивительно быстро. Небольшое замедление вставок и изменений с лихвой компенсируется. Кроме того, если столбцы, используемые функцией MY SOUNDEX, не изменяются, то изменения вообще не замедляются (функция MY SOUNDEX вызывается только при изменении столбца ENAME).

Теперь давайте рассмотрим, как добиться, чтобы в запросе не надо б1ло использовать функцию SUBSTR. Требование использовать SUBSTR может провоцировать ошибки: пользователи должны помнить о необходимости выбирать первые 6 символов с помощью SUBSTR. Если они укажут другой размер, индекс не будет использоваться. Хотелось бы также контролировать на сервере количество индексируемых байтов. Это позволило бы при необходимости в дальнейшем изменить функцию MYSOUNDEX так, чтобы она возвращала 7 байт вместо 6. Это можно очень просто сделать, скрыв вызов SUBSTR с помощью представления:

tkyte@TKrE816> create or replace view emp v

2 as

3 select ename, substr(my soundex(ename),1,6) ename soundex, hiredate

4 from emp

View created.

Теперь можно выполнять запросы к представлению:

tkyte@TKYTE816> exec stats.cnt := 0; PL/SQL procedure successfully completed.

tkyte@TKYTE816> set timing on

tkyte@TKYTE816> select ename, hiredate

2 from emp v

3 where ename soundex = my soundex(Kings)

372 Глава 7

ENAME HIREDATE

King 17-NOV-81

Elapsed: 00:00:00.10 tkyte@TKYTE816> set timing off

tkyte@TKYTE816> exec dbms output.put line(stats.cnt) 2

PL/SQL procedure successfully completed.

Используется тот же план выполнения запроса, что и при обращении к базовой таблице. Мы просто скрыли вызов SUBSTR(F(X), 1, 6) в представлении. Оптимизатор все равно распознает, что этот виртуальный столбец на самом деле проиндексирован и делает то, что нужно . Мы получили то же повышение производительности и тот же план выполнения запроса. Использование этого представления - ничем не хуже использования базовой таблицы, а даже лучше, поскольку позволяет скрыть сложности и в дальнейшем легко изменить размер строки, возвращаемой с помощью SUBSTR.

Подводный камень

При использовании индексов по функции я столкнулся с одной проблемой: не удается создать такой индекс по встроенной функции TO DATE. Например:

ops$tkyte@ORA8I.WORLD> create index t2 on t(to date(y,yyyy)); create index t2 on t(to date(y,yyyy))

ERROR at line 1:

ORA-01743: only pure functions can be indexed

Это известная ошибка, которая будет исправлена в следующих версиях Oracle (после 8.1.7). До этого придется создавать собственную интерфейсную функцию для встроенной функции TO DATE и индексировать ее:

ops$tkyte@ORA8I.WORLD> create or replace

2 function my to date(p str in varchar2,

3 p fmt in varchar2) return date

4 DETERMINISTIC

5 is

6 begin

7 return to date( p str, p fmt );

8 end;

9 / Function created.

ops$tkyte@ORA8I.WORLD> create index t2 on t(my to data(y,yyyy)); Index created.

Итак, индексы по функции просты в использовании и реализации, и их применение дает немедленный результат. Их можно использовать для ускорения работы существующих приложений, без изменения их алгоритмов или запросов. Можно получить уско-