Re: simple sql optimize help/mlml

"Michel Lee" <ae299_at_FreeNet.Carleton.CA> wrote in message news:cav7i7$hvn$1_at_freenet9.carleton.ca...
>
> 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
>
> i dont know why its slow since i created these indexes (i know, they are
> not concatenated)
>
> create index log_date_ix on wlog
> (log_date) tablespace xxxts NOLOGGING;
> create index user_id_ix on wlog
> (user_id) tablespace xxxts NOLOGGING;
>
> When i query by each field, they are fast.
>
> SQL> SELECT /*+ INDEX(wlog user_id_ix) */ count(1) FROM wlog
> 2 WHERE
> 3 user_id=6;
> COUNT(1)
> ----------
> 31360
> Elapsed: 00:00:00.00
>
> SELECT /*+ INDEX(wlog LOG_DATE_IX) */ count(1) FROM wlog
> WHERE
> LOG_DATE > '14-JUN-04';
> COUNT(1)
> ----------
> 1596996
> Elapsed: 00:00:04.04
>
>
> 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)
>
>
>
>
> What can i do to improve on the speed without using concatednated indexes
> create index userlogdate_ix on ws_log
> (user_id,log_date) tablespace websenseixts NOLOGGING;
>
> 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,
> ??
>
> thanks
> Mike
>
> --
> /-------------------------------------------------------------------/
> / http://miccc.com /
> /-------------ae299_at_ncf.ca------------------------------------------/

Create a concatenated index and compress it. create index myConcat on wlog(user_id,log_date) compress 2;

also don't assume the implicit conversion of the string to a date. Do an explicit conversion and use 4 digits for the year. to_date('14-JUN-2004','dd-mmm-yyyy')

Lose the nologging clause. By compressing the index it will be much smaller because there are probably a lot of repeated entries for user_id. Why do you fear concatenated indexes?
Jim Received on Fri Jun 18 2004 - 22:32:38 CDT