Index Skip Scan

Думаю, об индексах в Oracle можно писать и писать. Индексы - это одна из моих любимых тем по базам данных Oracle. До 9 версии Оракла, при поиске по составному индексу не использовался индекс если в запросе не было поиска по лидирующему (leading) столбцу индекса. Поэтому и рекомендовалось в качестве лидирующего столбца выбирать столбец наиболее часто использующийся для поиска.

В 9 версии появился новые индексный доступ INDEX SKIP SCAN, который позволяет использовать составной индекс для поиска данных, даже если в условиях поиска отсутствует лидирующий столбец.

SQL> create table t (a number, b number);

Table created.

SQL> create index t_ix on t(a, b);

Index created.

SQL>
SQL> begin
  2  for i in 1 .. 10000
  3  loop
  4  insert into t values(mod(i, 2), i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> begin
  2  dbms_stats.gather_table_stats(user, 'T', cascade=>true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> explain plan set statement_id='SKIP'
  2  for select * from t where b=5;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','SKIP', 'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------


--------------------------------------------------------------------
 Id   Operation              Name        Rows   Bytes  Cost
--------------------------------------------------------------------
   0  SELECT STATEMENT                       1      6      3
*  1   INDEX SKIP SCAN      T_IX             1      6      3 
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

   1 - access("T"."B"=5)
       filter("T"."B"=5)

Note: cpu costing is off

15 rows selected.

SQL>

В этом примере использовался индексный доступ INDEX SKIP SCAN по составному индексу, при отсутствии поиска по лидирующему столбцу.

Так, как же работает INDEX SKIP SCAN? На одной из публикаций на сайте Оракла, пишут:

During a skip scan, the composite index is accessed once for each distinct value of the leading column(s). For each distinct value, the index is searched to find the query's target values.

То есть для каждого различного значения лидирующего столбца выполняется поиск значений "второстепенных" столбцов (не нашла более подходящего слова для описания остальных, "лидируемых", ведомых, "менее значимых" столбцов составного индекса). Практически, выполняется INDEX RANGE SCAN для каждого различного значения лидирующего столбца.

В документации Оракла пишут, что INDEX SKIP SCAN эффективен в тех случаях, когда лидирующий столбец составного индекса имеет минимальное количество различных значений.

Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

В нашем примере лидирующий столбец имеет всего 2 различных значения (1 и 0), но если бы он был более селективным, то есть содержал больше различных значений, то скорее всего оптимизатор предпочел бы TABLE FULL SCAN. Как, например, здесь:

SQL> create table t2 (a number, b number);

Table created.

SQL> create index t2_ix on t2(a, b);

Index created.

SQL>
SQL> begin
  2  for i in 1 .. 10000
  3  loop
  4  insert into t2 values(mod(i, 10), i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> begin
  2  dbms_stats.gather_table_stats(user, 'T2', cascade=>true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan set statement_id='NOSKIP'
  2  for select * from t2 where b=5;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','NOSKIP', 'ALL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------


--------------------------------------------------------------------
 Id   Operation              Name        Rows   Bytes  Cost
--------------------------------------------------------------------
   0  SELECT STATEMENT                       1      6      5
*  1   TABLE ACCESS FULL    T2               1      6      5 
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

   1 - filter("T2"."B"=5)

Note: cpu costing is off

14 rows selected.

SQL>

Мы всего лишь увеличили количество различных значений лидирующего столбца с 2 до 10, и оптимизатор посчитал, что в этом случае ему выгодней использовать TABLE FULL SCAN чем INDEX SKIP SCAN. Есть хинт index_ss(алиас_таблицы название_индекса), который можно использовать для принудетельного использования индексного доступа INDEX SKIP SCAN:

SQL> explain plan set statement_id='INDEX_SS'
  2  for select /*+ index_ss (t2 t2_ix) */* from t2 where b=5;

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','INDEX_SS', 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------


--------------------------------------------------------------------
 Id   Operation              Name        Rows   Bytes  Cost
--------------------------------------------------------------------
   0  SELECT STATEMENT                       1      6     11
*  1   INDEX SKIP SCAN      T2_IX            1      6     11 
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------

   1 - access("T2"."B"=5)
       filter("T2"."B"=5)

Note: cpu costing is off

15 rows selected.

SQL>

Как уже было сказано выше, INDEX SKIP SCAN эффективен в тех случаях, когда лидирующий столбец имеет минимальное количество различных значений. INDEX SKIP SCAN - это не "silver bullet", то есть не идеальное и эффективнейшее решение для всех поисков по составному ключу без лидирующего столбца. Джонатан Льюс (Jonathan Lewis) в своей книге о стоимостном оптимизаторе (кстати, это одна из моих любимых книг по Ораклу) пишет, что INDEX SKIP SCAN может наоборот снижать производительность:

This is a beneficial feature in some cases, particularly if you have compressed indexes with a very low number of distinct values in the first column(s). But occasionally you may find that a skip-scan is causing performance problems.

Есть еще скрытый параметр _optimizer_skip_scan_enabled, который можно установить в false, чтоб отключить индексный путь доступа INDEX SKIP SCAN.

alter session set "_optimizer_skip_scan_enabled"=false;