Индексы по Функциям

Введение

Эта статья - первая из серии статей о новых функциональных возможностях релиза 8.1 СУБД Oracle 8i. В течение пары месяцев мы обсудим достаточно подробно все 150 с лишним новых возможностей Oracle. В каждой из этих статей мы исследуем некоторые новые особенности с объяснением:

• Зачем использовать эту возможность
• Как надо их использовать
• За и против новых возможностей
• Примеры использования

Oracle8i предоставляет возможность, которая может быть полезна как любому АБД, так и программисту, а именно: возможность индексировать функции и использовать эти индексы в запросах.. Это может позволить Вам осуществить, к примеру, нечувствительный к регистру поиск или сортировку, поиск по сложным выражениям, а также расширить язык SQL своими собственными функциями, чтобы затем осуществлять по ним быстрый поиск.

Зачем использовать эту возможность

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

Итак, почему это просто и обеспечивает прямой доступ к вычисляемому значению? Это просто потому, что выполнить оператор CREATE INDEX достаточно просто. Рассмотрим следующий пример.
Для начала я создам копию демонстрационной таблицы "сотрудники" (emp) в схеме 'scott/tiger'. Далее я изменю данные в этой таблице таким образом, чтобы значения столбца "ename" этой таблицы содержали как строчные, так и прописные буквы. А затем я создам индекс по функции UPPER от этого столбца, нечувствительный к строчным и прописным буквам.

SQL> create table emp as select * from scott.emp;
Table created.

SQL> update emp set ename = initcap(ename);
14 rows updated.

SQL> commit;
Commit complete.

SQL> create index emp_upper_idx on emp(upper(ename));
Index created.

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

SQL> set autotrace on explain
SQL> select ename, empno, sal from emp 
where upper(ename) = 'KING';

ENAME           EMPNO        SAL
---------- ---------- ----------
King             7839       5000


Execution Plan
---------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS 
        (Cost=1 Card=1 Bytes=40)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 
        (Cost=1 Card=1 Bytes=40)
   2    1     INDEX (RANGE SCAN) OF 'EMP_UPPER_IDX' 
        (NON-UNIQUE) (Cost=1 Card=1)

будут теперь использовать этот индекс. Когда этой возможности не было, каждая строка в таблице emp должна была просканироваться, после чего над столбцом ename необходимо было выполнить действие UPPER-CASE, и только после этого сравнить с константой 'KING'. При наличии индекса по upper(ename), запрос сравнивает константу 'KING' непосредственно с индексом, выполняя ранжированное сканирование небольшого объема данных, а затем получает доступ к таблице по rowid. Согласитесь, что это намного быстрее.

Это действие эффективно также в большинстве случаев, когда используются собственные функции от столбцов. Использование собственных функций в SQL-запросах было доступно, начиная с Oracle7.1:

SQL> select my_function(ename)
  2  from emp 
  3  where some_other_function(empno) > 10
  4  /

Это просто здорово, что сейчас можно существенно расширять язык SQL, включая в него специальные функции. Следует отметить, однако, что эффективность приведенных выше запросов может Вас несколько разочаровать. Пусть, к примеру, таблица EMP содержит 1000 строк -- тогда при выполнении запроса функция "some_other_function" должна будет выполниться 1000 раз, по одному разу на строку. Предположим, что выполнение функции занимает 1/100 секунды. Относительно простой запрос будет выполняться не меньше 10 секунд.

Ниже приведен реальный пример. Я модифицировал программу 'soundex' на PL/SQL:

SQL> create or replace package stats
  2  as

/* Пакет stats используется для подсчета количества вызовов функции 'my_sounex'.Он предназначен только для обозначения состояния. Этим же также демонстрируется, что 'чистые' ('purity') ограничения в Oracle8.0 и далее значительно смягчены... Пакет не содержит никакой pragma , и изменение состояния пакета производится функцией в фразе where -- две особенности, которых не было в предыдущих релизах Oracle */

  3          cnt number default 0;
  4  end;
  5  /
Package created.

