Индексирование избранных строк

Источник: ln

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

Можно ли индексировать только избранные строки?

Привет, Том!

Возможно, я отстал от времени - все еще использую версию 8.1.7, - но мне просто интересно, нельзя ли создать индексы только по избранным строкам. Например, когда в индексируемом столбце много пустых значений и, как я видел во множестве различных приложений, с которыми приходилось работать, совершенно точно известно, что строки с пустыми значениями в этом столбце (по этому индексу) выбирать никогда не будут.

Мне кажется, что можно было бы сэкономить место на диске (и в памяти, насколько я понимаю), если бы можно было потребовать индексировать только строки с конкретными значениями (или наоборот - не идексировать такие строки).

Ответ Тома Кайта

Ну, во-первых, если запись индекса (обычного, не на основе битовых карт - прим. В.К. ) целиком состоит из значений NULL, она вообще никогда не попадает в индекс!

Рассмотрим пример:

ops$tkyte@ORA920> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA920> create index t_idx on t(x);
 
Index created.
 
ops$tkyte@ORA920> insert into t select null from all_users;
 
51 rows created.
 
ops$tkyte@ORA920> analyze index t_idx validate structure;
 
Index analyzed.
 
ops$tkyte@ORA920> select name, lf_rows from index_stats;
 
NAME                              LF_ROWS
------------------------------ ----------
T_IDX                                   0

Этот индекс - пустой, в нем ничего нет. Поместим в таблицу непустую строку, и:

ops$tkyte@ORA920> insert into t values ( 1 );
 
1 row created.
 
ops$tkyte@ORA920> analyze index t_idx validate structure;
 
Index analyzed.
 
ops$tkyte@ORA920> select name, lf_rows from index_stats;
 
NAME                              LF_ROWS
------------------------------ ----------
T_IDX                                   1

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

ops$tkyte@ORA920> update t set x = rownum;
 
52 rows updated.
 
ops$tkyte@ORA920> analyze index t_idx validate structure;
 
Index analyzed.
 
ops$tkyte@ORA920> select name, lf_rows from index_stats;
 
NAME                              LF_ROWS
------------------------------ ----------
T_IDX                                  53

все они окажутся в индексе...

Теперь, чтобы проиндексировать избирательно, можно использовать CASE (или встроенные функции DECODE и NVL в тех версиях Oracle, где CASE еще не поддерживался - прим. В.К. ) со сложным условием для идентификации необходимых значений - в данном случае, я буду индексировать все строки, в которых x меньше 25:

 
ops$tkyte@ORA920> create index t_idx2 on t( case when x < 25 then x end );
 
Index created.
 
ops$tkyte@ORA920> analyze index t_idx2 validate structure;
 
Index analyzed.
 
ops$tkyte@ORA920> select name, lf_rows from index_stats;
 
NAME                              LF_ROWS
------------------------------ ----------
T_IDX2                                 24

Только эти строки оказались в индексе. Далее, можно (и, по-моему, нужно) использовать представление, чтобы скрыть сложность запросов через этот индекс:

ops$tkyte@ORA920> create or replace view v as select x, case when x < 25 then x end another_x from t;
 
View created.
 
ops$tkyte@ORA920> analyze table t compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA920> set autotrace traceonly explain
ops$tkyte@ORA920> select * from v where another_x = 5;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=2)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=2)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE) (Cost=2 Card=1)
 
ops$tkyte@ORA920> set autotrace off

Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=23581