Re: tkprof show many fetches with high elapsed time

Ana C. Dent wrote:
> "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.

Ana,

SELECT u.username, o.opdescription, count(*) from user u, operations o, bigolfacttable f where u.userid = 42
and f.userid = u.userid
and f.opcode = o.opcode
group by u.username, o.description
/

In english, show me the users, ops performed, and how many times each op was performed.

The select goes against bigolfacttable, but no actual data from bigolfacttable is displayed. Such a query seems perfectly reasonable to me.

comments/questions/concerns?

REgards,
Chas. Received on Sat Jan 13 2007 - 19:01:34 CST