Re: query performance -- revised!

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:EdrBa.7$No4.133_at_news.oracle.com...
> "Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message
> news:1efdad5b.0305290306.568c50ea_at_posting.google.com...
> > I have no diea where this stuff with select 1 from dual comes from. I
> > have never seen that.
> >
> > however moving code to sub-queries and deciding to use an exists
> > clause works well and is documented on asktom and in harrisons sql
> > tuning book. It has worked for me before.
>
> Optimizer might decide to unnest "simple" subquery into inline view. Next,
> "simple" inline views are always automatically merged into containing
query.
> Unnesting almost always beneficial, because it opens additional access
> paths. You have to be careful with your suggestion, and make sure that
> optimiser wouldn't rewrite your query back to equivalent unnested form.
>
> > most of the improvements I have seen with the CBO is by considering
> > the relative table sizes and how many rows you will return. There are
> > several approaches that are better or worse depending on that.
>
> I don't understand. What are "relative table sizes"? Do you mean affecting
> query plan by manually adjusting statistics, or something else?

If table A has 5 million rows and table B has 10 rows, and you want to return X number of values you do one thing.

If both tables have the same number of records you generally try something else. Its 'Cost',

Exists works better when the outer query has a smaller result set and less cardinality than the sub-query. The reverse is true for 'IN' and so on. Run it and try it.
>
> > Since his 'OR' is probably what is killing him. spinning it to a
> > sub-query may help.
>
> I don't understand your idea. If you mean putting all the values into an
> auxiliarry table, then you need an extra join. Or extra subquery (but,
> again, query with nested subquery is almost always inferior to equivalent
> query flattened to select-project-join form).
>
>
>
Received on Thu May 29 2003 - 17:44:38 CDT