simple sql optimize help/mlml

From: Michel Lee <ae299_at_FreeNet.Carleton.CA>
Date: 18 Jun 2004 17:08:23 GMT
Message-ID: <cav7i7$hvn$1@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------------------------------------------/

Received on Fri Jun 18 2004 - 12:08:23 CDT