[thesite] t.e.o. updates

rudy r937 at interlog.com
Sat Nov 17 15:33:45 CST 2001


> based on the time differences, i think i'll stick with
> the two query approach.

hey, i'm not going to argue with facts, but you are definitely comparing
apples and oranges

in the first of your two queries, you select all articles that are not in
special categories and less than a year old, and in the second of the two
queries, you have

     WHERE categorys.categoryid = content.categoryid
       AND content.userid = users.userid
       AND content.signoff = 1
       AND content.categoryid NOT IN (1, 23, 26, 9741)
       AND content.contentid = #Val(getrevivedrange.contentid[RandRange(1,
getrevivedrange.recordcount)])#

i'm guessing, but it looks like the last condition might be selecting one
or some (five?) of the contentids from the first of the two queries

by the way, that last condition, content.contentid = [something], means
you are going after specific articles, so you might as well remove the
preceding 2 qualifications, which, presumably, were already satisfied in
the first query

the difference in timings is most likely due to the fact that in the
one-query approach, you are joining all articles to their users and
categories, but in the two-query approach, you are only joining them if
they've met the selection criteria

yeah, the optimizer should've picked up on that, and maybe there's a way to
use HINT to get it to do the same, or perhaps we might try a different
tack...

could you run the following test for me?

    SELECT content.contentid
         , content.keyphrase
         , content.contentname
         , content.synopsis
         , content.datemod
         , content.replies
         , content.rating
         , content.ratings
         , users.userid
         , users.who
         , categorys.category
         , categorys.categoryid
   /* note no random number here */
      FROM content
         , users
         , categorys
     WHERE categorys.categoryid = content.categoryid
       AND content.userid = users.userid
       AND contentid IN
         ( SELECT contentid
             FROM
            ( SELECT contentid, rand.random AS randum
              RANK() OVER
                (ORDER BY randum ASC NULLS LAST) AS contrank
                FROM content
               WHERE content.signoff = 1
                 AND content.categoryid
                     NOT IN (1, 23, 26, 9741)
            ORDER BY randum ASC NULLS LAST
            )
           WHERE contrank < 6
         )

if you want, try the subquery first

this uses the new RANK() function in oracle 8

i admit, i have never used it before, but i ran across it in the following
article which i found when searching for "top ten" resources --

  Performing Top-N Queries in Oracle
  http://gethelp.devx.com/techtips/oracle_pro/10min/10min1200/10min1200.asp

obviously, you may need to adjust WHERE contrank < 6 (which returns the top
five) to reflect however many random articles you want out of the subquery


rudy






More information about the thesite mailing list