Re: simple sql optimize help/mlml

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Tue, 22 Jun 2004 04:36:16 GMT
Message-ID: <4HOBc.92520$0y.73179@attbi_s03>

"Michel Lee" <ae299_at_FreeNet.Carleton.CA> wrote in message news:cb6p31$63q$1_at_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------------------------------------------/

His point isn't that the data function will speed it up. His point is that if you rely on an implicit nls_date format then you will get bitten. Somday someone on the client will change the default nls date format and then you will have a lot of bugs in your code. Instead use explicit date conversions. Personally I prefer 4 digit years. Jim Received on Mon Jun 21 2004 - 23:36:16 CDT