SQL> create or replace function 
       my_soundex(p_string in varchar2) return varchar2
  2  deterministic
  3  as
  4      l_return_string varchar2(6) default
         substr(p_string,1,1);
  5      l_char      varchar2(1);
  6      l_last_digit    number default 0;
  7
  8      type vcArray is table of varchar2(10) 
        index by binary_integer;
  9      l_code_table    vcArray;
 10
 11  begin
 12      stats.cnt := stats.cnt+1;
 13
 14      l_code_table(1) := 'BPFV';
 15      l_code_table(2) := 'CSKGJQXZ';
 16      l_code_table(3) := 'DT';
 17      l_code_table(4) := 'L';
 18      l_code_table(5) := 'MN';
 19      l_code_table(6) := 'R';
 20
 21
 22      for i in 1 .. length(p_string)
 23      loop
 24          exit when (length(l_return_string) = 6);
 25          l_char := substr(p_string,i,1);
 26
 27          for j in 1 .. l_code_table.count
 28          loop
 29          if ( instr(l_code_table(j),l_char )  0
        AND j < l_last_digit )
 30          then
 31              l_return_string := l_return_string
        // to_char(j,'fm9');
 32              l_last_digit := j;
 33          end if;
 34          end loop;
 35      end loop;
 36
 37      return rpad(l_return_string,6,'0');
 38  end;
 39  /

Function created.

Обратите внимание, что в этой функции я использую новое ключевое слово"deterministic". Детерминистические объявления, как в приведенной выше функции, используются, когда нужно указать, что для одинаковых значений аргументов функция всегда будет возвращать одинаковые выходные значения.. Это ключевое слово необходимо для создания индексов на написанные пользовательские функции. Вы должны сообщить Oracle, что эта функция является детерминистической, т.е. всегда дает один и тот же результат для одинаковых входных значений. Таким образом, предполагается, что Вы не можете создать индекс, используя, например, функцию 'dbms_rand', являющуюся генератором случайных чисел [или функцию, зависящую от sysdate - прим.переводчика ], поскольку они не являются детерминистическими. С другой стороны, функция UPPER является детерминистической, поэтому индекс по функции UPPER от столбца создать можно.

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

SQL> create table test_soundex(name varchar2(30));

Table created.

SQL>
SQL> set timing on
SQL>
SQL> insert into test_soundex
  2  select object_name
  3    from all_objects
  4   where rownum <= 1000;

1000 rows created.

Elapsed: 00:00:00.46
SQL>
SQL> set autotrace on explain
SQL> exec stats.cnt := 0;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> select name
  2    from test_soundex A
  3   where my_soundex(name) = my_soundex('FILE$')
  4  /

NAME
------------------------------
FILE$

Elapsed: 00:00:02.38

Execution Plan
---------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS 
      (Cost=1 Card=1 Bytes=34)
   1    0   TABLE ACCESS (FULL) OF 
      'TEST_SOUNDEX' (Cost=1 Card=1 Bytes=34)




SQL> set autotrace off
SQL> set timing off
SQL> exec dbms_output.put_line( stats.cnt )
2000

PL/SQL procedure successfully completed.

Итак, как мы можем видеть, этот запрос выполняется больше 2 секунд посредством полного сканирования таблицы. Функция my_soundex была вызвана 2000 раз (как показал наш счетчик), по два раза на каждую строку. А теперь посмотрите, как индекс по функции может ускорить этот процесс:

Первое, что нужно сделать сделать, это создать индекс, как показано ниже:

SQL> create index test_soundex_idx 
        on test_soundex( substr(my_soundex(name),1,6) )
  2  /
Index created.

Обратите внимание на интересную вещь: в команде создания индекса используется функция substr. Это нужно потому, что мы индексируем функцию, которая возвращает строку. Для функций, возвращающих число или дату, это не нужно. Смысл использования функции substr заключается в том, что написанная пользователем функция, возвращающая строку, по умолчанию возвращает значение типа VARCHAR2(4000). Это многовато для индекса, поскольку каждая его строка должна уместиться в 1/3 размера блока. Попытавшись создать индекс без использования функции substr, мы получим следующую ошибку:

SQL> create index test_soundex_idx
        on test_soundex(my_soundex(name),1,6);
