Re: simple sql optimize help/mlml

ae299_at_FreeNet.Carleton.CA (Michel Lee) wrote:
> Please help me speed up this query.
> when i run this query , it takes 46 secs.
> wlog table has many rows (> 30million).
> SQL> SELECT count(1) FROM wlog
> 2 WHERE
> 3 LOG_DATE > '14-JUN-04' and
> 4 user_id=6;
> COUNT(1)
> ----------
> 696
> Elapsed: 00:00:46.07

...
>
> THIS IS the exec plan
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5039 Card=1 Bytes=12)
> 1 0 SORT (AGGREGATE)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'WLOG' (Cost=5039
> Card=1769 Bytes=21228)
> 3 2 INDEX (RANGE SCAN) OF 'USER_ID_IX' (NON-UNIQUE) (Cost=24
> Card=10562)

On similar data with a similar exec plan, I get under one second execution. But my main table appears to be almost entirely cached (30,812 consistent gets, yielding 2 physical reads). I would guess that yours isn't. What do you get for the execution statistics? Does it run much faster the second time? How well cached do you expect the table blocks to be just before the query is normally issued? Is the parameter compared to LOG_DATE always near the high end of the range, or was that just a happenstance?

> also, i tried to analyze the 2 indexes , log_date_ix , user_id_ix and
> table wlog
> i would think that having indexes on each column will give a fast
> performance, but its not,
> ??

Giving the /*+ INDEX_JOIN (wlog) */ hint should switch it to a hash join between the rowids returned by the two index range scans. On my system, this took longer than the first plan (2 seconds rather than 1) but on yours it should be faster than 46 seconds.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB