FULL TABLE SCAN-Oracle no quiere utilizar mi índice

Quizás esta imagen les sea conocida, 7 ó 12 personas apiladas detrás del DBA de turno viendo su pantalla, preguntando que sucede o haciendo suposiciones, te llaman para que apoyes, llegas, te abres paso entre la multitud de jefes y lo primero que vez es una gran franja azul en el Cloud Control (OEM), luego de dos clic en el OEM o tres consultas al ASH, es evidente que una consulta SQL está realizando muchísimas lecturas a disco.

Un desarrollador refuta que la tabla tiene un índice por el campo búsqueda en el WHERE, sin embargo, la Base de Datos decide hacer Table Full Scan. ¿Por qué está pasando esto?

Comencemos:

Consulta SQL que está generando afectación:

SELECT ID_EMBRQ FROM DIST_EMBARQUE_OPER WHERE ID_EMBRQ =:1; 

Podemos observar que el campo "ID_EMBRQ" es de tipo VARCHAR2

Column                           Type
Name                             Data         DATA_LENGTH N  POS
-------------------------------- ------------ ----------- - ----
ID_EMBRQ                         VARCHAR2              80 N    1
C_NUM_OPER                       NUMBER                22 Y    2

Índices

Index                                       Column
Owner           Name                  STATUS      NUM_ROWS  Name
--------------- --------------------  --------- ----------  ---------------
RAE             PK_ID_EMBRQ           VALID      9793685401 ID_EMBRQ

En el siguiente ejemplo vemos que al no utilizar el mismo tipo de datos en el valor del WHERE se fuerza un Full Table Scan.

SQL> SELECT ID_EMBRQ FROM DIST_EMBARQUE_OPER WHERE ID_EMBRQ = #
Enter value for num: 01012019123803098
old   1: SELECT ID_EMBRQ FROM DIST_EMBARQUE_OPER WHERE ID_EMBRQ = &num
new   1: SELECT ID_EMBRQ FROM DIST_EMBARQUE_OPER WHERE ID_EMBRQ = 01012019123803098

Elapsed: 00:00:00.92

Execution Plan
----------------------------------------------------------
Plan hash value: 3434683370

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |     1 |    18 | 10336   (2)| 00:02:05 |
|*  1 |  TABLE ACCESS FULL| DIST_EMBARQUE_OPER |     1 |    18 | 10336   (2)| 00:02:05 |
----------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("ID_EMBRQ")=01012019123803098)

“1 - filter(TO_NUMBER("ID_EMBRQ")=01012019123803098)” Esto es importante para comprender porque no utilizó el índice, podemos observar que el manejador realiza una conversión implícita con la función TO_NUMBER, y como nuestro índice es sin la función TO_NUMBER lo omite.

Si ponemos en el valor que buscamos entre comillas el Optimizador puede interpretar que son del mismo tipo y toma el índice y mejora sustancialmente el plan de ejecución.

SQL> SELECT ID_EMBRQ FROM DIST_EMBARQUE_OPER WHERE ID_EMBRQ = '&num';
Enter value for num: 01012019122613955
old   1: SELECT ID_EMBRQ FROM DIST_EMBARQUE_OPER WHERE ID_EMBRQ = '&num'
new   1: SELECT ID_EMBRQ FROM DIST_EMBARQUE_OPER WHERE ID_EMBRQ = '01012019122613955'

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2459158912

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |     1 |    18 |     2   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| PK_CETATRANOPER |     1 |    18 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - access("ID_EMBRQ"='01012019122613955')

Conclusión:
El FULL TABLE SCAN se debió al uso inadecuado del tipo de datos en el criterio del WHERE. Amigos desarrolladores por favor nunca, pero nunca dejen la conversión de datos al manejador, es solo cuestión de ver qué tipo de datos tiene el campo y usar el mismo, con esa simple acción ahorramos mucha energía y especialmente no interrumpen el desayuno de un humilde DBA.