Re: simple sql optimize help/mlml

From: Michel Lee <ae299_at_FreeNet.Carleton.CA>
Date: 21 Jun 2004 13:48:35 GMT
Message-ID: <cb6ovj$5sr$1@freenet9.carleton.ca>

You helped me , /*+ INDEX_JOIN(wlog) */ WORKS! pls see below,...
about 4 seconds

Mike

 (ctcgag_at_hotmail.com) writes:
> 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.
>

   SELECT /*+ INDEX_JOIN(wlog) */ count(1) FROM wlog     WHERE

      LOG_DATE > to_date('14-JUN-04','DD-MON-RR')   and
      user_id=6;

Elapsed: 00:00:04.08

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20709 Card=1 Bytes=12)    1 0 SORT (AGGREGATE)

   2    1     VIEW OF 'index$_join$_001' (Cost=20709 Card=1769 Bytes=21228)
   3    2       HASH JOIN
   4    3         INDEX (RANGE SCAN) OF 'USER_ID_IX' (NON-UNIQUE)
(Cost=759025 Card=1769 Bytes=21228)
   5    3         INDEX (RANGE SCAN) OF 'LOG_DATE_IX' (NON-UNIQUE)

(Cost=759025 Card=1769 Bytes=21228)

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

--
/-------------------------------------------------------------------/
/             http://miccc.com                                      /
/-------------ae299_at_ncf.ca------------------------------------------/

Received on Mon Jun 21 2004 - 08:48:35 CDT