En lo posible, evitá utilizar el comando "EXPLAIN PLAN FOR" !!!

"EXPLAIN PLAN FOR" tiene problemas...

• 1er. PROBLEMA: Trata todas las Bind Variables como VARCHAR2.

Veamos un ejemplo:

SQL_10gR2> DESC emp

Nombre                  Nulo?    Tipo
----------------------- -------- -------------
 EMPNO                   NOT NULL NUMBER(4)
ENAME                            VARCHAR2(10)
JOB                              VARCHAR2(9)
GENDER                           VARCHAR2(1)
MGR                              NUMBER(4)
HIREDATE                         DATE
SAL                              NUMBER(7,2)
COMM                             NUMBER(7,2)
DEPTNO                           NUMBER(2)

SQL_10gR2> VAR bind NUMBER;

SQL_10gR2> EXECUTE :bind := 7900

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> explain plan for
2  SELECT * FROM emp WHERE empno = :bind;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    39 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    39 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMPNO_PK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:BIND))

Como podemos ver, creé una variable del tipo NUMBER pero al ejecutar el comando "EXPLAIN PLAN FOR" utilizando esa variable, Oracle la convirtió en una variable del tipo VARCHAR2 para luego aplicarle la función de conversión TO_NUMBER y volver a convertirla en tipo NUMBER.

• 2do. PROBLEMA: Puede NO mostrarte el plan de ejecución real que será utilizado en el ejecución de tu consulta.

SQL_10gR2> DESC dept
Nombre                  Nulo?    Tipo
----------------------- -------- ----------------
 DEPTNO                  NOT NULL VARCHAR2(10)
DNAME                            VARCHAR2(14)
LOC                              VARCHAR2(13)

SQL_10gR2> SELECT * FROM dept;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON

4 filas seleccionadas.

SQL_10gR2> VAR bind NUMBER;

SQL_10gR2> EXECUTE :bind := 20

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> explain plan for
2  SELECT * FROM dept WHERE deptno = :bind;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT           |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_DEPTNO_PK |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:BIND)

SQL_10gR2> SELECT * FROM dept WHERE deptno = :bind;

DEPTNO     DNAME          LOC
---------- -------------- -------------
20         RESEARCH       DALLAS

1 fila seleccionada.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("DEPTNO")=:BIND)

Como pueden observar, la primera vez que obtuve el plan de ejecución de la consulta, lo hice con el comando "EXPLAIN PLAN FOR" y vemos que estamos utilizando un índice para acceder a los datos. La segunda vez, optamos por ejecutar la consulta y luego obtener el plan de ejecución REAL (utilizando dbms_xplan.display_cursor). Qué observamos? Oracle está realizando un acceso a datos através de un FULL SCAN de la tabla. Esto sucedió porque el tipo de dato VARCHAR2 es siempre elegido para ser convertido en una comparación de tipos de datos distintos. En este caso, como la columna DEPTNO es del tipo VARCHAR2, y como estoy comparando esa columna con una variable del tipo NUMBER, Oracle tuvo que aplicar la función TO_NUMBER a la columna DEPTNO y como ya sabemos, si aplicamos una función a una columna indexada, Oracle no puede utilizar el índice en esa columna ya que la función lo deshabilita. Cuando obtuve el plan de ejecución de la primer consulta no hubo ningún tipo de conversión de datos (es por eso que accedimos por índice) ya que estoy comparando una columna del tipo VARCHAR2 (deptno) con una variable NUMBER... pero que en realidad esa variable NUMBER es un VARCHAR2 (ya que como dijimos anteriormente, si ejecutamos el comando "EXPLAIN PLAN FOR", Oracle trata todas las Bind Variables como VARCHAR2).

Siempre es bueno tener en mente cuál es la diferencia entre lo IDEAL y lo REAL. Lo IDEAL para éste caso sería acceder por índice a los datos, lo REAL es que Oracle está realizando lo contrario.