Эта короткая статья посвящена индексированию строк таблицы, удовлетворяющих некоторому условию.
Можно ли индексировать только избранные строки?
Привет, Том!
Возможно, я отстал от времени - все еще использую версию 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