Re: tkprof show many fetches with high elapsed time

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sat, 13 Jan 2007 18:51:59 GMT
Message-ID: <Xns98B76E8A1D518anacedenthotmailcom@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. Received on Sat Jan 13 2007 - 12:51:59 CST