[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