Optimización consultas SQL con Oracle

Otro producto cartesiano...

2005-10-25T10:53:00.000+02:00

Para Nacho, por haber descubierto la pieza que faltaba. :-P

La siguiente consulta se ha cancelado tras 15 horas y 37 minutos de ejecución.

SQL> select count(*) from (SELECT BITOWN03.BS_C_CLIENTES_BIT_03.DNICIF_DE,
  2    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID,
  3    HSCONTRATOS_ESTADOS.ESTADO_DE,
  4    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT,
  5    BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID
  6  FROM
  7    BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
  8    BITOWN03.LK_C_ESTADOS_BIT_03  HSCONTRATOS_ESTADOS,
  9    BITOWN03.BS_C_CLIENTES_BIT_03,
10    BITOWN03.BS_C_CONTRATOS_BIT_03  CONTRATOS
11  WHERE  BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID          = CONTRATOS.ABONADO_ID
12    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID = CONTRATOS.CONTRATO_ID
13    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID   = HSCONTRATOS_ESTADOS.ESTADO_ID
14    AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT
15                   BETWEEN  to_date('01-06-2005 00:00:00','DD-MM-YYYY HH24:MI:SS')
16                       AND  to_date('30-06-2005 23:59:59','DD-MM-YYYY HH24:MI:SS')
17   AND  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID  =  'BA');
  BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
           *
ERROR at line 7:
ORA-00028: your session has been killed

Elapsed: 15:37:01.66

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                    |                               |   461 | 24433 |    16 |       |       |
|   1 |  NESTED LOOPS                       |                               |   461 | 24433 |    16 |       |       |
|   2 |   MERGE JOIN CARTESIAN              |                               |  1448M|    58G|    16 |       |       |
|   3 |    NESTED LOOPS                     |                               |   971 | 35927 |    16 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID     | LK_C_ESTADOS_BIT_03           |     1 |    20 |     1 |       |       |
|*  5 |      INDEX UNIQUE SCAN              | PK_C_ESTADOS_BIT_03           |     1 |       |       |       |       |
|*  6 |     INDEX RANGE SCAN                | IDX_FECHA_ESTADO_CONT_JAVIER  |   971 | 16507 |    15 |       |       |
|   7 |    BUFFER SORT                      |                               |  1491K|  8739K|     1 |       |       |
|   8 |     INDEX FULL SCAN                 | PK_C_CLIENTES_BIT_03          |  1491K|  8739K|       |       |       |
|*  9 |   TABLE ACCESS BY GLOBAL INDEX ROWID| BS_C_CONTRATOS_BIT_03         |     1 |    10 |       | ROWID | ROW L |
|* 10 |    INDEX UNIQUE SCAN                | PK_C_CONTRATOS_BIT_03         |     1 |       |       |       |       |
---------------------------------------------------------------------------------------------------------------------

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

   5 - access("HSCONTRATOS_ESTADOS"."ESTADO_ID"='BA')
   6 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT">=TO_DATE('2005-06-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA' AND
              "HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT"<=TO_DATE('2005-06-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
       filter("HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"="HSCONTRATOS_ESTADOS"."ESTADO_ID" AND
              "HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA')
   9 - filter("BS_C_CLIENTES_BIT_03"."ABONADO_ID"="CONTRATOS"."ABONADO_ID")
  10 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."CONTRATO_ID"="CONTRATOS"."CONTRATO_ID")

Note: cpu costing is off

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

Las tablas contienen estadísticas fieles. El producto cartesiano tampoco engaña: cruzará 58 Gb. para atender nuestra petición. De todas maneras, algo falla. Ese plan no es óptimo.

Las estadísticas se generan mediante la siguiente llamada a DBMS_STATS.

SQL> begin
  2      DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>usuario,
  3         TABNAME=>tabla,
  4         ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
  5         METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1',
  6         DEGREE=>4);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Es preciso no únicamente analizar las tablas, sino también los índices. Algunos de ellos están analizados con el procedimiento DBMS_STATS.GATHER_INDEX_STATS, pero una de las tablas está particionada. Lo mejor y más simple es incluir el parámetro CASCADE=>TRUE en la llamada a DBMS_STATS.

Es cierto que el coste de análisis de estas tablas incrementará. Además, el parámetro CASCADE afecta, no sólo a índices, sino también a vistas materializadas, particiones, etc. (es decir, a todas sus dependencias).

Con las tablas analizadas de este modo, el nuevo plan de ejecución es éste:

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

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

