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.

Diferencias entre COUNT(1) y COUNT(*) - Parte 2

Hace unas semanas, se me acercaron y me hicieron la siguiente pregunta:

"Me dijeron que si en una consulta se coloca el COUNT(*), por cada registro que leamos en un acceso por índice, vamos a tener que acceder también a la tabla ya que el símbolo * significa que estoy colocando todas las columnas de la tabla en la consulta, y si no tengo todas las columnas de la tabla en el índice, entonces Oracle tiene que acceder a la tabla a buscar el resto de las columnas. Es cierto?".

Bueno, no... no es cierto. Para validar el porque digo ésto, primero veamos un ejemplo:

SQL_10gR2> CREATE TABLE test AS
2  SELECT level id, 'texto_'||level texto
3  FROM dual
4  CONNECT BY level <= 100000 ; 

Tabla creada. 

SQL_10gR2> CREATE UNIQUE INDEX test_id_uq ON test(id) ;

Índice creado.

SQL_10gR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',CASCADE=>TRUE) ;

Procedimiento PL/SQL terminado correctamente.

Bien, veamos el plan de ejecución de la siguiente consulta:

SQL_10gR2> SELECT COUNT(*)
2  FROM test
3  WHERE id = 100;

COUNT(*)
----------
      1

1 fila seleccionada.

SQL_10gR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  d6urw3zfuxz32, child number 0
-------------------------------------
SELECT COUNT(*) FROM test WHERE id = 100

Plan hash value: 4041652814

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |            |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN| TEST_ID_UQ |      1 |      1 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)

Como podemos observar, coloqué el COUNT con el símbolo * y accedí al índice con el ID 100; pero luego de acceder al índice NO accedí a la tabla, simplemente accedí al índice (ya que tiene la columna que estoy utilizando en el predicado). Como anteriormente dije en el post "Diferencias entre COUNT(1) y COUNT(*) - Parte 1", no existe ninguna diferencia entre el COUNT(1) y COUNT(*), pero si hay diferencia si ejecutamos COUNT(*) y COUNT(nonbre_de_columna) ya que si colocamos una columna de la tabla en el COUNT, Oracle hace un conteo sólo de los valores de esa columna que NO tengan valores nulos.