Выбор индекса

Рассмотрим некоторую гипотетическую ситуацию, когда у пользователя PORTAL есть 2 индекса на 1 таблице и попытаемся понять когда и какой из выбирается оптимизатором и почему.

Таблицу наше зовут ADM_CRIME_TEST, индексированные поля FIRST_NAME, LAST_NAME, SECOND_NAME, индексы INDX_L_F_S_NAME и INDX_SECOND_NAME. 

Описание таблицы и индексов получено при помощи tabdesc.sql

Table               Number             Empty Average Chain Average Global User       Sample Date
Name               of Rows   Blocks   Blocks   Space Count Row Len Stats  Stats        Size MM-DD-YYYY
--------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ----------
ADM_CRIME_TEST       1,212       61       25   1,585     0     309 YES    NO          1,212 08-26-2003

Column                    Column                   Distinct          Number  Number Global User       Sample Date
Name                      Details                    Values Density Buckets   Nulls Stats  Stats        Size MM-DD-YYYY
------------------------- ------------------------ -------- ------- ------- ------- ------ ------ ---------- ----------
LAST_NAME                 VARCHAR2(30) NOT NULL           4       0       3       0 YES    NO          1,212 08-26-2003
FIRST_NAME                VARCHAR2(30) NOT NULL          81       0      32       0 YES    NO          1,212 08-26-2003
SECOND_NAME               VARCHAR2(30)                   90       0      30       3 YES    NO          1,209 08-26-2003

                                   B                                Average     Average
Index                           Tree Leaf   Distinct     Number Leaf Blocks Data Blocks  Cluster Global User       Sample Date
Name                 Unique    Level Blks       Keys    of Rows     Per Key     Per Key   Factor Stats  Stats        Size MM-DD-YYYY
-------------------- --------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ----------
INDX_L_F_S_NAME      NONUNIQUE     1    7        503      1,212           1           1      160 NO     NO          1,212 08-26-2003
INDX_SECOND_NAME     NONUNIQUE     1    4         90      1,209           1           5      499 NO     NO          1,209 08-26-2003

Index                Column                     Col Column
Name                 Name                       Pos Details
-------------------- ------------------------- ---- ------------------------
INDX_L_F_S_NAME      LAST_NAME                    1 VARCHAR2(30) NOT NULL   
                     FIRST_NAME                   2 VARCHAR2(30) NOT NULL   
                     SECOND_NAME                  3 VARCHAR2(30)            
INDX_SECOND_NAME     SECOND_NAME                  1 VARCHAR2(30)  

Будем выполнять запрос вида: 

SELECT a.birthday_region, a.birthday_date, a.entry_date
FROM adm_crime_test a where last_name = :lname
and SECOND_NAME = :sname
/

Сначала удалим статистику:

exec dbms_stats.delete_table_stats (ownname => 'PORTAL', tabname => 'ADM_CRIME_TEST');

Оказывается, без статистики вне зависимости от того, используются ли bind переменные или литералы используется индекс INDX_SECOND_NAME. Я думаю это такая установка оптимизатора, использовать индекс с меньшим количеством полей. Поскольку он явно меньше, и следовательно потребуется меньше  I/O операций.

Соберем статистику

exec dbms_stats.gather_table_stats (ownname => 'PORTAL', tabname => 'ADM_CRIME_TEST', method_opt => 'for all indexed columns', cascade => TRUE)

Если использовать bind переменные то всегда используется индекс INDX_L_F_S_NAME, вероятно из-за лучшего cluster_factor (и это несмотря даже на то что при его сканировании делается больше чтений) , а вот если использовать вместо bind переменных константы, то план выполнения зависит от их значений, и могут выбираться оба индекса. Это происходит, очевидно, из-за использования оптимизатором гистограммы. И выбор индекса тут более оптимален, чем в предыдущем случае. 

Планы выполнения:

with bind 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=3 Bytes=159)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ADM_CRIME_TEST' (Cost=4
          Card=3 Bytes=159)

   2    1     INDEX (RANGE SCAN) OF 'INDX_L_F_S_NAME' (NON-UNIQUE) (Co
          st=3 Card=3)
          

          0  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
          

          
with some literals

 0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=10 Bytes=530)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ADM_CRIME_TEST' (Cost=6
          Card=10 Bytes=530)

   2    1     INDEX (RANGE SCAN) OF 'INDX_SECOND_NAME' (NON-UNIQUE) (C
          ost=1 Card=10)

         0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size

Из trace файла (как его получить)           


  Access path: index (scan)
      Index: INDX_L_F_S_NAME
  TABLE: ADM_CRIME_TEST
      RSC_CPU: 0   RSC_IO: 10
  IX_SEL:  9.9422e-01  TB_SEL:  8.0115e-03
  Access path: index (equal)
      Index: INDX_SECOND_NAME
  TABLE: ADM_CRIME_TEST
      RSC_CPU: 0   RSC_IO: 6


select count(*) from adm_crime_test where last_name = 'СМИРНОВА';

COUNT(*)
----------
      1205

select count(*) from adm_crime_test where second_name  = 'ИГОРЕВНА';
  COUNT(*)
----------
        16

Из документации:

The optimizer uses the following criteria when determining which index to use:

• Number of rows in the index (cardinality).
• Number of distinct keys. These define the selectivity of the index.
• Level or height of the index. This indicates how deeply the data probe must search in order to find the data.
• Number of leaf blocks in the index. This is the number of I/Os needed to find the desired rows of data.
• Clustering factor (CF). This is the collocation amount of the index block relative to data blocks. The higher the CF, the less likely the optimizer is to select this index.
• Average leaf blocks for each key (ALFBKEY). Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always one.