| Need a better query [message #337804] |
Fri, 01 August 2008 04:31  |
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  |
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 |
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 #337860 is a reply to message #337816] |
Fri, 01 August 2008 06:42 |
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....
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
