Home » SQL & PL/SQL » SQL & PL/SQL » Need a better query (Oracle 10g, HP Unix)
Need a better query [message #337804] Fri, 01 August 2008 04:31 Go to next message
arsheshadri
Messages: 12
Registered: January 2006
Junior Member
Hi,

Can anyone suggest a better way to write the below query?
This query is taking more time. The table is having 25 million records.

Select * from Source_Emp
Where
START_DATE > $V_DATE or
END_DATE > $V_DATE or
CREATED_DATE > $V_DATE or
ENDED_DATE > $V_DATE

In the above query, $V_DATE is a single value comming from variable.

Thanks & Regards
Shesha
Re: Need a better query [message #337805 is a reply to message #337804] Fri, 01 August 2008 04:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Select * from Source_Emp
Where LEAST(START_DATE,END_DATE,CREATED_DATE,ENDED_DATE) > $V_DATE


You can then create a function based index on LEAST(START_DATE,END_DATE,CREATED_DATE,ENDED_DATE) if you need to.
Re: Need a better query [message #337809 is a reply to message #337805] Fri, 01 August 2008 04:49 Go to previous messageGo to next message
arsheshadri
Messages: 12
Registered: January 2006
Junior Member
Hi,
The LEAST query is giving me wrong results.
I have to select records where any of the date columns is greater than V$DATE.
i.e any of START_DATE or END_DATE or CREATED_DATE or ENDED_DATE > V$DATE

Thanks & Regards
Sheshadri
Re: Need a better query [message #337816 is a reply to message #337809] Fri, 01 August 2008 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not LEAST, it is GREATEST (conditions are ORed and not ANDed, so it is sufficient the greatest value fit the >).

Regards
Michel
Re: Need a better query [message #337823 is a reply to message #337809] Fri, 01 August 2008 05:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Think. Think what least would do. JRowbottom also makes mistakes (ooh.. can't believe I said that)
Try to find which function you should use.

[Edit: hm. too late.]

[Updated on: Fri, 01 August 2008 05:15]

Report message to a moderator

Re: Need a better query [message #337860 is a reply to message #337816] Fri, 01 August 2008 06:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're right. I was thinking we were looking to check if a value was earlier than vdate$, not later than vdate$.

Not enought coffee....
Re: Need a better query [message #337882 is a reply to message #337805] Fri, 01 August 2008 08:06 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Looks like another example of the abuse of DATEs and strings again, though.
Previous Topic: Calling procedure scenario
Next Topic: comparing values in table
Goto Forum:
  


Current Time: Sun Dec 04 20:52:53 CST 2016

Total time taken to generate the page: 0.09662 seconds