create index test_soundex_idx 
        on test_soundex(my_soundex(name),1,6)
         *
ERROR at line 1:
ORA-01450: maximum key length (758) exceeded

Даже в базе данных с большим размером блока (например, 16k), все равно невозможно проиндексировать функцию, возвращающую значение типа varchar2(4000).
Итак, при использовании для создания индекса функции, возвращающей символьную строку, необходимо в команде создания индекса ограничить длину возвращаемоой строки. Поскольку нам заранее было известно, что функция my_soundex возвращает строку длиной не более 6 символов, то мы и ограничились этими шестью символами.

Теперь мы готовы к проверке эффективности таблицы с построенным на ней индексом. Для этого нам придется провести мониторинг эффективности индекса на операторах INSERT и SELECT, чтобы определить эффективность каждого из них. Как Вы помните, без индекса наш запрос выполнялся более двух секунд, а вставка 1000 записей - около 1/2 секунды. Посмотрите, как изменилась эффективность после создания индекса:

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

SQL> insert into test_soundex
  2  select object_name
  3    from all_objects
  4   where rownum <= 1000;

1000 rows created.

Elapsed: 00:00:02.23
SQL> exec dbms_output.put_line(stats.cnt)
1000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL>
SQL>
SQL> set autotrace on explain
SQL> exec stats.cnt := 0;

PL/SQL procedure successfully completed.

SQL> select name
  2    from test_soundex  B
  3   where substr(my_soundex(name),1,6) =
         my_soundex('FILE$')
  4  /

NAME
------------------------------
FILE$

Elapsed: 00:00:00.05

Execution Plan
---------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS 
      (Cost=1 Card=1 Bytes=34)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 
      'TEST_SOUNDEX' (Cost=1 Card=1 Bytes=34)
   2    1     INDEX (RANGE SCAN) OF 'TEST_SOUNDEX_IDX' 
      (NON-UNIQUE) (Cost=1 Card=1)


SQL> exec dbms_output.put_line(stats.cnt)
2

Итак, мы получили:
 

Что из этого следует:

• Вставка 1000 записей занимает приблизительно в 5 раз большее время. Индексация пользовательской функции приводит к снижению эффективности вставки и - иногда - обновления данных. Однако следует заметить, что большинство приложений вставляют и обновляют по одной записи за раз, а вставка каждой записи занимает менее 3/1000 секунды. Для обычного приложения не стоит это даже обсуждать. Не забывайте - Вы вставляете запись только один раз, а запрашиваете ее тысячи.
• В то время, как вставка записи выполняется всего в 5 раз медленнее, запрос выполняется быстрее в 47 раз. По эффективности время выполнения запроса к индексированной таблице несравнимо с запросом к неиндексированной. Поскольку размер таблицы постоянно растет, на полное сканирование будет затрачивается все больше и больше времени. Запрос, использующий индекс, будет выполняться приблизительно с одной и той же эффективностью, независимо от размера таблицы.
• В нашем запросе мы использовали выражение "substr()". Это не слишком красиво по сравнению с выражением "where my_soundex(name) = my_soundex( 'FILE$' )", но это можно легко поправить, как быдет показано ниже.

Итак, вставка записей несколько замедлилась, зато запрос работает невероятно быстро. Вознаграждение за некоторое снижение эффективности вставки/обновления огромно. Следует добавить, что обновление столбцов, не используемых функцией, по которой построен индекс, не вызывает перестройки индекса и не замедляет работу.

А сейчас посмотрим, как сделать так, чтобы не возникало необходимости использования функции substr в запросе. Использование substr почти всегда будет вызывать ошибки пользователей, забывших, что substr должна брать только от 1 до 6 символов. Если использовать другой размер, то индекс работать не будет.

Для преодоления этой проблемы можно использовать представление, с помощью которого функция substr будет скрыта от конечного пользователя:

SQL> create or replace view test_soundex_v
  2  as
  3  select name, substr(my_soundex(name),1,6) 
        name_soundex
  4    from test_soundex
  5  /
View created.

