Por que el optimizador no usa mi índice cuando arma el plan de ejecución (Clustering Factor)

Es común que los desarrolladores se frusten al ver que el optimizador por costos (CBO) no usa el índice que recién crearon. No es casualidad que le haya llevado varios años a Oracle convencer a sus clientes que el optimizador por reglas (RBO) no servía mas (de hecho se dejó de actualizar en 1992). Es cierto que versión tras versión el CBO fue haciéndose mas sofisticado (de hecho se lo considera una de las piezas de software mas complejas que existe) y también es verdad que en sus primeras versiones tenia algunos problemas, pero eso fue cambiando. En mi opinión, el gran salto se dio a partir de 10g ya que despues de muchas promesas durante los años anteriores, fue recien en 10g donde se desoportó a RBO, aunque se podía seguir utilizando.
Uno de los motivos mas frecuentes de que el optimizador no escogiera el plan ideal era la falta o desactualizacion de estadísticas de los objetos involucrados en las sentencias. Para mi las dos principales innovaciones de 10g fueron: a) la automatización de la recolección de estadísticas mediante las ventanas de mantenimiento, que se activan por default, y b) el denominado dynamic sampling por el cual si se realiza una pseudo recolección de estadísticas "on the fly" (al vuelo) para los segmentos que aun no cuenten con información estadística. Sin embargo, hay casos donde las estadísticas son "frescas" pero de todas formas nos frustamos al ver que no se arma el plan que pretendemos, no se usa nuestro índice.
Cuando comencé con el tema de tuning sentí esa misma frustración muchas veces y con el tiempo me di cuenta que en la mayoría de las casos era por desconocimiento, no sabia que pasaba y entonces recurría a una técnica muy frecuente y efectiva para salir del paso, el uso de un hints. No voy a ahondar demasiado en el tema de hints, pero es sabido que excepto algunos tipos de hints en ciertos casos especificos, no es una práctica recomendada. Uno de esos desconocimientos fue no conocer que era el clustering_factor. El clustering_factor es un número (una parte de información recolectada en la toma de estadísticas de los índices) que compara el orden de un índice con el grado de desorden en la tabla que indexa. Ese número puede ser tan pequeño como la cantidad de bloques de la tabla indexada o tan grande como el número de filas indexadas. Si ese valor tiende a ser igual a la cantidad de bloques decimos que el clustering_factor es bajo y si el valor tiende a ser igual a la cantidad de filas decimos que el clustering_factor es alto. En este ultimo caso es cuando el optimizador tiende a evitar el uso del índice para consultas del tipo RANGE SCAN. Cuanto mas alto sea el clustering_factor menos probable será que el optimizador escoja el índice para generar su plan.Ahora voy a intentar ejemplificar como funciona el clustering_factor:

Con sqlplus creo una tabla T con datos en cierto orden para que al indexar por la columna X me queden ordenados (clustering factor bajo) y al indexar por la columna Y me queden desordenados (clustering factor alto)

rop@DESA10G> create table t
  2  as
  3  select trunc(rownum/10000) x,
  4         trunc(dbms_random.value(0,9999)) y,
  5         dbms_random.string('a',10) z
  6  from dual
  7  connect by rownum <= 1000000;

Tabla creada.



rop@DESA10G> create index t_x on t(x);

Índice creado.

rop@DESA10G> create index t_y on t(y);

Índice creado.

rop@DESA10G> begin
  2      dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

Vamos a ver la cantidad de filas y bloques estimadas para la tabla T:

rop@DESA10G> select num_rows,blocks
  2  from user_tab_statistics
  3  where table_name = 'T';

  NUM_ROWS     BLOCKS
---------- ----------
    997144       3243

rop@DESA10G> select index_name,clustering_factor,leaf_blocks,num_rows
  2  from user_ind_statistics
  3  where table_name = 'T';

INDEX_NAME                     CLUSTERING_FACTOR LEAF_BLOCKS   NUM_ROWS
------------------------------ ----------------- ----------- ----------
T_X                                         3183        1952    1000000
T_Y                                       984472        2090    1000000

Como resultado de la recolección estadistica se ve que el indice T_X tiene un clustering factor similar a la cantidad de bloques y que el indice T_Y tiene un clustering factor similar a la cantidad de filas.

Una vez armado el escenario, vamos a ver como cambia el plan en concordancia con el factor de aglutinamiento (clustering factor) de acuerdo a la columna que se usa para filtrar en el predicado

rop@DESA10G>  select avg(length(z)) from t where x between 90 and 100;

AVG(LENGTH(Z))
--------------
            10


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 288193465

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    14 |   523   (1)| 00:00:12 |
|   1 |  SORT AGGREGATE              |      |     1 |    14 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |   100K|  1374K|   523   (1)| 00:00:12 |
|*  3 |    INDEX RANGE SCAN          | T_X  |   100K|       |   200   (1)| 00:00:05 |
-------------------------------------------------------------------------------------

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

   3 - access("X">=90 AND "X"<=100)


Estadísticas
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        518  consistent gets
          0  physical reads
          0  redo size
        250  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

El optimizador eligió ir por el indice ya que es mas barato dado que tiene el dato de que el clustering factor es bajo.

Ahora probamos de filtrar por la columna Y, cuyo indexacion tiene clustering factor elevado:

rop@DESA10G>  select avg(length(z)) from t where y between 90 and 100;

AVG(LENGTH(Z))
--------------
            10


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    15 |   740   (3)| 00:00:17 |
|   1 |  SORT AGGREGATE    |      |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1197 | 17955 |   740   (3)| 00:00:17 |
---------------------------------------------------------------------------

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

   2 - filter("Y"<=100 AND "Y">=90)


Estadísticas
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3190  consistent gets
          0  physical reads
          0  redo size
        250  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rop@DESA10G>

Tal cual se esperaba el optimizador optó por hacer un full scan basandose en la dispersión de los datos en los bloques de la tabla.