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: Turkbear <john.g_at_dot.spamfree.com>
Date: Thu, 01 Dec 2005 11:05:19 -0600
Message-ID: <s7buo1dj2581smakbka88c6696hctc3047@4ax.com>


DA Morgan <damorgan_at_psoug.org> wrote:

>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.
I suspect that v_filter_254 is a view not a Table ( although the poster failed to mention that)..

Nicola, please post the View's code... Received on Thu Dec 01 2005 - 11:05:19 CST

Original text of this message

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