POR QUE MI QUERY REALIZA UN FULL SCAN A PESAR DEL ÍNDICE

Algunos desarrolladores consideran que generando índices se resuelven la mayor parte de los problemas de desempeño en sus querys. Generan planes de ejecución buscando al supuesto culpable de todos sus males: la sentencia 'full scan'.  Incluso algunas herramientas  lo muestran en rojo, para que sea más fácil identificarlo.

Una vez que el desarrollador detecta el 'full scan'  procede a la generación del indice, normalmente de tipo b-tree, que es el default de Oracle. Una vez generado, observan con molestia que se sigue realizando el 'full scan' a la tabla.

El siguiente ejercicio tiene como objetivo generar un índice y observar su comportamiento. Previo a su generación, vamos a realizarnos  las siguientes preguntas básicas:

 1.- Del total de registros de la tabla, cuantos va a recuperar el query?

 2.- Cuantos valores distintos contiene el campo o campos sobre el cual se va a generar el índice?

 3.- Contiene valores nulos el campo a indexar?

 4.- El campo a indexar contiene los datos ordenados?

 5.- Verificar que los siguientes parametros a nivel instancia que afectan la ejecución de indices,  contengan   los valores por default:

• OPTIMIZER_INDEX_COST_ADJ=100.- El valor por default es 100. Si se disminuye este valor, Oracle considera que tiene 'menor costo' el utilizar un índice y favorece su ejecución. Lo recomendable es modificar este valor a nivel sesión, no a nivel instancia.
• OPTIMIZER_INDEX_CACHING=0.- El valor por default es 0. Si asignamos un valor,  el indice tendra la informacion en cache y se ejecutara más rápido. Es recomendable modificarlo a nivel sesión, no instancia.

Para verificar los valores apropiados para estos parámetros se puede revisar la guía de performance OPDG  (Oracle Performance Diagnostic Guide) del sitio de soporte oficial de Oracle.

Iniciamos el ejercicio creando la tabla TEST la cual va a contener un solo campo de tipo númerico

SQL> CREATE TABLE TEST ( campo1 number);

Table created.

Insertamos 100 mil registros únicos en la tabla TEST, ordenados de forma ascendente por medio del siguiente bloque pl/sql:

SQL>  DECLARE
                vcuenta NUMBER := 100000;
              BEGIN
                 FOR i IN 1..100000 LOOP
                   vcuenta  := vcuenta+1;
                   INSERT INTO TEST values (vcuenta);
                 END LOOP;
                   COMMIT;
          END;
   /

PL/SQL procedure successfully completed. 

Validamos la ejecución del bloque PL/SQL. Se tienen 100 mil registros únicos iniciando con el valor 100,000 y finalizando con el registro 200,000   

SQL> SELECT min(campo1), max(campo1), count(*) FROM test;

   MIN(CAMPO1) MAX(CAMPO1)   COUNT(*)
-----------            -----------                       ----------
     100001          200000                         100000

Generamos el índice idx_test1 en el campo campo1

    
SQL> CREATE INDEX idx_test1 ON test (campo1);

Index created.

Actualizamos estadísticas de la tabla TEST con el package 'DBMS_STATS' con los parámetros básicos,

incluyendo la opción 'cascade=> TRUE' para que se actualicen las estadísticas del índice:

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TEST', cascade=> TRUE);

PL/SQL procedure successfully completed.

Actualizamos las estadísticas del índice recien generado IDX_TEST1 (ya habiamos dado la opción cascade, por si las dudas)

SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>'TEST', indname=> 'IDX_TEST1');

PL/SQL procedure successfully completed.

Verificamos las estadísticas actualizadas de la tabla 'TEST' en la tabla del sistema 'dba_tables'

SQL> SELECT num_rows, last_analyzed, sample_size

           FROM dba_tables WHERE table_name = 'TEST';

  

   NUM_ROWS   LAST_ANA  SAMPLE_SIZE
----------                --------                  -----------
    100000           29/04/14           100000    

Verificamos las estadísticas del índice en la tabla del sistema dba_indexes.  Esta consulta nos puede indicar, de forma muy básica, que tan probable es que nuestro índice funcione correctamente, verificando los siguientes puntos:

1.- NUM_ROWS .- El índice contiene los rowids del total de registros de la tabla : 100,000. El rowid es la  dirección física donde se encuentra el registro guardado. El índice va a recuperar estos rowids.
               
2.- LAST_ANALIZED .- Contiene la fecha de la última actualización de estadísticas para el índice

3.- SAMPLE_SIZE .- Contiene el tamaño muestra utilizado por el 'package' DBMS_STATS para calcular las estadísticas.  Lo ideal es que este valor sea igual al campo 'NUM_ROWS'. En este caso tenemos un tamaño muestra apropiado de 100,000 registros, que el mismo que el el 'NUM_ROWS'

