jueves, octubre 13, 2005

Optimización SQL. Ejemplo 3. El truco de los abanicos (fan trick).

Para Manel Moreno, que no me ha dado ningún beso por ésto. :P


La siguiente consulta tardaba 11 horas en ejecutarse.

SELECT DISTINCT A.NODO_ID, B.NODO_B_ID NODO_EQ1
FROM BITOWN03.BS_R_NODOS_BIT_03 A,
         BITOWN03.RE_R_CONEX_EXTERNAS_BIT_03 B,
         BITOWN03.TMP_NODOS_OK_EST_BIT_03 C
WHERE A.NODO_ID = B.NODO_A_ID
  AND B.NODO_B_ID = C.NODO_ID;


con el siguiente plan de ejecución:
SQL> @c:\oracle\ora92\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
| Id  | Operation              |  Name                    | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                          |  5554 |   124K|    23 |
|   1 |  SORT UNIQUE           |                          |  5554 |   124K|    23 |
|   2 |   NESTED LOOPS         |                          |  5554 |   124K|     2 |
|   3 |    MERGE JOIN CARTESIAN|                          |  5985M|    61G|     2 |
|   4 |     TABLE ACCESS FULL  | TMP_NODOS_OK_EST_BIT_03  |  1327 |  6635 |     2 |
|   5 |     BUFFER SORT        |                          |  4510K|    25M|       |
|   6 |      INDEX FULL SCAN   | PK_R_NODOS_BIT_03        |  4510K|    25M|       |
|*  7 |    INDEX RANGE SCAN    | IDX_NODO_CONEX_EXTERNA   |     1 |    12 |       |
-----------------------------------------------------------------------------------

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

   7 - access("B"."NODO_B_ID"="C"."NODO_ID" AND "A"."NODO_ID"="B"."NODO_A_ID")

Note: cpu costing is off


********************************************************************************
SOLUCIÓN al caso.
********************************************************************************


Otro producto cartesiano. En este caso el cartesiano sabe muy bien lo que hace. Cruza casi 6.000 millones de filas (en total 61 gigas de información) y un coste mínimo. Vaya paradoja.

Un detalle para entender esta decisión: no existen restricciones de Primary Key, ni
Foreign Key, ni índices únicos, ni restricciones de Not Null.

A causa de ello, Oracle encuentra práctico combinar todos los resultados de una tabla (4,5 millones) sobre las 1400 filas de la otra tabla, en un "todos con todos".

No está mal. No obstante, hay información que Oracle, por mucho que analice las tablas, no va a poder obtener a priori. Únicamente nos interesan valores únicos de la tabla de relación, que existan en sus respectivas tablas relacionadas, pero, como digo, no existe nada que aporte a Oracle esa información.

Cambio algunos detalles de la consulta y la dejo así:


SELECT DISTINCT A.NODO_ID, B.NODO_B_ID NODO_EQ1
FROM     BITOWN03.BS_R_NODOS_BIT_03 A,
      (select distinct nodo_a_id, nodo_b_id from BITOWN03.RE_R_CONEX_EXTERNAS_BIT_03) B,
         BITOWN03.TMP_NODOS_OK_EST_BIT_03 C
WHERE A.NODO_ID = B.NODO_A_ID
  AND B.NODO_B_ID = C.NODO_ID;


Informo de dos cosas: que tengo interés en obtener los códigos distintos de la tabla de relación, y que además existan en las otras dos tablas.

El plan de ejecución cambia totalmente para ejecutarse tal como lo he dicho de otra forma. Ahora Oracle realiza este otro plan de ejecución.


SQL> @c:\oracle\ora92\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------
| Id  | Operation             |  Name                       | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                             |  5554 |   200K|       | 13626 |
|   1 |  NESTED LOOPS         |                             |  5554 |   200K|       | 13626 |
|   2 |   NESTED LOOPS        |                             |  5554 |   168K|       | 13626 |
|   3 |    VIEW               |                             |  7159K|   177M|       | 13626 |
|   4 |     SORT UNIQUE       |                             |  7159K|    81M|   273M| 13626 |
|   5 |      TABLE ACCESS FULL| RE_R_CONEX_EXTERNAS_BIT_03  |  7159K|    81M|       |  2589 |
|*  6 |    INDEX UNIQUE SCAN  | PK_TMP_MANEL_03             |     1 |     5 |       |       |
|*  7 |   INDEX UNIQUE SCAN   | PK_R_NODOS_BIT_03           |     1 |     6 |       |       |
---------------------------------------------------------------------------------------------

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

   6 - access("B"."NODO_B_ID"="C"."NODO_ID")
   7 - access("A"."NODO_ID"="B"."NODO_A_ID")




El coste ahora parece haberse disparado por completo. 13626 unidades de coste. Respecto al coste anterior, de sólo 23. Pero ahora no aparece el cartesiano y parece que la ejecución es más fiel a lo que queremos.

Acabo de lanzar la ejecución: un minuto con veinte segundos.
Bien.

No hay comentarios:

Amazon