| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: unexpected merge cartesian join
In the case someone would like to help I post also the autotrace
output.
This is for the query version:
ilcorsi_at_NOVE> select *
2 from v_filter_254
3 where upper(f2227) like upper('%test%')
4 order by f520, f184, f32
5 ;
Execution Plan
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=83 Bytes=28635)
1 0
SORT (ORDER BY) (Cost=79 Card=83 Bytes=28635)
2 1
NESTED LOOPS (OUTER) (Cost=72 Card=83 Bytes=28635)
3 2
HASH JOIN (OUTER) (Cost=72 Card=83 Bytes=26975)
4 3
HASH JOIN (OUTER) (Cost=62 Card=83 Bytes=15770)
5 4
HASH JOIN (OUTER) (Cost=54 Card=83 Bytes=13446)
6 5
HASH JOIN (OUTER) (Cost=46 Card=83 Bytes=11205)
7 6
HASH JOIN (OUTER) (Cost=38 Card=83 Bytes=8964)
8 7
HASH JOIN (OUTER) (Cost=30 Card=83 Bytes=7802)
9 8
TABLE ACCESS (FULL) OF 'A_CORSO' (Cost=7 Card=83
Bytes=3071)
10 8
VIEW OF 'V_CORSI_SPESE_INT_BASE' (Cost=22 Card=195
Bytes=11115)
11 10
SORT (GROUP BY) (Cost=22 Card=195 Bytes=10530)
12 11
HASH JOIN (OUTER) (Cost=18 Card=195 Bytes=10530)
13 12
TABLE ACCESS (BY INDEX ROWID) OF
'M_SPESE_CORSO'
14 13
NESTED LOOPS (Cost=10 Card=188 Bytes=7708)
15 14
NESTED LOOPS (Cost=10 Card=1712
Bytes=34240)
16 15
HASH JOIN (Cost=10 Card=1712 Bytes=25680)
17 16
TABLE ACCESS (FULL) OF
'M_RIEPILOGO_ANNUALE' (Cost=2 Card=7 Bytes=42)
18 16
TABLE ACCESS (FULL) OF 'A_CORSO'
19 15
INDEX (UNIQUE SCAN) OF 'UI_A_CORSO_0'
INDEX (RANGE SCAN) OF 'I_M_SPESE_CORSO_0'
TABLE ACCESS (FULL) OF 'M_VAL_INT' (Cost=7
Card=557 Bytes=7241)
22 7
TABLE ACCESS (FULL) OF 'A_CORSO' (Cost=7 Card=3300
Bytes=46200)
23 6
TABLE ACCESS (FULL) OF 'M_VAL_STR' (Cost=7 Card=1 Bytes=27)
24 5
TABLE ACCESS (FULL) OF 'M_VAL_STR' (Cost=7 Card=1 Bytes=27)
25 4
TABLE ACCESS (FULL) OF 'M_VAL_INT' (Cost=7 Card=2 Bytes=56)
26 3
VIEW OF 'V_DIPARTIMENTI' (Cost=9 Card=2 Bytes=270)
27 26
SORT (UNIQUE) (Cost=9 Card=2 Bytes=80)
28 27
UNION-ALL 29 28
TABLE ACCESS (FULL) OF 'TAB_CDCF' (Cost=3 Card=1 Bytes=57)
30 28
TABLE ACCESS (FULL) OF 'T_DIPARTIMENTI_BUDGET' (Cost=2 Card=1 Bytes=23)
31 2
TABLE ACCESS (BY INDEX ROWID) OF 'T_AVANZAMENTO_CORSO' 32 31
INDEX (UNIQUE SCAN) OF 'PK_T_AVANZAMENTO_CORSO' (UNIQUE) Statistics
211 recursive calls
0 db block gets
3493 consistent gets
16 physical reads
0 redo size
2602 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
12 rows processed
ilcorsi_at_NOVE> quit
And this one is the other version the one with the cartesian merge join
ilcorsi_at_NOVE> select ID,F184,X184,F520,F2227,F522,F32
2 from v_filter_254
3 where upper(f2227) like upper('%test%')
4 order by f520, f184, f32;
12 rows selected.
Execution Plan
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=73 Card=83 Bytes=22908)
1 0
SORT (ORDER BY) (Cost=73 Card=83 Bytes=22908)
2 1
NESTED LOOPS (OUTER) (Cost=67 Card=83 Bytes=22908)
3 2
HASH JOIN (OUTER) (Cost=67 Card=83 Bytes=22659)
4 3
HASH JOIN (OUTER) (Cost=57 Card=83 Bytes=11454)
5 4
HASH JOIN (OUTER) (Cost=49 Card=83 Bytes=9130)
6 5
HASH JOIN (OUTER) (Cost=41 Card=83 Bytes=6889)
7 6
HASH JOIN (OUTER) (Cost=33 Card=83 Bytes=4648)
8 7
HASH JOIN (OUTER) (Cost=25 Card=83 Bytes=3486)
9 8
TABLE ACCESS (FULL) OF 'A_CORSO' (Cost=7 Card=83
Bytes=3071)
10 8
VIEW OF 'V_CORSI_SPESE_INT_BASE' (Cost=17 Card=195
Bytes=975)
11 10
SORT (GROUP BY) (Cost=17 Card=195 Bytes=6240)
12 11
HASH JOIN (OUTER) (Cost=14 Card=195 Bytes=6240)
13 12
NESTED LOOPS (Cost=7 Card=188 Bytes=4324)
14 13
MERGE JOIN (CARTESIAN) (Cost=7 Card=621456
Bytes=11186208)
15 14
NESTED LOOPS (Cost=7 Card=1712 Bytes=22256)
16 15
TABLE ACCESS (FULL) OF 'A_CORSO' (Cost=7
Card=2939 Bytes=26451)
17 15
INDEX (UNIQUE SCAN) OF
'IU1_M_RIEPILOGO_ANNUALE' (UNIQUE)
18 14
BUFFER (SORT) (Cost=7 Card=363 Bytes=1815)
19 18
INDEX (FULL SCAN) OF 'I_M_SPESE_CORSO_0'
INDEX (UNIQUE SCAN) OF 'UI_A_CORSO_0'
INDEX (FAST FULL SCAN) OF 'U1_M_VAL_INT'
22 7
TABLE ACCESS (FULL) OF 'A_CORSO' (Cost=7 Card=3300
Bytes=46200)
23 6
TABLE ACCESS (FULL) OF 'M_VAL_STR' (Cost=7 Card=1 Bytes=27)
24 5
TABLE ACCESS (FULL) OF 'M_VAL_STR' (Cost=7 Card=1 Bytes=27)
25 4
TABLE ACCESS (FULL) OF 'M_VAL_INT' (Cost=7 Card=2 Bytes=56)
26 3
VIEW OF 'V_DIPARTIMENTI' (Cost=9 Card=2 Bytes=270)
27 26
SORT (UNIQUE) (Cost=9 Card=2 Bytes=80)
28 27
UNION-ALL 29 28
TABLE ACCESS (FULL) OF 'TAB_CDCF' (Cost=3 Card=1 Bytes=57)
30 28
TABLE ACCESS (FULL) OF 'T_DIPARTIMENTI_BUDGET' (Cost=2 Card=1 Bytes=23)
31 2
INDEX (UNIQUE SCAN) OF 'PK_T_AVANZAMENTO_CORSO' (UNIQUE) Statistics
5167 recursive calls
0 db block gets
1062011 consistent gets
286 physical reads
0 redo size
1926 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
181 sorts (memory)
0 sorts (disk)
12 rows processed
ilcorsi_at_NOVE> spool off
And finally this is the tkprof output of the running query (the one with the cartesian merge)
Rows Row Source Operation
------- ---------------------------------------------------
12 SORT ORDER BY
12 NESTED LOOPS OUTER
12 HASH JOIN OUTER
12 HASH JOIN OUTER
12 HASH JOIN OUTER
12 HASH JOIN OUTER
12 NESTED LOOPS OUTER
12 HASH JOIN OUTER
12 TABLE ACCESS FULL OBJ#(1397027)
167 VIEW
167 SORT GROUP BY
298 HASH JOIN OUTER
298 NESTED LOOPS
1059960 MERGE JOIN CARTESIAN
2920 NESTED LOOPS
2939 TABLE ACCESS FULL OBJ#(1397027)
2920 INDEX UNIQUE SCAN OBJ#(1374394) (object id
1374394)
1059960 BUFFER SORT
363 INDEX FULL SCAN OBJ#(1374407) (object id
1374407)
298 INDEX UNIQUE SCAN OBJ#(1754386) (object id
1754386)
557 INDEX FAST FULL SCAN OBJ#(1374423) (object id
1374423)
12 TABLE ACCESS BY INDEX ROWID OBJ#(1397027)
12 INDEX UNIQUE SCAN OBJ#(1754386) (object id 1754386)
4 TABLE ACCESS FULL OBJ#(1374428)
286 TABLE ACCESS FULL OBJ#(1374428)
553 TABLE ACCESS FULL OBJ#(1374421)
21 VIEW
21 SORT UNIQUE
21 UNION-ALL
20 TABLE ACCESS FULL OBJ#(1400837)
1 TABLE ACCESS FULL OBJ#(1374473)
12 INDEX UNIQUE SCAN OBJ#(1374458) (object id 1374458)
There is a cardinality quite higher (1059960) for the merge step than
the expexted one (621456) at the step 14.
I tried playing with the statistics of the underlying tables (removing
the histogram on the indexed columns) and I got an extimated card much
more similar to the actual but....
the merge join remains !
And it remains also my question
*why* using select * did not get the merge join and using select id ...
get it ?????
Received on Thu Dec 01 2005 - 10:20:19 CST
 |
 |