364 Глава 7

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

QUERY REWRITE ENABLED=TRUE QUERY REWRITE INTEGRITY=TRUSTED

Необходимо установить эти параметры либо на уровне сеанса с помощью оператора ALTER SESSION, либо на уровне системы в файле параметров инициализации init.ora. Смысл установки параметра QUERY REWRITE ENABLED - разрешить оптимизатору переписывать запрос так, чтобы можно было использовать индекс по функции. Смысл установки параметра QUERY REWRITE INTEGRnY - сообщить оптимизатору, что можно доверять указанному программистом признаку предопределенности результатов выполнения кода (deterministic). (Примеры кода с предопределенным результатом выполнения и смысл этой предопределенности рассматривается ниже.) Если результаты выполнения кода не предопределены (другими словами, возвращает разные результаты при одних и тех же входных данных), полученные по индексу строки могут оказаться некорректными. Предопределенность должен обеспечить разработчик.

После того как все пункты представленного выше списка выполнены, использовать индексы по функции просто - достаточно выполнить оператор CREATE INDEX. Оптимизатор найдет и использует эти индексы автоматически.

Пример использования индекса по функции

Рассмотрим следующий пример. Необходимо выполнить поиск, независимо от регистра символов, по столбцу ENAME таблицы ЕМР. До появления индексов по функции эта задача решалась по-другому. Приходилось добавлять дополнительный столбец в таблицу ЕМР, например UPPER ENAME. Значения в этом столбце поддерживались с помощью триггера на события INSERT и UPDATE, который просто устанавливал :NEW.UPPER NAME := UPPER(:NEW.ENAME). По этому дополнительному столбцу и создавался индекс. Теперь, при наличии индексов по функциям, этот дополнительный столбец не нужен.

Начнем с создания копии демонстрационной таблицы ЕМР в пользовательской схеме SCOTT и добавления в нее множества данных.

tkyte@TKYTE816> create table emp

2 as

3 select * from scott.emp; Table created.

lkyte@TKE816> set timing on tkyte@TKE816> insert into emp

2 select rownum EP

3 substr(object name,l,10) EMIE,

4 substr(object type,1,9) JOB,

5 -rownum MK

6 created hiredate,

Индексы 365

7 rownum SAL,

8 rownum COMM,

9 (mod(rownum,4)+l)*10 DEPTNO

10 from all objects

11 where rowjnum < 10000

12 /

9999 rows created.

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

tkyte@TKYTE816> commit; Commit complete.

Теперь изменим данные в столбце фамилии сотрудника так, чтобы они хранились в смешанном регистре. Затем создадим индекс по функции UPPER от столбца ENAME, создавая по сути индекс, не зависящий от регистра символов в строке:

tkyte@TKYTE816> update emp set ename = initcap(ename); 10013 rows updated.

tkyte@TKYTE816> create index emp upper idx on emp (upper(ename)); Index created.

Наконец, проанализируем таблицу, поскольку, как уже было сказано, для использования индексов по функции надо применять оптимизатор, основанный на стоимости:

tkyte@TKYTE816> analyze table emp compute statistics

2 for table

3 for all indexed columns

4 for all indexes;

Table analyzed.

Теперь имеется индекс по функции UPPER от столбца. Любое приложение, использующее не зависящие от регистра запросы и выполняющееся с соответствующими установками на уровне системы или сеанса, например:

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

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

tkyte@TKYTE816> set autotrace on explain

tkyte@TKYTE816> select ename, empno, sal from emp where upper(ename)=KING;

ENAME EMPNO SAL

King 7839 5000

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=9 Bytes=297)

1 0 TABLE ACCESS (BY INDEX ROWID) OF EMP (Cost=2 Card=9 Bytes = 297)

2 1 INDEX (RANGE SCAN) OF EMP UPPER IDX (NON-UNIQUE) (Cost=l Card=9)

Глава 7

будет использовать этот индекс, что значительно повысит производительность. До появления подобных индексов нужно было просматривать каждую строку в таблице ЕМР, переводить значение столбца в верхний регистр и сравнивать с литералом. Теперь, при наличии индекса по UPPER(ENAME), сервер ищет литерал KING по индексу, просматривая несколько блоков данных, а затем обращается к таблице по идентификатору строки для получения соответствующих данных. Это делается очень быстро.

Рост производительности особенно заметен при индексировании по заданным пользователем функциям от столбцов. Начиная с версии Oracle 7.1, появилась возможность использовать в операторах SQL функции, задаваемые пользователем:

SQL> select my function(ename)

2 from emp

3 where some other function(empno) > 10

Это замечательно, потому что появилась возможность эффективного расширения языка SQL специфическими функциями приложения. К сожалению, однако, производительность при выполнении подобных запросов иногда крайне низка. Предположим, в таблице ЕМР - 1000 строк; тогда функция SOME OTHER FUNCTION по ходу выполнения запроса будет вызываться 1000 раз - по одному разу для каждой строки. Если функция выполняется, например, сотую долю секунды, то этот сравнительно простой запрос будет выполняться не менее 10 секунд.

Вот реальный пример. Я реализовал аналог функции SOUNDEX в языке PL/SQL Мы используем глобальную переменную пакета в качестве счетчика для процедуры - при выполнении запросов, использующих функцию MY SOUNDEX, можно будет определить, сколько раз она вызывалась:

tkyte@TKYTE816> create or replace package stats

2 as

3 cnt number default 0;

4 end;

5 / Package created.

tkyte@TKYTE816> create or replace

2 function my soundex(p string in varchar2) return varchar2

3 deterministic

4 as

5 l return string varchar2(6) default substr(p string, 1, 1) ;

6 l char varchar2(1) ;

7 l last digit number default 0; 8

9 type vcArray is table of varchar2(10) index by binary integer;

10 l code table vcArray;

12 begin

13 stats.cnt := stats.cnt+1; 14

15 l code table(l) := BPFV;

16 1 code table(2) := CSKGJQXZ;