Re: Optimization of query finding related products for a certain product
Date: Mon, 10 Sep 2001 19:25:03 GMT
"Bob Badour" <bbadour_at_golden.net> 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