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: [HELP] unexpected merge cartesian join

Re: [HELP] unexpected merge cartesian join

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 01 Dec 2005 08:50:15 -0800
Message-ID: <1133455801.410577@jetspin.drizzle.com>


nicola.farina_at_info-line.it wrote:
> Hello all
>
> I desperately try to understand why appears a merge cartesian join in a
> query
> changing the select fields.
> The query is
>
> select ID,F184,X184,F520,F2227,F522,F32
> from v_filter_254
> where upper(f2227) like upper('%test%')
> order by f520, f184, f32
>
> For this query CBO uses a merge cartesian join
>
> BUT
>
> The same query in this form does not use a merge cartesian join
>
> select *
> from v_filter_254
> where upper(f2227) like upper('%test%')
> order by f520, f184, f32
>
> I am using 9.2.0.7 under windows 2000 server
>
> All tables are analyzed with the command
> dbms_stats.gather_schema_stats(user, cascade=>'True', method_opt='for
> all indexed columns size 254')
>
> Looking at the tkprof output I saw this for the "no-*" version :
>
> SELECT STATEMENT 0
> SORT ORDER BY 1
> NESTED LOOPS OUTER 2 1
> HASH JOIN OUTER 3 2
> HASH JOIN OUTER 4 3
> HASH JOIN OUTER 5 4
> HASH JOIN OUTER 6 5
> HASH JOIN OUTER 7 6
> HASH JOIN OUTER 8 7
> TABLE ACCESS A_CORSO FULL 9 8
> VIEW V_CORSI_SPESE_IN 10 8
> SORT GROUP BY 11 10
> HASH JOIN OUTER 12 11
> TABLE ACCESS M_SPESE_CORSO BY INDEX ROWID 13 12
> NESTED LOOPS 14 13
> NESTED LOOPS 15 14
> HASH JOIN 16 15
> TABLE ACCESS M_RIEPILOGO_ANNUFULL 17 16
> TABLE ACCESS A_CORSO FULL 18 16
> INDEX UI_A_CORSO_0 UNIQUE SCAN 19 15
> INDEX I_M_SPESE_CORSO_RANGE SCAN 20 14
> TABLE ACCESS M_VAL_INT FULL 21 12
> TABLE ACCESS A_CORSO FULL 22 7
> TABLE ACCESS M_VAL_STR FULL 23 6
> TABLE ACCESS M_VAL_STR FULL 24 5
> TABLE ACCESS M_VAL_INT FULL 25 4
> VIEW V_DIPARTIMENTI 26 3
> SORT UNIQUE 27 26
> UNION-ALL 28 27
> TABLE ACCESS TAB_CDCF FULL 29 28
> TABLE ACCESS T_DIPARTIMENTI_BFULL 30 28
> TABLE ACCESS T_AVANZAMENTO_COBY INDEX ROWID 31 2
> INDEX PK_T_AVANZAMENTOUNIQUE SCAN 32 31
>
> While in the "select *" version I got:
>
> SELECT STATEMENT 0
> SORT ORDER BY 1
> NESTED LOOPS OUTER 2 1
> HASH JOIN OUTER 3 2
> HASH JOIN OUTER 4 3
> HASH JOIN OUTER 5 4
> HASH JOIN OUTER 6 5
> HASH JOIN OUTER 7 6
> HASH JOIN OUTER 8 7
> TABLE ACCESS A_CORSO FULL 9 8
> VIEW V_CORSI_SPESE_IN 10 8
> SORT GROUP BY 11 10
> HASH JOIN OUTER 12 11
> NESTED LOOPS 13 12
> MERGE JOIN CARTESIAN 14 13
> NESTED LOOPS 15 14
> TABLE ACCESS A_CORSO FULL 16 15
> INDEX IU1_M_RIEPILOGO_UNIQUE SCAN 17 15
> BUFFER SORT 18 14
> INDEX I_M_SPESE_CORSO_FULL SCAN 19 18
> INDEX UI_A_CORSO_0RSO_UNIQUE SCAN 20 13
> INDEX U1_M_VAL_INTRSO_FAST FULL SCAN 21 12
> TABLE ACCESS A_CORSO FULL 22 7
> TABLE ACCESS M_VAL_STR FULL 23 6
> TABLE ACCESS M_VAL_STR FULL 24 5
> TABLE ACCESS M_VAL_INT FULL 25 4
> VIEW V_DIPARTIMENTI 26 3
> SORT UNIQUE 27 26
> UNION-ALL 28 27
> TABLE ACCESS TAB_CDCF FULL 29 28
> TABLE ACCESS T_DIPARTIMENTI_BFULL 30 28
> INDEX PK_T_AVANZAMENTOUNIQUE SCAN 31 2
>
> Any help will be very appreciated!
> I simply don't understand why using * should be different from using a
> subset of the
> fields!
> Thanks in advance
> Bye
> Nicola

There is no relationship between the SQL statement you posted and the resulting explain plan.

 > select ID,F184,X184,F520,F2227,F522,F32
 >  from v_filter_254
 > where upper(f2227) like upper('%test%')
 > order by f520, f184, f32

Does not join anything ... you've only got one table.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Dec 01 2005 - 10:50:15 CST

Original text of this message

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