Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: unexpected merge cartesian join
If you check the plans closely, you'll see that my first guess was heading in the right direction. One query has to visit tables
M_SPESE_CORSO M_RIEPILOGO_ANNUALE M_VAL_INT T_AVANZAMENTO_CORSO
It's too messy to work out quickly - but
the critical point from your perspective
is that the Oracle is doing something
extraordinary in costing the merge Cartesian.
(And it's not doing what my example on
pages 365-387 says will happen, so I'm
going to have to get 9.2.0.7 installed and
see what the 10053 trace says for that example).
Since your problem is so far under so many layers of views, you can't really address it easily. (I assume you want to run the query against the subset of columns only). So you could try the following:
with big_query as (
select /*+ materialize */
*
from v_filter_254
where upper(f2227) like upper('%test%')
)
select ID,F184,X184,F520,F2227,F522,F32
from big_query
order by
f520, f184, f32
;
This should force Oracle into using the
plan without the hash join to create a
temporary table with the full rows in it,
from which you can select just the columns
you want.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 4th Nov 2005 <nicola.farina_at_info-line.it> wrote in message news:1133455973.014049.29630_at_o13g2000cwo.googlegroups.com...Received on Thu Dec 01 2005 - 11:36:25 CST
> 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 ...!!
>