OraFAQ Forum: SQL & PL/SQL » Date Range

Home » SQL & PL/SQL » SQL & PL/SQL » Date Range - Optimizing

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date Range - Optimizing [message #22497]

Mon, 14 October 2002 20:30

Srividya
Messages: 4
Registered: May 2002

Junior Member

If i give a date range in WHERE clause of my query, then query takes around 8 secs to run. otherwise query takes 1 sec. Can you help me out in optimizing the query which includes a date range?
Query
-----
Select * from my_view where my_date between to_date('10/1/2002','mm/dd/yyyy') and to_date('10/10/2002','mm/dd/yyyy')

Thanks,
Srividya.

Report message to a moderator

Re: Date Range - Optimizing [message #22498 is a reply to message #22497]

Mon, 14 October 2002 20:41

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

You have constructed the query correctly (no functions around the date column, explicit date conversions), so any optimization would have to be done on the database side. The most obvious question is - do you have an index on the my_date column? If so, and you're using the CBO, is this table analyzed?

Report message to a moderator

Re: Date Range - Optimizing [message #22502 is a reply to message #22497]

Tue, 15 October 2002 05:00

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

try this instead of BETWEEN clause.

...
my_date > (minvalue) and
my_date < (maxvalue)

Report message to a moderator

Re: Date Range - Optimizing [message #22508 is a reply to message #22497]

Tue, 15 October 2002 06:27

Mike
Messages: 417
Registered: September 1998

Senior Member

The way you wrote your where clause is fine. Using the to_date function is no problem because it is not used again a column but a hard coded value. Using between or > < doesnt change anything.

Did you analyze your table?

In which case does it take only 1 sec? Is it to display all data or only just the 1st record?

Could you show the explain plan of your query.

Mike

Report message to a moderator

Previous Topic:	ora-0090
Next Topic:	Querying Between Dates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 07 08:31:15 CDT 2024