Puede Oracle utilizar un índice sin tener una cláusula WHERE en la consulta ?

Un fast full index scan sucede cuando la base de datos recupera registros de una tabla, sin ir directamente a la tabla. La base de datos lee los bloques del índice sin un orden particular y devuelve el resultado solicitado.

Esto es posible si se cumple al menos dos condiciones:

- El indice contiene todas las columnas requeridas por la consulta.

- Al menos alguna columna debe tener un política de restricción que limite la existencia de nulos en la columna, NOT NULL constraint.


Para demostrar lo anterior, vamos a utilizar la tabla "employees" del esquema de ejemplo HR.

SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

Como observan, la columna "last_name" cuenta con un atributo de constraint de no nulo, pero la columna "salary" no cuenta con restricción alguna.

Si validamos el plan de ejecución de la consulta a continuación, sin incluir una cláusula WHERE, por lógica vamos a obtener un FULL TABLE SCAN sobre la tabla "employees".

SQL> explain plan for
  2  select last_name, salary from employees;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  1284 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  1284 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.

Ahora bien, que pasa si creamos un índice compuesto o concatenado en la tabla, incluyendo ambas columnas de la consulta anterior.?

SQL> create index employees_composite01 on employees(last_name, salary);

Index created.

Actualizamos las estadísticas del nuevo objeto.

SQL> execute dbms_stats.gather_index_stats('HR','EMPLOYEES_COMPOSITE01');

PL/SQL procedure successfully completed.

Ahora veamos el nuevo plan de ejecución.

SQL>  explain plan for
  2  select last_name, salary from employees;

Explained.

SQL> select * from table(dbms_xplan.display);
SQL> set linesize 200
SQL> /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2853682791

------------------------------------------------------------------------------------------
| Id  | Operation        | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                       |   107 |  1284 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMPLOYEES_COMPOSITE01 |   107 |  1284 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

8 rows selected.

SQL>

Como pueden observar, la base de datos utiliza el índice para devolver los registros requeridos por la consulta, sin tener que ir a la tabla.