Re: tkprof show many fetches with high elapsed time

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:Xns98B76E8A1D518anacedenthotmailcom_at_69.28.173.184...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in news:YO-
> dnUVIpeJFQD3YRVnyhgA_at_bt.com:
>
>> "Ana C. Dent" <anacedent_at_hotmail.com> wrote in message
>> news:Xns98B06BA0C27CFanacedenthotmailcom_at_69.28.173.184...
>>
>>>
>>> SELECT s.ID AS ID, s.DATA as DATA
>>> FROM PROFILESTOREDATA s,
>>> WHERE s.ID IN
>>> (select STORE_ID from PROFILESTOREDATA_BATCH b where b.BATCH_ID= :1)
>>>
>>> Never have in the FROM clause a table which does not contribute to the
>>> SELECT clause.
>>
>> That's an interesting suggestion - but seems a bit
>> extreme. What's the rationale behind it ?
>>
>
> Why is it extreme?
> If no data is actually being extracted out of a table,
> why should it ever be included in the FROM clause?
> In every case I have been able to implement this "rule".
> In many, many cases the resultant SQL actually completes
> much, much faster as a result.
> Typically the subordinated sub-queries are "smaller & simpler"
> & run much faster.
>
> Too bad the OP used this forum as a WORM medium & did not post
> the results from the re-written query.

It looks like Chas beat me to it. I was going to use the old Students, Courses, Lecturers model to come up with something similar - viz: cross-reference Lecturers with Students

The advice is extreme because the word "never" is, by definition, at an extreme - it allows no exceptions.

I think the observation is sound, but only as a clue to a possible method for re-writing a badly performing query.

I certainly wouldn't propose it as a directive to be invoked when you first put pen to paper - the first rule (in my opinion) should always be to write the query in SQL that reads like a statement of the original problem.; then strive to keep the SQL easy to read.

Bear in mind - from a performance point of view - that Oracle frequently unnests subqueries anyway. In this particular example the optimizer would probably have transformed your version of the query back to the original join.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html