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: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 1 Dec 2005 17:36:25 +0000 (UTC)
Message-ID: <dmncao$70h$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

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

in places where the other query can
stop at the index.

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

> 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 - 11:36:25 CST

Original text of this message

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