Re: Optimization of query finding related products for a certain product

From: Anurag Varma <>
Date: Mon, 10 Sep 2001 19:25:03 GMT
Message-ID: <js8n7.91097$>


"Bob Badour" <> wrote in message
> Yuck! Take a weak optimizer and render it completely useless? Do you
> really think that's helpfull
> If, after all of that, the optimizer simply handles "IN" clauses
> poorly, I would suggest trying logical equivalents using "= ANY" or
> "EXISTS", which some dbmses handle better.

Usually (maybe always) Oracle handles =ANY same way as IN. If the parent query is returning a lot of rows then using EXISTS might be worse!

> If logically correct,
> replace the whole subquery with a join.

I won't be surprised if Oracle optimizer is transforming the IN clause into a join.

> If the dbms supports
> subqueries in the "FROM" clause, rewrite the query using a join
> between VISIT and the subquery.
> I wouldn't recommend using cursors under any circumstances. Using
> procedural methods prevents the optimizer from ever doing its job.

Whoever said that!! Though in this case, it might not bring any improvement. There are *some* cases in which using procedural method is the best way to go.

Anurag Received on Mon Sep 10 2001 - 21:25:03 CEST

Original text of this message