Re: simple sql optimize help/mlml

From: Michel Lee <ae299_at_FreeNet.Carleton.CA>
Date: 21 Jun 2004 13:50:25 GMT
Message-ID: <cb6p31$63q$1@freenet9.carleton.ca>

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

>
> You should never, ever, never depend upon implicit datatype conversion!
>
> SELECT count(1) FROM wlog
> WHERE LOG_DATE > to_date('14-JUN-04',DD-MON-RR)
> and user_id=6;
>
> What do you get from the SQL above?

the date function did not speed it up,

i used this hint and it speed it up to 4 seconds! /*+ INDEX_JOIN(wlog) */

thx
Mike

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

Received on Mon Jun 21 2004 - 08:50:25 CDT