lunes, 10 de septiembre de 2007

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.

3 comentarios:

Albeyro dijo...

Muy interesante el tema yo creia que no hiba a poder optimizar las consultas sobre vistas.

Anónimo dijo...

Buenos días Leandro desde España. Hoy he encontrado tu blog y te animo a que sigas compartiendo tu conocimiento. Está genial.

Tengo una duda sobre el tema de las vistas.
Hemos creado una vista con la siguiente estructura

CREATE VIEW VW1 AS
SELECT campo1, campo2, campo3,...
FROM TB1,(SELECT .... )

Si consultamos las vista con las siguients condiciones:

SELECT * FROM VW1
WHERE campo1 = a
AND CAMPO2 = B
and campo3 = C

tiene un rendimiento muy inferior a si ejecutamos la consulta de la vista directamente con el mismo WHERE.

Analizando las consultas vemos que la ejecución directa utiliza un plan distinto que si ejecutamos la vista con las condiciones.

¿Puede ser que primero se ejecute la vista y después sobre le resultado aplique la WHERE?, de ser así se puede utilizar algún hint que obtenga el plan de la ejecución del select directa?

Creo que no me he explicado muy bien pero de antemano te agradezco tu ayuda.

Un Saludo desde España (de un español casado con una argetina)

Pedro.

Leonardo Horikian dijo...

Hola,

Seguramente lo que sucede es que la vista al tener un in-line view (SELECT .... ) no te deje mergear la vista con las condiciones del where. Esto produce que Oracle tenga que ejecutar primero la vista, y una vez que tiene los datos de la vista en el espacio temporal, tenga que aplicarle los filtros del where... es por eso que si ejecutas la consulta con la vista cuesta más que si la ejecutas sin la vista.

Yo probaría haciendo lo siguiente:

Si por ejemplo, tenes la siguiente vista:

CREATE VIEW VW1 AS
SELECT TB1.campo1, TB1.campo2, TB2.campo3
FROM TB1, (SELECT campo3 FROM TB2 WHERE id = 10) TB2
WHERE TB1.id = TB2.id;

Probaría transformar esa vista de ésta forma:

CREATE VIEW VW1 AS
SELECT TB1.campo1, TB1.campo2, TB2.campo3
FROM TB1, TB2
WHERE TB1.id = TB2.id
AND TB2.id = 10;

...de ésta manera evitas realizar el costoso in-line view.

Saludos.