Incorrect Max(time value) results returned [message #313717] |
Mon, 14 April 2008 05:20  |
nooruls143
Messages: 8 Registered: March 2008
|
Junior Member |
|
|
Hi,
I am using Oracle 10g.
I have a simple query. I have a table with 14 million records. I perform this query
select (sysdate - max(reading_time))*84600/60 as maxTime from locationsTable where reversegeocoded = 2
The table is indexed on the pair of columns (reading_Time,reversegeocoded) and also indexed on these two individual columns seperately.
This query takes about a minute to complete. And sometimes, this gives the max(reading_time) value several hours back which is incorrect as the actual time difference between sysdate and reading_time is just few seconds (upto a minute) normally. We cannot reproduce the problem when we want as it happens randomly.
Any help or suggestions on this will be highly appreciated.
Regards,
Noor
|
|
|
|
Re: Incorrect Max(time value) results returned [message #313735 is a reply to message #313717] |
Mon, 14 April 2008 06:37   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
To supply Littlefood: have a look into Oracle Concepts manual, found eg. online on http://tahiti.oracle.com/.
Read there the paragraph about Statement-Level Read Consistency.
To the time: as READING_TIME is in the first place of the index, the query may need to scan many records till it finds corresponding REVERSEGEOCODED. Maybe you could try to swap columns in index; however it may affect other queries filtering only READING_TIME.
By the way, how many distinct REVERSEGEOCODED do you have in the table.
By the way, one day has 86400 seconds, not 84600. I would rather put " * 24*60" into the expression (24 hours in a day times 60 minutes in an hour).
|
|
|
|
|
|