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