4.- DISTINCT_KEYS.- Tal vez sea uno de los campos más importantes para validar la viabilidad del índice. En este caso tenemos 100,000 valores distintos, que es lo más apropiado. Si por ejemplo se tuvieran 100,000 mil registros, de los cuales 50,000  contienen el valor 1 y los otros 50 mil restantes el valor de 2, tendriamos un valor de DISTINCT_KEYS igual a 2, por lo que seguramente el índice no funcionaria.

5.- CLUSTERING_FACTOR .- En este caso ingresamos 100,000 registros de forma ascendente 1,2,3,4,5 sucesivamente, por lo que los datos se encuentran ordenados fisicamente en la tabla, lo que facilita las búsquedas. El valor contenido en este campo, de 152  indica un buen grado de ordenamiento en la tabla. Entre menor sea el valor de este campo, mejor ordenamiento físico se tendrá.
                      

SQL> SELECT num_rows, last_analyzed, sample_size, distinct_keys, clustering_factor FROM dba_indexes where index_name ='IDX_TEST1';

  NUM_ROWS         LAST_ANA        SAMPLE_SIZE        DISTINCT_KEYS    CLUSTERING_FACTOR
----------                      --------                    -----------                      -------------                       -----------------
    100000                29/04/14               100000                       100000                            152   
   

Con el índice generado vamos a realizar una primera consulta que va a recuperar el 100% de los registros de la tabla.

El siguiente plan de ejecución muestra que se está utilizando él índice 'IDX_TEST1'. Se esta realizando un 'INDEX FAST FULL SCAN', el cual permite contar el total de registros, por medio del mismo índice, sin necesidad de contar los registros en la tabla. Este tipo de 'barrido' o 'scan' es muy útil cuando se recupera un volumen importante de información  ya que utiliza lecturas 'multibloque' y puede ser invocado por el hint 'index_ffs'.  En este momento se está realizando un 'FULL SCAN'

  1* SELECT count(*) FROM test WHERE campo1 between 100000 AND 200000

  COUNT(*)
----------
    100000

Execution Plan
----------------------------------------------------------
Plan hash value: 2671621383

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     5 |    63   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_TEST1 |   100K|   488K|    63   (2)| 00:00:01 |
-----------------------------------------------------------------------------------

Vamos a realizar otra consulta para recuperar ahora el 30% del total de registros y como nos muestra el plan de ejecución, se sigue realizando un 'FULL SCAN'
   
SQL> SELECT count(*) FROM test WHERE campo1 between 100000 AND 130000;

  COUNT(*)
  ----------
     30000

Execution Plan
----------------------------------------------------------
Plan hash value: 2671621383

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     5 |    63   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_TEST1 | 30000 |   146K|    63   (2)| 00:00:01 |

-----------------------------------------------------------------------------------           

Ahora modificamos el query para que recupere el 27% del total de registros. En este momento se observa en el plan de ejecución cambio de un 'INDEX FAST FULL SCAN' a un 'INDEX RANGE SCAN' por lo que en este punto es donde el índice estaría cumpliendo su verdadera funcionalidad, de retornar un  número limitado de registros de forma eficiente.   
   
No es una regla escrita, pero si se va a recuperar entre el 1 y el 25% del  total de registros de una tabla (en este ejercicio funciono hasta el 27% del total), el índice será utilizado, sin necesidad de realizar un 'FULL SCAN' y tendremos a nuestro desarrollador contento.

En este punto se deben considerar factores como cardinalidad, selectividad y valor asignado al parámetro 'DB_FILE_MULTIBLOCK_READ_COUNT'

Para valores mayores, el manejador prefiere realizar un 'FULL SCAN' con lecturas 'multibloque' ignorando lecturas de índice de tipo 'INDEX SCAN' y 'RANGE SCAN' principalmente.
   
SQL> SELECT count(*) FROM test WHERE campo1 between 100000 AND 127000;

  COUNT(*)
----------
     27000

Execution Plan
----------------------------------------------------------
Plan hash value: 3196887443
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     5 |    62   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TEST1 | 27000 |   131K|    62   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Conclusiones

Previo a la generación de un índice, es necesario considerar distintos factores como son el número de registros que va a retornar, cardinalidad y selectividad entre otros. Si un query va a retornar un aproximado del 30% o más de registros y generamos un índice, es probable que este no sea invocado para búsquedas puntuales de tipo 'INDEX SCAN' y 'RANGE SCAN' principalmente.  La tabla del sistema 'DBA_INDEXES' nos muestra información básica del índice. Si requerimos información adicional de un índice, la podremos obtener de la vista del sistema 'INDEX_STATS'

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com


5532439143 Ciudad de México