Home » SQL & PL/SQL » SQL & PL/SQL » Best Logic for retriving data based on timestamp.
Best Logic for retriving data based on timestamp. [message #215339] Sun, 21 January 2007 11:37 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
Scenario
------------
One of the 3rd party Application feeds data into an Oracle table almost on a hourly basis. One of the column is a date field.

Requirement
-------------
A report is generated out of this table that is scheduled to run every morning at 2 am that pulls up data for the previous day.

What would be a good logic to track the changes since the last time the report was run. For eg.
1/20/2007 02:00:00 -- time the report is scheduled
1/19/2007 12:14:50 -- Was the last date-time in main table for which the report was generated. (No data was populated in table on that day after this date/time because of some problem in the 3rd party application).

1/21/2007 02:00:00 -- time the report is scheduled.
Now the report should track/report changes after 1/19/2007 12:14:50.

Approach
--------------
One approach is create one more table that tracks the last timstamp the report was generated for. Chances of report missing on data exists.
Other approach could be have some kind of Primary/Foreign key relationship on main table and tracking table based on some unique numbers.

Regards,
Re: Best Logic for retriving data based on timestamp. [message #215355 is a reply to message #215339] Sun, 21 January 2007 19:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If the report runs on or after 2:00am, then just run it for the period yesterday 2:00am to today 1:59:19. Then you don't need to capture the launch time, and there's no way anything can get out-of-synch.

Ross Leishman
Re: Best Logic for retriving data based on timestamp. [message #215488 is a reply to message #215355] Mon, 22 January 2007 08:51 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Hi,
The Problem with that approach is with this scenario.

1/19/2007 12:12:50 - Last Date (Max Date) in the table available.
1/20/2007 02:00:00 - The report is run. (No Problem)
1/20/2007 - Table is not populated with the data due to some problem in the 3rd party application sending the data to Oracle. (Either DB is down or application engine collects the data but fails to send it to DB)
1/21/2007 02:00:00 - Report throws up Blank.
1/21/2007 - Again table is not populated due to errors
1/22/2007 02:00:00 - Report is blank.
1/22/2007 - The application is up and running and it syncs up all data it gathered from 1/19/2007 12:12:50, however the report would fail to show this because I look up only last day's data.
There is no way of me knowing where to start if I dont track.

Regards,
Re: Best Logic for retriving data based on timestamp. [message #215532 is a reply to message #215488] Mon, 22 January 2007 14:05 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
What do you think of using materialized views for getting changed rows since there would only be inserts.

Regards,
Re: Best Logic for retriving data based on timestamp. [message #215542 is a reply to message #215532] Mon, 22 January 2007 17:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Rather than reporting based on the date/time of the source system, you should report on the date/time YOUR system received the data. Add an extra column and default it to SYSDATE.
Re: Best Logic for retriving data based on timestamp. [message #215554 is a reply to message #215542] Mon, 22 January 2007 19:06 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Just remember that if the process creating the data timestamps the records inserted - those records not visible until they are committed. If you need to be 100% accurate, then you need to ensure that you aren't missing any uncommitted records. Even if you take out an exclusive lock on the table - that won't prevent inserts (I'm 99% sure anyway - test it). The MV approach is good. Another common "watertight" approach is for you to update records you've read with a flag so you don't read them again. This is a variation of keeping track of each individual records you've read on your side.

If you know your inserts and commits are handled quickly - or there is no insert activity around your cutoff times - you don't really have a problem...
Re: Best Logic for retriving data based on timestamp. [message #215576 is a reply to message #215554] Mon, 22 January 2007 23:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Another option is Change Data Capture. It is a similar concept to the MV, but with (generally) fewer overheads in 10g.
Re: Best Logic for retriving data based on timestamp. [message #215747 is a reply to message #215576] Tue, 23 January 2007 14:44 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
Thanks.
I will go for MV since I am on 9i. Thanks for the help.

Regards,
Previous Topic: Column Keyword
Next Topic: How to call a Oracle Stored Procedure from Excel?
Goto Forum:
  


Current Time: Sun Dec 04 06:31:31 CST 2016

Total time taken to generate the page: 0.12309 seconds