------------------------------------------------------------------------------------------------
| Id  | Operation                      |  Name                         | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                               |   471 | 30615 |   405 |
|*  1 |  HASH JOIN                     |                               |   471 | 30615 |   405 |
|*  2 |   INDEX RANGE SCAN             | IDX_FECHA_ESTADO_CONT_JAVIER  |  8740 |   145K|    75 |
|   3 |   NESTED LOOPS                 |                               |   771K|    35M|   257 |
|   4 |    NESTED LOOPS                |                               |  1626K|    46M|   257 |
|   5 |     TABLE ACCESS BY INDEX ROWID| LK_C_ESTADOS_BIT_03           |     1 |    20 |     1 |
|*  6 |      INDEX UNIQUE SCAN         | PK_C_ESTADOS_BIT_03           |     1 |       |       |
|   7 |     INDEX FAST FULL SCAN       | IDX_ABONADO_CONTRATO          |  1626K|    15M|   256 |
|   8 |    TABLE ACCESS BY INDEX ROWID | BS_C_CLIENTES_BIT_03          |     1 |    18 |       |
|*  9 |     INDEX UNIQUE SCAN          | PK_C_CLIENTES_BIT_03          |     1 |       |       |
------------------------------------------------------------------------------------------------

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

   1 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."CONTRATO_ID"="CONTRATOS"."CONTRATO_ID" AND
              "HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"="HSCONTRATOS_ESTADOS"."ESTADO_ID")
   2 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT">=TO_DATE('2005-06-01 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND "HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA' AND
              "HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT"<=TO_DATE('2005-06-30 23:59:59', 'yyyy-mm-dd
              hh24:mi:ss'))
       filter("HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA')
   6 - access("HSCONTRATOS_ESTADOS"."ESTADO_ID"='BA')
   9 - access("BS_C_CLIENTES_BIT_03"."ABONADO_ID"="CONTRATOS"."ABONADO_ID")

Note: cpu costing is off

30 rows selected.

La ejecución ahora sólo tarda: 3 segundos.
El coste de análisis aumenta de 26 minutos a 32 minutos. La ganancia justifica el coste.

Muy bien. :-)

NOT IN vs. NOT EXISTS.

2005-10-17T18:26:00.000+02:00

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. :-)

Dílo de otra forma.

2005-10-13T16:18:00.000+02:00

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.

Mala Cardinalidad.

2005-10-11T13:26:00.000+02:00

Para Cristina Álvarez, quien confió en mi desde el primer momento y lo disimuló con toda su alma. :)

La siguiente consulta ha sido cancelada tras una hora y veinte minutos de ejecución.

SELECT N2.ENTIDAD_ID,
       CLI.rowid row_id, cli.*,
       MAX(REL.ABONADO_PADRE_ID) OVER (PARTITION BY ABONADO_HIJO_ID) REL_ABONADO_PADRE_ID
FROM BITOWN03.BS_V_MGEC_NODO_BIT_03 N1,
     BITOWN03.BS_V_MGEC_NODO_BIT_03 N2,
     BITOWN03.BS_V_MGEC_REL_NODOS_BIT_03 RN,
     BITOWN02.TM_C_CLIENTES_SAC_02 CLI,
     BITOWN03.RE_C_RELACIONES_ABONADO_BIT_03 REL
WHERE N1.ENTIDAD_ID= CLI.COD_DNICIF
  AND N1.NODO_ID=RN.NODO_ID
  AND RN.TIPO_RELACION_ID=2 AND RN.FECHA_FIN_DT IS NULL
  AND RN.NODO_PADRE_ID=N2.NODO_ID
  AND CLI.COD_ABONADO = REL.ABONADO_HIJO_ID (+);

con el siguiente plan de ejecución:

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

---------------------------------------------------------------------------------------------------
| Id  | Operation                       |  Name                           | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                                 |     1 |   278 |  2496 |
|   1 |  SORT UNIQUE                    |                                 |     1 |   278 |  2496 |
|   2 |   WINDOW SORT                   |                                 |     1 |   278 |  2496 |
|   3 |    NESTED LOOPS                 |                                 |     1 |   278 |  2479 |
|   4 |     NESTED LOOPS                |                                 |     1 |   261 |  2478 |
|   5 |      NESTED LOOPS OUTER         |                                 |     1 |   244 |  2477 |
|   6 |       MERGE JOIN CARTESIAN      |                                 |     1 |   232 |  2477 |
|*  7 |        TABLE ACCESS FULL        | BS_V_MGEC_REL_NODOS_BIT_03      |     1 |    22 |    70 |
|   8 |        BUFFER SORT              |                                 |  1486K|   297M|  2407 |
|   9 |         TABLE ACCESS FULL       | TM_C_CLIENTES_SAC_02            |  1486K|   297M|  2407 |
|* 10 |       INDEX FULL SCAN           | PK_C_RELACIONES_ABONADO_BIT_03  |     1 |    12 |       |
|* 11 |      TABLE ACCESS BY INDEX ROWID| BS_V_MGEC_NODO_BIT_03           |     1 |    17 |     1 |
|* 12 |       INDEX UNIQUE SCAN         | PK_V_MGEC_NODO_BIT_03           |     1 |       |       |
|  13 |     TABLE ACCESS BY INDEX ROWID | BS_V_MGEC_NODO_BIT_03           |     1 |    17 |     1 |
|* 14 |      INDEX UNIQUE SCAN          | PK_V_MGEC_NODO_BIT_03           |     1 |       |       |
---------------------------------------------------------------------------------------------------

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

   7 - filter("RN"."TIPO_RELACION_ID"=2 AND "RN"."FECHA_FIN_DT" IS NULL)
  10 - access("CLI"."COD_ABONADO"="REL"."ABONADO_HIJO_ID"(+))
       filter("CLI"."COD_ABONADO"="REL"."ABONADO_HIJO_ID"(+))
  11 - filter("N1"."ENTIDAD_ID"="CLI"."COD_DNICIF")
  12 - access("N1"."NODO_ID"="RN"."NODO_ID")
  14 - access("RN"."NODO_PADRE_ID"="N2"."NODO_ID")

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

