| optimized search for max() function [message #305194] |
Mon, 10 March 2008 01:53  |
nooruls143
Messages: 8
Registered: March 2008
|
Junior Member |
|
|
Hi,
I am searching for a max(reading_time), sysdate function to get the maximum reading_time and sysdate, but it takes 20 seconds to return. I have an index for it but it is using another index even if I try to use index in hints
eg:
select /*+ index(TRACKING_DATA$REVCODED) */ round((sysdate - max(reading_time))*84600/60,2) as time_diff, sysdate from almasar.bt_mls_tracking_data a where reversegeocoded = 1
when I check explain plan, it is using full scan. Please give me optimized way to achieve this.
Please help,
Regards,
Noor
P.S. - I also used /*+ rule */, but it works only when I get back single value. I also want sysdate to be returned along with max() value.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
