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

From: Anurag Varma <avdbi_at_nospam.hotmail.com>
Date: Mon, 10 Sep 2001 19:25:03 GMT
Message-ID: <js8n7.91097$tb.9032543@news02.optonline.net>

Hi,

"Bob Badour" <bbadour_at_golden.net> wrote in message news:cd3b3cf.0109091615.41062c6e_at_posting.google.com...
>
> 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 - 14:25:03 CDT