[HELP] unexpected merge cartesian join

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 Received on Thu Dec 01 2005 - 09:54:05 CST