Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: unexpected merge cartesian join

Re: unexpected merge cartesian join

From: <nicola.farina_at_info-line.it>
Date: 1 Dec 2005 08:52:53 -0800
Message-ID: <1133455973.014049.29630@o13g2000cwo.googlegroups.com>


Thanks for replying.
I tried used dbms_xplan.
This is the output for the version without * : select ID,F184,X184,F520,F2227,F522,F32
  from v_filter_254
 where upper(f2227) like upper('%test%') order by f520, f184, f32;



| Id | Operation | Name |
Rows | Bytes | Cost |

| 0 | SELECT STATEMENT | |
 55 | 15290 | 72 |
| 1 | SORT ORDER BY | |
 55 | 15290 | 72 |
| 2 | NESTED LOOPS OUTER | |
 55 | 15290 | 68 |
|*  3 |    HASH JOIN OUTER               |                          |
 55 | 15125 |    68 |
|*  4 |     HASH JOIN OUTER              |                          |
 55 |  7700 |    58 |
|*  5 |      HASH JOIN OUTER             |                          |
 55 |  6160 |    50 |
|*  6 |       HASH JOIN OUTER            |                          |
 55 |  4675 |    42 |
|*  7 |        HASH JOIN OUTER           |                          |
 55 |  3190 |    34 |
|*  8 |         HASH JOIN OUTER          |                          |
 55 |  2420 |    26 |
|*  9 |          TABLE ACCESS FULL       | A_CORSO                  |
 55 |  2145 |     7 |

| 10 | VIEW | V_CORSI_SPESE_INT_BASE |
200 | 1000 | 18 |
| 11 | SORT GROUP BY | |
200 | 6400 | 18 | |* 12 | HASH JOIN OUTER | | 335 | 10720 | 14 |
| 13 | NESTED LOOPS | |
323 | 7429 | 7 |
| 14 | MERGE JOIN CARTESIAN| |
1066K| 18M| 7 |
| 15 | NESTED LOOPS | |
2939 | 38207 | 7 | |* 16 | TABLE ACCESS FULL | A_CORSO | 2939 | 26451 | 7 | |* 17 | INDEX UNIQUE SCAN | IU1_M_RIEPILOGO_ANNUALE | 1 | 4 | |
| 18 | BUFFER SORT | |
363 | 1815 | 7 |
| 19 | INDEX FULL SCAN | I_M_SPESE_CORSO_0 |
363 | 1815 | | |* 20 | INDEX UNIQUE SCAN | UI_A_CORSO_0 | 1 | 5 | | |* 21 | INDEX FAST FULL SCAN | U1_M_VAL_INT | 557 | 5013 | 6 |
| 22 | TABLE ACCESS FULL | A_CORSO |
3300 | 46200 | 7 | |* 23 | TABLE ACCESS FULL | M_VAL_STR | 1 | 27 | 7 | |* 24 | TABLE ACCESS FULL | M_VAL_STR | 1 | 27 | 7 | |* 25 | TABLE ACCESS FULL | M_VAL_INT | 2 | 56 | 7 |
| 26 | VIEW | V_DIPARTIMENTI |
2 | 270 | 9 |
| 27 | SORT UNIQUE | |
2 | 80 | 9 |
| 28 | UNION-ALL | |
| | | |* 29 | TABLE ACCESS FULL | TAB_CDCF | 1 | 57 | 3 |
| 30 | TABLE ACCESS FULL | T_DIPARTIMENTI_BUDGET |
1 | 23 | 2 | |* 31 | INDEX UNIQUE SCAN | PK_T_AVANZAMENTO_CORSO | 1 | 3 | | ---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   3 - access("M3"."DD_VALORE_STRINGA"="V4"."ID_CDCF"(+))
   4 - access("M6"."CT_MASTER"(+)="A"."ID_OUVERTURE")
   5 - access("M3"."CT_MASTER"(+)="A"."ID_OUVERTURE")
   6 - access("M5"."CT_MASTER"(+)="A"."ID_OUVERTURE")
   7 - access("A"."ID_OUVERTURE"(+)="AC"."ID_OUVERTURE")
   8 - access("VCSIB"."CV_CORSI_EDIZIONI"(+)="AC"."ID_OUVERTURE")
   9 - filter(UPPER("AC"."DESCRIZIONE") LIKE '%TEST%' AND
"AC"."CD_TIPO"='I')
  12 - access("MVI"."CT_MASTER"(+)="MSC"."CV_CORSI_EDIZIONI")
  16 - filter("A"."ANNORIFERIMENTO" IS NOT NULL)
  17 - access("A"."ANNORIFERIMENTO"="MRA"."NR_ANNO")
  20 - access("A"."ID_OUVERTURE"="A"."ID_OUVERTURE")
       filter("MSC"."CV_CORSI_EDIZIONI"="A"."ID_OUVERTURE")
  21 - filter("MVI"."CA_TABLE_CATALOGO"(+)=240)   23 - filter("M5"."CA_TABLE_CATALOGO"(+)=522 AND

"M5"."DT_INIZIO"(+)<=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy') AND "M5"."DT_FINE"(+)>=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy'))   24 - filter("M3"."CA_TABLE_CATALOGO"(+)=184 AND

"M3"."DT_INIZIO"(+)<=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy') AND "M3"."DT_FINE"(+)>=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy'))   25 - filter("M6"."CA_TABLE_CATALOGO"(+)=298 AND

"M6"."DT_INIZIO"(+)<=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy') AND "M6"."DT_FINE"(+)>=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy'))   29 - filter(TO_NUMBER("TAB_CDCF"."LIVELLO3")=0 AND TO_NUMBER("TAB_CDCF"."LIVELLO4")=0 AND
              TO_NUMBER("TAB_CDCF"."LIVELLO2")<>0)   31 - access("M6"."NR_VALORE_INTERO"="T7"."ID"(+))

