Function Based Indexes, by Tomas Kyte
Эта статья впервые была размещена на сайте www.oramag.ru

От редакции Russian Oracle Magazine

В этом номере ROIM с любезного разрешения автора начинается публикация серии статей Т.Кайта (Tomas Kyte), известного автора и ведущего колонки "Ask Tom" журнала Oracle Magazine. Не часто можно встретить столь глубокое знание предмета и искреннее желание поделиться с читателями. Мы продолжим перевод и публикацию работ Т.Кайта в следующих номерах журнала. Самые нетерпеливые наши читатели могут ознакомиться с творчеством Т.Кайта на его личном сайте http://govt.oracle.com/~tkyte , могут обратиться к нему с вопросами в Oracle Magazine http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.main. Я хотел бы, чтобы Вы при чтении этих статей испытали такую же радость, что и я при подготовке их к печати.

Введение

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

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

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

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.

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)

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

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

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

SQL> create or replace package stats
  2  as

  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.

Сейчас, когда у нас есть функция "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.

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

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

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

Теперь мы готовы к проверке эффективности таблицы с построенным на ней индексом. Для этого нам придется провести мониторинг эффективности индекса на операторах 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.

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

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

QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED

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

За и Против

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

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

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

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

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

Скрипт funcind.sql

За дополнительной информацией обращайтесь в Interface Ltd.