lunes, octubre 17, 2005

Optimización SQL. Ejemplo 2. NOT IN vs. NOT EXISTS.

Para Fernando, por la que le espera. :-P


La siguiente consulta se ha cancelado tras 5 horas y 11 minutos en ejecutarse.

SELECT count(CLI.COD_ABONADO)
   FROM BITOWN02.TM_C_CLIENTES_SAC_02 CLI,
        BITOWN02.TE_ERRORES_BIT_02 TE
   WHERE CLI.ROWID = TE.FILA_ID (+)
     AND 'TM_C_CLIENTES_SAC_02' = TE.TABLA_DE (+)
     AND TE.ERROR_ID IS NULL
   AND CLI.COD_ABONADO NOT IN (
   SELECT CU.ABONADO_id
     FROM BITOWN03.BS_C_CONTRATOS_BIT_03 CONT, BITOWN03.BS_C_CUENTAS_BIT_03 CU
    WHERE CU.CUENTA_ID=CONT.CUENTA_ID
  );


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

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

---------------------------------------------------------------------------------------------------------
| Id  | Operation                      |  Name                  | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        |     1 |    44 |  3343 |       |       |
|   1 |  SORT AGGREGATE                |                        |     1 |    44 |       |       |       |
|*  2 |   FILTER                       |                        |       |       |       |       |       |
|*  3 |    FILTER                      |                        |       |       |       |       |       |
|*  4 |     HASH JOIN OUTER            |                        |       |       |       |       |       |
|   5 |      TABLE ACCESS FULL         | TM_C_CLIENTES_SAC_02   | 74323 |   870K|  2407 |       |       |
|   6 |      TABLE ACCESS FULL         | TE_M_ERRORES_BIT_01    |     1 |    32 |     2 |    13 |    13 |
|   7 |    NESTED LOOPS                |                        |  1640K|    50M|   922 |       |       |
|   8 |     PARTITION LIST ALL         |                        |       |       |       |     1 |     7 |
|   9 |      TABLE ACCESS FULL         | BS_C_CONTRATOS_BIT_03  |  1640K|    20M|   922 |     1 |     7 |
|* 10 |     TABLE ACCESS BY INDEX ROWID| BS_C_CUENTAS_BIT_03    |     1 |    19 |       |       |       |
|* 11 |      INDEX UNIQUE SCAN         | PK_C_CUENTAS_BIT_03    |     1 |       |       |       |       |
---------------------------------------------------------------------------------------------------------


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

   2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "BITOWN03"."BS_C_CUENTAS_BIT_03"
              "CU","BITOWN03"."BS_C_CONTRATOS_BIT_03" "CONT" WHERE "CU"."CUENTA_ID"="CONT"."CUENTA_ID" AND
              LNNVL("CU"."ABONADO_ID"<>:B1)))
   3 - filter("TE_M_ERRORES_BIT_01"."ERROR_ID" IS NULL)
   4 - access("SYS_ALIAS_1".ROWID="TE_M_ERRORES_BIT_01"."FILA_ID"(+))
  10 - filter(LNNVL("CU"."ABONADO_ID"<>:B1))
  11 - access("CU"."CUENTA_ID"="CONT"."CUENTA_ID")

Note: cpu costing is off

30 rows selected.


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


En este caso, la consulta con IN tiene un coste aceptable. No obstante, después de cinco horas de ejecución, es de sospechar que la cosa no va muy bien. La estrategia de ejecución es realizar dos FILTER: el primero para el OuterJoin de Clientes sin errores y el segundo para combinarlo (en un pesadísimo NestedLoops) con las cuentas con contratos.

Como la subconsulta está resultando más pesada incluso que la principal, es posible que sustituir IN por la cláusula EXISTS sea una buena estrategia.

Sí, también tengo cuidado que no haya códigos con valor NULL para resolver la consulta, ya que NOT IN y NOT EXISTS no son lo mismo.

Sustituyo NOT IN por NOT EXISTS y la consulta queda de este modo:



explain plan for
SELECT count(CLI.COD_ABONADO)
   FROM BITOWN02.TM_C_CLIENTES_SAC_02 CLI,
        BITOWN02.TE_ERRORES_BIT_02 TE
   WHERE CLI.ROWID = TE.FILA_ID (+)
     AND 'TM_C_CLIENTES_SAC_02' = TE.TABLA_DE (+)
     AND TE.ERROR_ID IS NULL
   AND not exists (
   SELECT null
   FROM BITOWN03.BS_C_CONTRATOS_BIT_03 CONT, BITOWN03.BS_C_CUENTAS_BIT_03 CU
  WHERE CLI.COD_ABONADO=CU.ABONADO_ID AND
        CU.CUENTA_ID=CONT.CUENTA_ID);


El plan de ejecución resultante parece ser similar al anterior, incluso su coste parece peor.


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

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

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                  | Rows  | Bytes |TempSpc| Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                        |     1 |    57 |       |  4946 |    |          |
|   1 |  SORT AGGREGATE                   |                        |     1 |    57 |       |       |    |          |
|*  2 |   FILTER                          |                        |       |       |       |       |    |          |
|*  3 |    HASH JOIN OUTER                |                        |       |       |       |       |    |          |
|*  4 |     HASH JOIN ANTI                |                        |  1486K|    35M|    34M|  4269 |    |          |
|   5 |      TABLE ACCESS FULL            | TM_C_CLIENTES_SAC_02   |  1486K|    17M|       |  2407 |    |          |
|   6 |      VIEW                         | VW_SQ_1                |  1640K|    20M|       |   922 |    |          |
|   7 |       NESTED LOOPS                |                        |  1640K|    50M|       |   922 |    |          |
|   8 |        PARTITION LIST ALL         |                        |       |       |       |       |  1 |        7 |
|   9 |         TABLE ACCESS FULL         | BS_C_CONTRATOS_BIT_03  |  1640K|    20M|       |   922 |  1 |        7 |
|  10 |        TABLE ACCESS BY INDEX ROWID| BS_C_CUENTAS_BIT_03    |     1 |    19 |       |       |    |          |
|* 11 |         INDEX UNIQUE SCAN         | PK_C_CUENTAS_BIT_03    |     1 |       |       |       |    |          |
|  12 |     TABLE ACCESS FULL             | TE_M_ERRORES_BIT_01    |     1 |    32 |       |     2 | 13 |       13 |
--------------------------------------------------------------------------------------------------------------------

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

   2 - filter("TE_M_ERRORES_BIT_01"."ERROR_ID" IS NULL)
   3 - access("CLI".ROWID="TE_M_ERRORES_BIT_01"."FILA_ID"(+))
   4 - access("CLI"."COD_ABONADO"="VW_SQ_1"."ABONADO_ID")
  11 - access("CU"."CUENTA_ID"="CONT"."CUENTA_ID")

Note: cpu costing is off


Acabo de lanzar la ejecución: 28 segundos.
Très bien. :-)

1 comentario:

Anonymous dijo...

gracias por la ayuda, como puedo grabar la fecha con milisegundos de esta consulta en un campo date
SELECT TO_CHAR(systimestamp, 'DD/MM/YYYY HH24:MI:SS FF3') fecha from dual
intente con el update pero sale error

Amazon