Utilizando Hint's sobre vistas

Hay 2 preguntas que me hacen muy a menudo: Cuando consulto una vista ¿Cómo puedo hacer para influenciar al CBO con un Hint sin modificar el contenido de la vista? ¿Porqué el CBO no toma los Hint's que le coloco cuando consulto la vista?

Bien, generalmente veo que cuando se utilizan Hint's sobre las vistas... se hace de forma incorrecta. Lo importante es saber cómo escribir el Hint para que el CBO lo utilice. Para ésto, vamos a realizar un ejemplo.
La primer parte del ejemplo va a consistir en crear una vista y colocar un Hint para que utilice un índice. En la segunda parte vamos a crear otra vista más sobre la vista anterior, y vamos a colocar un Hint para que utilice un índice de la primer vista.

Comencemos creando las tablas, índice y estadísticas...

SQL_9iR2> CREATE TABLE a AS
  2  SELECT level id, 'desc_a_'||level desc_a
  3  FROM dual
  4  CONNECT BY level <= 10000 ;

Table created.

SQL_9iR2> CREATE TABLE b AS
  2  SELECT level id, 'desc_b_'||level desc_b
  3  FROM dual
  4  CONNECT BY level <= 10000 ;

Table created.

SQL_9iR2> CREATE UNIQUE INDEX a_id_idx ON a(id) ;

Index created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'A', cascade => true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'B') ;

PL/SQL procedure successfully completed.

Creamos la primer vista:

SQL_9iR2> CREATE OR REPLACE VIEW view_a_b AS
  2  SELECT a.id, a.desc_a, b.desc_b
  3  FROM a, b
  4  WHERE a.id = b.id ;

View created.

Veamos el Explain Plan de la vista...

SQL_9iR2> explain plan for
  2  SELECT *
  3  FROM view_a_b ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 10000 |   292K|    12 |
|*  1 |  HASH JOIN           |             | 10000 |   292K|    12 |
|   2 |   TABLE ACCESS FULL  | A           | 10000 |   146K|     4 |
|   3 |   TABLE ACCESS FULL  | B           | 10000 |   146K|     4 |
--------------------------------------------------------------------

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

   1 - access("A"."ID"="B"."ID")

Lo que notamos es que se está realizando un Full Scan de las tabla A y B. Que sucede si a modo de ejemplo queremos hacer que la tabla A acceda por el índice que creamos antes? El CBO eligió el explain plan que vemos como el más óptimo (de hecho lo es), pero supongamos que para nosotros es más conveniente acceder por índice... tendríamos que utilizar un Hint para decirle al CBO que acceda por índice en vez de realizar un Full Scan. Pero como hacemos ésto a través de una vista??? Veamos...

SQL_9iR2> explain plan for
  2  SELECT /*+ INDEX(view_a_b.a a_id_idx) */ *
  3  FROM view_a_b ;

Explained.

SQL_9iR2> @explains

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             | 10000 |   292K|    58 |
|*  1 |  HASH JOIN                   |             | 10000 |   292K|    58 |
|   2 |   TABLE ACCESS BY INDEX ROWID| A           | 10000 |   146K|    50 |
|   3 |    INDEX FULL SCAN           | A_ID_IDX    | 10000 |       |    21 |
|   4 |   TABLE ACCESS FULL          | B           | 10000 |   146K|     4 |
----------------------------------------------------------------------------

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

   1 - access("A"."ID"="B"."ID")

Fijense que en el Hint estoy anteponiendo al nombre de la tabla A, el nombre de la vista. Porqué? Porque si coloco solamente en nombre de la tabla A en el Hint, el CBO no lo toma ya que la tabla A no existe en en nuestra consulta, si existe dentro de la vista VIEW_A_B, es por eso que tenemos que anteponer el nombre de la vista y luego hacer referencia a la tabla A.

Qué suecede si tenemos una vista de una vista y queremos hacer lo mismo que hicimos antes?

Creamos la segunda vista:

SQL_9iR2> CREATE OR REPLACE VIEW view_a_b_2 AS
  2  SELECT *
  3  FROM view_a_b
  4  WHERE id BETWEEN 1 AND 500 ;

View created.

Veamos el Explain Plan de la vista...

SQL_9iR2> explain plan for
  2  SELECT *
  3  FROM view_a_b_2 ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   499 | 14970 |     9 |
|*  1 |  HASH JOIN           |             |   499 | 14970 |     9 |
|*  2 |   TABLE ACCESS FULL  | A           |   500 |  7500 |     4 |
|*  3 |   TABLE ACCESS FULL  | B           |   500 |  7500 |     4 |
--------------------------------------------------------------------

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

   1 - access("A"."ID"="B"."ID")
   2 - filter("A"."ID">=1 AND "A"."ID"<=500)
   3 - filter("B"."ID">=1 AND "B"."ID"<=500)

Sucedió exactamente lo mismo que en el ejemplo anterior utilizando una única vista. El CBO está accediendo por Full Scan. Pero nosotros somos caprichosos y pensamos que con un índice la consulta sería más eficiente. Cómo le decimos al CBO a través de un Hint que acceda por índice?

SQL_9iR2> explain plan for
  2  SELECT /*+ INDEX(view_a_b_2.view_a_b.a a_id_idx) */ *
  3  FROM view_a_b_2 ;

Explained.

SQL_9iR2> @explains

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   499 | 14970 |    10 |
|*  1 |  HASH JOIN                   |             |   499 | 14970 |    10 |
|   2 |   TABLE ACCESS BY INDEX ROWID| A           |   500 |  7500 |     5 |
|*  3 |    INDEX RANGE SCAN          | A_ID_IDX    |   500 |       |     3 |
|*  4 |   TABLE ACCESS FULL          | B           |   500 |  7500 |     4 |
----------------------------------------------------------------------------

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

   1 - access("A"."ID"="B"."ID")
   3 - access("A"."ID">=1 AND "A"."ID"<=500)
   4 - filter("B"."ID">=1 AND "B"."ID"<=500)

Ahora no sólo estoy anteponiendo en el Hint el nombre de la vista, sino que también estoy anteponiendo el nombre de la segunda vista que creamos. Porqué? Por la misma razón que en el ejemplo anterior. Si hacemos referencia en el Hint sólo a la tabla A, el CBO no utiliza el Hint ya que la tabla A no existe en nuestra consulta, tampoco existe en la vista VIEW_A_B_2, pero si existe dentro de la vista VIEW_A_B; es por eso que tenemos que anteponer el nombre de las 2 vistas y luego hacer referencia a la tabla A.