Home » SQL & PL/SQL » SQL & PL/SQL » Fetching changes for a given query
Fetching changes for a given query [message #395123] Tue, 31 March 2009 07:02 Go to next message
dyahav
Messages: 32
Registered: March 2009
Member
Hi,
I have an application that uses a query to retrieve thousands of
rows.
Initially, the application fetches all the rows. After that, the application
has to fetch only the changed rows. This can be done by using a date
column that contains the Last Update Time and adding a date condition to
the query. Something like (pseudo code):

Select * from myTbl t
where t.name like '%john%'
and lastUpdateTime > current_date - (5 seconds)

In the solution above, the application gets the updated rows (in the last 5
seconds). But the application does not know which rows have been deleted
and which rows don't meet the condition.
In the given example, initially, the application has all the rows, then, when
trying to get the changes, the application don't get a row that its "name"
has been changed to 'foo'. Thus the app still displays this rows and doesn't
know that this row is not valid due to the name change (The row doesn't
meet the query condition).

Do you have any idea how to get correct changes for a given query periodically?
Thanks
Dyahav
Re: Fetching changes for a given query [message #395130 is a reply to message #395123] Tue, 31 March 2009 07:26 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you had a look at oracle's Database Change Notification mechanism?

It's a different approach to what you're doing, one I keep meaning to find the time to have a play with, so I'm not sure if it'll cover all your needs though I assume it should.
Re: Fetching changes for a given query [message #395161 is a reply to message #395123] Tue, 31 March 2009 09:07 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to go to Database Change Notification, carefully check the drawbacks (and restrictions): server load increase, sga usage...

I recommend you to use the server PL/SQL callback procedure to fit a table with changes so that you application can query this table instead of the original one.
I recommend you to NOT use client side callback.

Regards
Michel
Previous Topic: Empty Collection
Next Topic: SUM up three rows into one and do it for the whole document. (merged)
Goto Forum:
  


Current Time: Fri Dec 02 20:46:58 CST 2016

Total time taken to generate the page: 0.12615 seconds