Index-Skip Scans

El "Index-Skip Scans" está disponible a partir de Oracle 9i. Cuando tenemos un índice compuesto, permite en algunas circunstancias, que el CBO no tome en cuenta la primer columna del índice, sino que lea las restantes. Esto es útil cuando en una consulta hacemos referencia a alguna de las columnas que no están en la cabecera del índice pero que sin embargo queremos utilizar ese índice. Obviamente, el CBO utiliza Index-Skip Scans solamente cuando se cumplen 2 condiciones:

1- La columna cabecera del índice debe contener muy pocos valores distintos. Osea, tiene que ser una columna no selectiva. Tipicamente son las columnas apropiadas para la utilización de un Bitmap Index.
2- En la consulta debemos hacer referencia, por lo menos, a alguna de las columnas restantes del índice.

Supongamos que tenemos 3 consultas:

SELECT d FROM test WHERE a = :a ;

SELECT d FROM test WHERE a = :a AND b = :b ;

SELECT d FROM test WHERE a = :a AND b = :b AND c = :c ;

Qué índice nos conviene crear para que sea utilizado en las 3 consultas?
Claramente crearíamos un índice B*Tree compuesto por las columnas A,B,C.

Qué sucede si tenemos ésta consulta?

SELECT d FROM test WHERE b = :b AND c = :c ;

El índice es utilizado?

Veamos un ejemplo:

SQL_9iR2> CREATE TABLE test AS
  2  SELECT mod(level,3) a, level b, level c, 'nom_'||level d
  3  FROM dual
  4  CONNECT BY level <= 20000 ;

Table created.

SQL_9iR2> CREATE INDEX t_abc_idx ON test(a,b,c) ;

Index created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

Ya tenemos nuestra tabla creada y un índice por las columnas A,B,C. Observen que inserté en la columna A sólo 3 valores distintos (0,1 y 2).

Bien, veamos el explain plan de la consulta que vimos anteriormente:

SQL_9iR2> explain plan for
  2  SELECT d
  3  FROM test
  4  WHERE a = 2 AND b = 182 AND c = 182 ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    21 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    21 |     2 |
|*  2 |   INDEX RANGE SCAN          | T_ABC_IDX   |     1 |       |     1 |
---------------------------------------------------------------------------

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

   2 - access("TEST"."A"=2 AND "TEST"."B"=182 AND "TEST"."C"=182)

Como podemes ver, el índice es utilizado porque estamos accediendo a las 3 columnas del índice. Que sucede si accedemos sólo a las columnas B y C ?

SQL_9iR2> explain plan for
  2  SELECT d
  3  FROM test
  4  WHERE b = 182 AND c = 182 ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    20 |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    20 |     5 |
|*  2 |   INDEX SKIP SCAN           | T_ABC_IDX   |     1 |       |     4 |
---------------------------------------------------------------------------

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

   2 - access("TEST"."B"=182 AND "TEST"."C"=182)
       filter("TEST"."B"=182 AND "TEST"."C"=182)

Observamos que el índice es utilizado porque cumplimos con las 2 condiciones necesarios para que el CBO utilice Index-Skip Scans.

La utilización de ésta clase de acceso es más costosa que realizar un accedo directo a través del índice, pero en general es menos costosa que utilizar un full-scan.