Индексы

17 18 19 20

21 22

23 24

25 26 27 28 29 30 31 32 33

35 36 37 38 39 40

l code table(3) := DT; l code table(4) := L; l code table (5) := MN; l code table(6) := R;

for i in 1 . . length(p string) loop

exit when (length (l retum string)=6); l char := upper(substr( p string, i, 1 ));

for j in 1 .. l code table. count loop

if (instr(l eode table(j), l char) > 0 AND j О l last digit)

then

l retum string := l retum string to char(j,fm9);

l last digit := j;

end if;

end loop; end loop;

return rpad(l retum string, 6, 0);

end;

Function created.

Обратите внимание, что в этой функции использовано новое ключевое слово, DETERMINISTIC. Оно означает, что данная функция при одних и тех же входных данных всегда даст одинаковый результат. Это необходимо указать при создании индекса по функции, заданной пользователем. Необходимо сообщить серверу Oracle, что результат выполнения функции предопределен (DETERMINISTIC) и она будет всегда давать одинаковые результаты при одинаковых входных данных. Это ключевое слово тесно связано с установкой QUERY REWRITE INTEGRITY=TRUSTED на уровне

системы или сеанса. Мы сообщаем серверу Oracle, что можно быть уверенным в одинаковости результатов функции при одних и тех же входных данных, независимо от последовательности вызовов. В противном случае при доступе к данным по индексу и путем полного просмотра таблицы могли бы получаться разные результаты. Предопределенность означает, например, что нельзя создавать индекс по функции DBMS RANDOM.RANDOM генератору случайных чисел - при тех же входных данных она дает случайные результаты. Результат встроенной функции SQL, UPPER, использованной в первом примере, предопределен, поэтому по функции UPPER от столбца индекс создать можно.

Теперь давайте разберемся, какую производительность будет иметь функция MY SOUNDEX при отсутствии индекса. Используем созданную ранее таблицу ЕМР содержащую примерно 10000 строк:

tkyte@TKYTE816> КЕМ reset our counter tkyte@TKYTE816> exec stats.cnt := 0

Глава 7

PL/SQL procedure successfully completed.

tkyte@TKYTE816> set timing on tkyte@TKYTE816> set autotrace on explain tkyte@TKYTE816> select ename, hiredate

2 from emp

3 where my soundex(ename) = my soundex(Kings)

4 / ENAME HIREDATE

King 17-NOV-81 Elapsed: 00:00:04.57

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=101 Bytes=16

1 0 TABLE ACCESS (FULL) OF EMP (Cost=12 Card=101 Bytes=1616)

tkyte@TKYTE816> set autotrace off tkyte@TKYTE816> set timing off

tkyte@TKYTE816> set serveroutput on

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

PL/SQL procedure successfully completed.

Итак, запрос выполняется (путем полного просмотра таблицы) более четырех секунд. Функция MY SOUNDEX б]ла вызвана более 20000 раз (как показывает счетчик), дважды для каждой строки. Давайте посмотрим, как добавление индекса по функции позволит ускорить работу.

Сначала создадим индекс следующим образом:

tkyte@TKYTE816> create index emp soundex idx on

2 emp(substr(my soundex(ename),1,6))

Index created.

Обратите внимание, что в этом операторе создания индекса используется функция SUBSTR. Дело в том, что индексируется функция, возвращающая строку. Если бы индексировалась функция, возвращающая число или дату, эта функция SUBSTR не понадобилась бы. Применять функцию SUBSTR к заданным пользователем функциям, возвращающим строки, необходимо потому, что они возвращают данные типа VARCHAR2(4000). Это слишком большое значение для индексирования - запись индекса должна помещаться в треть блока. Если попытаться обойтись без SUBSTR, будет получено следующее сообщение (в базе данных с размером блока 8 Кбайт):

tkyte@TKYTE816> create index emp soundex idx on emp(my soundex(ename)); create index emp soundex idx on emp(my soundex(ename))

ERROR at line 1:

ORA-01450: maximum key length (3218) exceeded

Индексы 369

В базах данных с другим размером блока может быть выдано другое значение вместо 3218, но пока используются блоки размером менее 16 Кбайт, создать индекс по дан-н1м типа столбцу VARCHAR2(4000) не удастся.

Итак, чтобы проиндексировать заданную пользователем функцию, возвращающую строку, необходимо ограничить тип возвращаемого значения в операторе CREATE INDEX. В рассмотренном выше примере, поскольку функция MYSOUNDEX возвращает не более 6 символов, мы выбираем подстроку из первых шести символов.

Теперь все готово для оценки производительности при наличии индекса. Проследим последствия добавления индекса при выполнении операторов INSERT, а также ускорение выполнения операторов SELECT. Пока индекса не было, запросы выполнялись более четырех секунд, а вставка 10000 строк потребовала около одной секунды. Протестируем снова:

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

tkyte@TKYTE816> set timing on tkyte@TKYTE816> truncate table emp; Table truncated.

tkyte@TKYTE816> insert into emp -rovjnum EMPNO,

initcap( substr(object name,l,10)) ENAME, substr(object type,1.9) JOB, -rovjnum MGR, created hiredate, rovjnum SAL, rownum COMM,

(mod(rownum,4)+l)*10 DEPTNO all objects ownum < 10000 ll

impno, initcap(ename), job, mgr, hiredate, sal, comm, deptno scott.emp

10013 rows created.

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

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

10013

PL/SQL procedure successfully completed.

Итак, на этот раз выполнение операторов INSERT потребовало около 5 секунд. Причина - дополнительные расходы ресурсов на поддержку нового индекса по функции MY SOUNDEX, - как на обычные действия по поддержке индекса (добавление любого индекса замедляет вставки), так и на вызов хранимой функции 10013 раз, как показывает значение переменной stats.cnt.