Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: Fetching changes for a given query

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It sounds like you want to create an audit table for myTbl that records each update and delete event and the "old" values. This could be maintained by a trigger like:
    Code:
    create trigger myTbl_trg
    after update or delete on myTbl
    for each row
    declare
       l_action varchar2(1);
    begin
       l_action := case when updating then 'U' else 'D' end;
       insert into myTbl_audit
          ( action, action_date, old_c1, old_c2, ...)
       values 
          ( l_action, sysdate, :old.c1, :old.c2, ...);
    end;
    Now your application can query this table instead of myTbl.

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Thanks for the fast reply.
    The problem with your solution is that I have many tables and this can
    be very expensive.
    Is there an efficient solution for this problem?
    Thanks
    dyahav

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not really, because the requirement is not usual: you want at any time to be able to query the database for updated/deleted rows, based on what those rows used to be, not what they now are.

    Maybe you could use flashback query and compare with current state of data?
    Code:
    select * from myTbl as of timestamp sysdate-5/60/60/24;

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Bad design

    Quote Originally Posted by dyahav
    ... Etc ...
    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).
    Either your application is badly designed: "the application has all the rows" or the process' that updates the rows is incorrect "application don't get a row that its 'name' has been changed".

    In esence you are saying you cannot detect changed/deleted rows, therefore you have to correct the process that updates the 'lastUpdateTime' and instead of deleting rows, add a 'deleted' flag column.

    Also you may be missing rows with the "current_date - (5 seconds)" condition, you would need to save the MAX(lastUpdateTime) you queried and change the condition of your query to "lastUpdateTime > lastUpdateTime_saved".


    OR, use flashback query as adrewst suggests.
    Last edited by LKBrwn_DBA; 03-31-09 at 10:16.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •