Re: [HELP] unexpected merge cartesian join

<nicola.farina_at_info-line.it> wrote in message news:1133452445.757769.153640_at_g49g2000cwa.googlegroups.com...
> 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
>

If you used "explain plan" and
"select * from table(dbms_xplan.display);" the output might be more readable, and
would include the predicates built into
the view. Then someone might be able
to work out something concrete.

Then you might include the text of the view definition, as this may help.

At a GUESS - you may be seeing the effect of Oracle eliminating redundant table visits. It is possible that the "*" forces Oracle to visit every single table, whereas the explicit list allows Oracle to acquire some data from index-only accesses. (I haven't checked the plan to see if this is true). Alternatively, it may be the effect that all those extra columns are having on the width of data that has to go into hash joins. Possibly you are just unlucky, and Oracle has decided that at some point a merge Cartesian for early elimination of data will work better than hauling a few dozen columns and a lot of rows into a large hash table.

-- 
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 29th Nov 2005