SQL> exec stats.cnt := 0;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL> select name
  2    from test_soundex_v B
  3   where name_soundex = my_soundex('FILE$')
  4  /

NAME
------------------------------
FILE$

Elapsed: 00:00:00.04

Execution Plan
--------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS 
      (Cost=1 Card=1 Bytes=34)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 
      'TEST_SOUNDEX' (Cost=1 Card=1 Bytes=34)
   2    1     INDEX (RANGE SCAN) OF 'TEST_SOUNDEX_IDX' 
      (NON-UNIQUE) (Cost=1 Card=1)




SQL> exec dbms_output.put_line(stats.cnt)
2

PL/SQL procedure successfully completed.

Итак, мы спрятали функцию substr( f(x), 1, 6 ) внутрь представления. Оптимизатор опознает ее, как виртуальный столбец, который в действительности является индексированным, и сделает все "как надо". Мы видим такое же улучшение эффективности и такой же план запроса. Использовать это представление так же хорошо, как и базовую таблицу, и даже лучше, поскольку пользователю не нужно знать о спрятанных внутри представления сложностях.

Как это использовать

Выше мы сделали большую часть работы, необходимой для использования индексов по функциям. В дополнение к сделанному отметим, что необходима еще некоторая настройка файла init.ora, а, кроме того, пользователь должен обладать некоторыми привилегиями. Ниже приведен список того, что нужно пользователю для использования индексов по функциям:

• для создания индексов по функциям на таблицы Вашей собственной схемы надо иметь системную привилегию query rewrite;
• для создания индексов по функциям на таблицы из других схем надо иметь системную привилегию global query rewrite;
• для оптимизации использования индексов по функциям необходима установка следующих параметров сессии:

QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED

• Вы должны использовать стоимостной оптимизатор (Cost Based Optimizer). Индексы по функциям имеют смысл только для стоимостного оптимизатора и не используются в случае применения синтаксического оптимизатора (Rule Based Optimizer);
• Вы должны использовать функцию substr() для ограничения длины возвращаемого значения функциями, возвращаемый тип которых VARCHAR2 или RAW.

Как только все перечисленные условия выполнены, можно создавать индекс по функции просто командой "CREATE INDEX". Все остальное сделает за Вас оптимизатор.

За и Против

Здесь приведены некоторые Pros и Cons использования этой новой особенности Oracle:
 

Как Вы можете видеть, в нашем случае "ЗА" в основном значительно перевешивают "ПРОТИВ". Невозможность выполнения прямой загрузки может быть лекго преодолена посредством индексирования после прямой загрузки с включенной опцией "parallel query option". Снижение скорости вставки записей минимально, и для большинства приложений вообще незаметно [надо заметить, что эффект снижения скорости вставки записей наблюдается для любых индексированных таблиц, вне зависимости от того, построен индекс на функцию от какого-то столбца или просто на столбец, так что это рассуждение касается индексов вообще, а не только индексов по функциям - прим.перев ].

Примеры использования этой функциональной возможности

Здесь приведен только один скрипт, использованный в этой статье. Этот скрипт отрабатывает тот пример, который мы рассмотрели выше (с использованием функции 'my_soundex' и пакета 'stats'). Скрипт создает тестовую таблицу 'test_soundex' и загружает в нее первые 1000 строк из системного представления 'all_objects table'. Далее он отрабатывает запросы с использованием индекса по функции и без него. Также он демонстрирует использование представления, с помощью которого можно спрятать встроенной функции substr, примененной к пользовательской функции.

Для правильной работы скрипта необходимо сделать следующее:

• В SQLPLUS должна быть включена опция Autotrace.
• Пользователь, запустивший скрипт, должен иметь системную привилегию rewrite query (включена в роль DBA)

Оригинальный текст скрипта находится по адресу: http://govt.oracle.com/~tkyte/article1/funcidx.sql

В следующий раз...

В следующей статье будет дан обзор новой возможности Fine Grained Access Control, которая позволяет разработчику предоставить конечным пользователям множество представлений на один и тот же объект без использования представлений. Мы узнаем, как ей пользоваться, что может дать ее использование, и как ее использовать.