Note: cpu costing is off

68 rows selected.

This is the select * version:



| Id | Operation | Name
 | Rows | Bytes | Cost |

| 0 | SELECT STATEMENT |
 | 55 | 19085 | 77 |
| 1 | SORT ORDER BY |
 | 55 | 19085 | 77 |
| 2 | NESTED LOOPS OUTER |
 | 55 | 19085 | 72 |
|*  3 |    HASH JOIN OUTER                     |
 |    55 | 17985 |    72 |
|*  4 |     HASH JOIN OUTER                    |
 |    55 | 10560 |    62 |
|*  5 |      HASH JOIN OUTER                   |
 |    55 |  9020 |    54 |
|*  6 |       HASH JOIN OUTER                  |
 |    55 |  7535 |    46 |
|*  7 |        HASH JOIN OUTER                 |
 |    55 |  6050 |    38 |
|*  8 |         HASH JOIN OUTER                |
 |    55 |  5280 |    30 |
|*  9 |          TABLE ACCESS FULL             | A_CORSO
 |    55 |  2145 |     7 |

| 10 | VIEW | V_CORSI_SPESE_INT_BASE
| 200 | 11400 | 22 |
| 11 | SORT GROUP BY |
| 200 | 9200 | 22 | |* 12 | HASH JOIN OUTER | | 335 | 15410 | 18 |
| 13 | TABLE ACCESS BY INDEX ROWID| M_SPESE_CORSO
| 1 | 13 | |
| 14 | NESTED LOOPS |
| 323 | 10659 | 10 |
| 15 | NESTED LOOPS |
| 2939 | 58780 | 10 | |* 16 | HASH JOIN | | 2939 | 44085 | 10 |
| 17 | TABLE ACCESS FULL | M_RIEPILOGO_ANNUALE
| 7 | 42 | 2 | |* 18 | TABLE ACCESS FULL | A_CORSO | 2939 | 26451 | 7 | |* 19 | INDEX UNIQUE SCAN | UI_A_CORSO_0 | 1 | 5 | | |* 20 | INDEX RANGE SCAN | I_M_SPESE_CORSO_0 | 2 | | | |* 21 | TABLE ACCESS FULL | M_VAL_INT | 557 | 7241 | 7 |
| 22 | TABLE ACCESS FULL | A_CORSO
| 3300 | 46200 | 7 | |* 23 | TABLE ACCESS FULL | M_VAL_STR | 1 | 27 | 7 | |* 24 | TABLE ACCESS FULL | M_VAL_STR | 1 | 27 | 7 | |* 25 | TABLE ACCESS FULL | M_VAL_INT | 2 | 56 | 7 |
| 26 | VIEW | V_DIPARTIMENTI
| 2 | 270 | 9 |
| 27 | SORT UNIQUE |
| 2 | 80 | 9 |
| 28 | UNION-ALL |
| | | | |* 29 | TABLE ACCESS FULL | TAB_CDCF | 1 | 57 | 3 |
| 30 | TABLE ACCESS FULL | T_DIPARTIMENTI_BUDGET
| 1 | 23 | 2 |
| 31 | TABLE ACCESS BY INDEX ROWID | T_AVANZAMENTO_CORSO
| 1 | 20 | | |* 32 | INDEX UNIQUE SCAN | PK_T_AVANZAMENTO_CORSO | 1 | | | --------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   3 - access("M3"."DD_VALORE_STRINGA"="V4"."ID_CDCF"(+))
   4 - access("M6"."CT_MASTER"(+)="A"."ID_OUVERTURE")
   5 - access("M3"."CT_MASTER"(+)="A"."ID_OUVERTURE")
   6 - access("M5"."CT_MASTER"(+)="A"."ID_OUVERTURE")
   7 - access("A"."ID_OUVERTURE"(+)="AC"."ID_OUVERTURE")
   8 - access("VCSIB"."CV_CORSI_EDIZIONI"(+)="AC"."ID_OUVERTURE")
   9 - filter(UPPER("AC"."DESCRIZIONE") LIKE '%TEST%' AND
"AC"."CD_TIPO"='I')
  12 - access("MVI"."CT_MASTER"(+)="MSC"."CV_CORSI_EDIZIONI")
  16 - access("A"."ANNORIFERIMENTO"="MRA"."NR_ANNO")
  18 - filter("A"."ANNORIFERIMENTO" IS NOT NULL)
  19 - access("A"."ID_OUVERTURE"="A"."ID_OUVERTURE")
  20 - access("MSC"."CV_CORSI_EDIZIONI"="A"."ID_OUVERTURE")
  21 - filter("MVI"."CA_TABLE_CATALOGO"(+)=240)
  23 - filter("M5"."CA_TABLE_CATALOGO"(+)=522 AND

"M5"."DT_INIZIO"(+)<=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy') AND "M5"."DT_FINE"(+)>=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy'))   24 - filter("M3"."CA_TABLE_CATALOGO"(+)=184 AND

"M3"."DT_INIZIO"(+)<=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy') AND "M3"."DT_FINE"(+)>=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy'))   25 - filter("M6"."CA_TABLE_CATALOGO"(+)=298 AND

"M6"."DT_INIZIO"(+)<=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy') AND "M6"."DT_FINE"(+)>=TO_DATE(SYS_CONTEXT('userenv','client_info'),'dd/mm/yyyy'))   29 - filter(TO_NUMBER("TAB_CDCF"."LIVELLO3")=0 AND TO_NUMBER("TAB_CDCF"."LIVELLO4")=0 AND
              TO_NUMBER("TAB_CDCF"."LIVELLO2")<>0)   32 - access("M6"."NR_VALORE_INTERO"="T7"."ID"(+))

Note: cpu costing is off

69 rows selected.

Thanks for any tips

PS
Yesterday the postman brings me my copy of CBO fundamentals but I am still on the
initial pages ...!! Received on Thu Dec 01 2005 - 10:52:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US