[Theforum] [---Dev] RE: [---tent] Article cleanup issue

Matt Liotta mliotta at r337.com
Tue Jul 23 18:23:49 CDT 2002


> 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
>      , (SELECT Count(*)
>           FROM replies
>          WHERE contentid = content.contentid
>            AND datemod > '22-Jul-02'
>        ) AS newcomments
>    FROM content
>       , users
>       , categorys
>   WHERE content.signoff = 1
>     AND content.categoryid <> 23
>     AND content.categoryid <> 26
>     AND content.categoryid <> 9741
>     AND content.userid = users.userid
>     AND content.categoryid = categorys.categoryid
>   ORDER BY content.datemod DESC
>       , content.contentid DESC
>
The above SQL doesn't look so bad, but there is plenty of room for
improvement. First, we need to know what is making the query slow.
Simply process of elimination should suffice. Probable candidates are
the sub-query and the two joins. Once we know which is causing the
problem we can fix it. Possibly solutions involve adding additionally
indexes, breaking the query up into multiple queries, using a
materialized view, and using optimizer hints. Any combination of those
solutions should do the trick rather quickly as Oracle should be able to
handle this query with a reasonable data set in less than 100ms.

-Matt




More information about the theforum mailing list