Omitiendo el detalle que la cláusula distinct sobra. La mantenemos para que los planes resulten de ejecuciones similares.

Parece un plan bastante aceptable. No obstante, un producto cartesiano llama la atención... ¿únicamente se obtiene una fila? en ese caso, el cartesiano no hace daño. Vamos a consultar el total de filas de la tabla, si la tabla tiene generadas estadísticas (la columna num_rows de user_tables puede darnos una pista) y si el filtro de la operación 7 realmente resuelve una única fila.

SQL> select count(*) from bitown03.BS_V_MGEC_REL_NODOS_BIT_03;

COUNT(*)
----------
118907

SQL> select table_name, num_rows from dba_tables where
2 table_name='BS_V_MGEC_REL_NODOS_BIT_03';

TABLE_NAME NUM_ROWS
------------------------------ ----------
BS_V_MGEC_REL_NODOS_BIT_03 118907

SQL> select count(*) from bitown03.BS_V_MGEC_REL_NODOS_BIT_03
2 where TIPO_RELACION_ID=2 AND FECHA_FIN_DT IS NULL; --> FILTRO OPERACION 7

COUNT(*)
----------
12844

Pues parece que no... Se está produciendo un producto cartesiano de 1,5 millones de filas (operación 9 TABLE FULL SCAN) sobre 12844 elementos. Oracle está estimando mal la cardinalidad de las filas. Cree que con el filtro 7 únicamente obtendrá una fila y por eso el producto cartesiano no dispara el coste.

Graso error.

Es preciso analizar CORRECTAMENTE las tablas implicadas, indicando que se analicen también los valores de las columnas implicadas. Los comandos para realizar ese análisis son:

SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'usuario',TABNAME=>'BS_V_MGEC_REL_NODOS_BIT_03',METHOD_OPT=>'for all columns');

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.71
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'usuario',TABNAME=>'BS_V_MGEC_NODO_BIT_03',METHOD_OPT=>'for all columns');

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.57

De esta forma, las estadísticas tienen también información sobre la cardinalidad de las columnas y el plan de ejecución cambia sustancialmente:

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

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

---------------------------------------------------------------------------------------------------
| Id  | Operation               |  Name                           | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                                 |   209K|    53M|       | 12462 |
|   1 |  SORT UNIQUE            |                                 |   209K|    53M|   112M| 12462 |
|   2 |   WINDOW SORT           |                                 |   209K|    53M|   112M| 12462 |
|   3 |    NESTED LOOPS OUTER   |                                 |   209K|    53M|       |  4217 |
|*  4 |     HASH JOIN           |                                 |   209K|    51M|       |  4217 |
|*  5 |      HASH JOIN          |                                 | 12841 |   589K|       |   320 |
|*  6 |       HASH JOIN         |                                 | 12841 |   388K|       |   192 |
|*  7 |        TABLE ACCESS FULL| BS_V_MGEC_REL_NODOS_BIT_03      | 12841 |   188K|       |    70 |
|   8 |        TABLE ACCESS FULL| BS_V_MGEC_NODO_BIT_03           |   128K|  2012K|       |   112 |
|   9 |       TABLE ACCESS FULL | BS_V_MGEC_NODO_BIT_03           |   128K|  2012K|       |   112 |
|  10 |      TABLE ACCESS FULL  | TM_C_CLIENTES_SAC_02            |  1486K|   297M|       |  2407 |
|* 11 |     INDEX FULL SCAN     | PK_C_RELACIONES_ABONADO_BIT_03  |     1 |    12 |       |       |
---------------------------------------------------------------------------------------------------

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

   4 - access("N1"."ENTIDAD_ID"="CLI"."COD_DNICIF")
   5 - access("RN"."NODO_PADRE_ID"="N2"."NODO_ID")
   6 - access("N1"."NODO_ID"="RN"."NODO_ID")
   7 - filter("RN"."TIPO_RELACION_ID"=2 AND "RN"."FECHA_FIN_DT" IS NULL)
  11 - access("CLI"."COD_ABONADO"="REL"."ABONADO_HIJO_ID"(+))
       filter("CLI"."COD_ABONADO"="REL"."ABONADO_HIJO_ID"(+))

Note: cpu costing is off

Efectivamente, el coste del plan es mayor, pero REAL.
Sólo ha tardado 23 segundos!!