[thesite] friday is a good day..

[Dean Mah]

Long message ahead....

First let me say, I love the way you broke that query down.  You are
the SQL god.

I wrote your query like this:


SELECT content.contentid, content.keyphrase, content.contentname,
       content.synopsis, content.datemod, content.replies, users.userid,
       users.who, categorys.category,  categorys.categoryid,
       Count(*) AS newcomments
  FROM users, content, categorys, replies
 WHERE content.signoff = 1 AND content.userid = users.userid
   AND content.categoryid <> 23 AND content.categoryid <> 26
   AND content.categoryid <> 9741 AND content.categoryid = categorys.categoryid
   AND content.contentid = replies.contentid (+)
GROUP BY content.contentid, content.keyphrase, content.contentname,
         content.synopsis, content.datemod, content.replies, users.userid,
         users.who, categorys.category,  categorys.categoryid


It ran marginally faster.  If SQL*Plus's timing features can be
trusted.  The original CF query (2 runs) ran at: 8.71 and 9.06.  Your
query ran at: 8.35 and 8.40.

Oracle optimizes the queries into almost exactly the same thing.

The CF query looked like:


SELECT STATEMENT   Cost =
  SORT ORDER BY
    NESTED LOOPS
      NESTED LOOPS
      TABLE ACCESS FULL CATEGORYS
      TABLE ACCESS BY INDEX ROWID CONTENT
        INDEX RANGE SCAN CONTENT_CATID
      TABLE ACCESS BY INDEX ROWID USERS
      INDEX UNIQUE SCAN SYS_C001208


While your query looked like:


SELECT STATEMENT   Cost =
  SORT GROUP BY
    NESTED LOOPS OUTER
      NESTED LOOPS
      NESTED LOOPS
        TABLE ACCESS FULL CATEGORYS
          TABLE ACCESS BY INDEX ROWID CONTENT
              INDEX RANGE SCAN CONTENT_CATID
              TABLE ACCESS BY INDEX ROWID USERS
                INDEX UNIQUE SCAN SYS_C001208
      INDEX RANGE SCAN REPLIES_CONTID


In Oracle you do outer joins by marking the table with (+).  I don't
think, at least in Oracle7, that SQL92's outer join syntax is
supported.

In Oracle7 there wasn't any conception on the SELECT TOP or LIMIT as I
understand them.  You can try using Oracle's rownum or rowid but I
don't think that it will get you exactly what you want.

Dean


rudy writes:

> > We need to rewrite the query so that it doesn't require the nested one.
> 
> i think you will find the alternative equally slow, because the
> alternative is a LEFT JOIN with a GROUP BY on all the columns other
> than the Count(*)
>
> i would be interested to see if oracle actually does better on this query
> than the one using the nested correlated subquery, but my guess is it will
> be just as slow
>
> does oracle support anything like microsoft's SELECT TOP or mysql's LIMIT?