Re: query performance -- revised!

"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?

> 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 - 12:07:10 CDT