Home » SQL & PL/SQL » SQL & PL/SQL » Incorrect Max(time value) results returned
icon8.gif  Incorrect Max(time value) results returned [message #313717] Mon, 14 April 2008 05:20 Go to next message
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 #313728 is a reply to message #313717] Mon, 14 April 2008 06:18 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess SYSDATE always returns a correct value. So, it must be "reading_time" that causes problems.

In order to identify the cause, could you select "reading_time" as well (so that you could see its value and, if possible, find out who/what inserted such a value into the "locationsTable". Moreover, you might create a job which would do that occasionally and enter collected values into some kind of a log table (for future analysis).

By the way, why not simply " * 1440" (instead of "84600/60")?
Re: Incorrect Max(time value) results returned [message #313735 is a reply to message #313717] Mon, 14 April 2008 06:37 Go to previous messageGo to next message
flyboy
Messages: 1832
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).
Re: Incorrect Max(time value) results returned [message #313746 is a reply to message #313735] Mon, 14 April 2008 07:20 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Flyboy
supply Littlefood
Hungry? ./fa/3378/0/ ./fa/3943/0/

Just kidding!

But: as I understood the question, there's NO performance problem (14 million rows => 1 minute query execution), but in DATA being retreived - sometimes the difference between SYSDATE and MAX(reading_time) is "hours" instead of "seconds".
Re: Incorrect Max(time value) results returned [message #314200 is a reply to message #313746] Wed, 16 April 2008 01:18 Go to previous messageGo to next message
nooruls143
Messages: 8
Registered: March 2008
Junior Member
Littlefoot,
you are right. The difference I get is hours instead of minutes. However, I also found my mistake (1 day = 86400 seconds, not 84600 seconds. How silly of me Razz )

Anyways, I ll check now whether it is due to this reason or some other one. This error occurs randomly.


Cheers,
Noorul
Re: Incorrect Max(time value) results returned [message #314203 is a reply to message #313717] Wed, 16 April 2008 01:24 Go to previous message
nooruls143
Messages: 8
Registered: March 2008
Junior Member
Distinct reversegeocoded values are 0, 1 and 2
Previous Topic: comapre date with sysdate
Next Topic: please suggest a sql statement
Goto Forum:
  


Current Time: Sun Dec 11 00:27:36 CST 2016

Total time taken to generate the page: 0.11409 seconds