Re: query performance -- revised!

From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Thu, 29 May 2003 16:48:59 -0700
Message-ID: <m6xBa.13$No4.1092@news.oracle.com>

"Ryan" <rgaffuri_at_cox.net> wrote in message news:q3wBa.131283$823.104219_at_news1.east.cox.net...
> 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.

No matter what cardinalities of A and B are, optimizer is supposed to figure out the best join order and join method.

> 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.

Once again, a tuple iteration semantics of nested subquery is essentially a Nested Loop. Optimizer considers that Nested Loop among many other alternatives after it unnests subquery.

If optimizer is not able to figure out cardinalities and cost correctly, that's a completely different story. A suggestion that one can handle cases like this by rewriting a query is just promoring a hack. Received on Thu May 29 2003 - 18:48:59 CDT