Home » RDBMS Server » Performance Tuning » optimized search for max() function
optimized search for max() function [message #305194] Mon, 10 March 2008 01:53 Go to next message
Messages: 8
Registered: March 2008
Junior Member
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


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,

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.
Re: optimized search for max() function [message #305195 is a reply to message #305194] Mon, 10 March 2008 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the hint and post the relevant information requested for all tuning questions.

Read OraFAQ Forum Guide, How to Identify Performance Problem and Bottleneck and OraFAQ Oracle SQL Tuning Guide.


[Updated on: Mon, 10 March 2008 01:57]

Report message to a moderator

Re: optimized search for max() function [message #305307 is a reply to message #305195] Mon, 10 March 2008 06:36 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You will need an index on (reversegeocoded, reading_time).

You will also need to gather statistics on both the table and the index, and then remove the hint.

Ross Leishman
Previous Topic: Database Metrics for Capacity Planning
Next Topic: Tunning required
Goto Forum:

Current Time: Sat Oct 22 08:46:30 CDT 2016

Total time taken to generate the page: 0.10235 seconds