Re: How to signal a program that a table ha

>>> If you polled the database, how would you tell whether a table has changed?
 

>> All of our data is timestamped. A client would check that the timestamp
>> (of the row with the greatest timestamp, if it's a logging table) is
>> different than what it remembers.
 

>>OK but that requires the application to make provision for the polling.
>
>It would be neat if Oracle kept some per-table sequence no. which it
>incremented whenever the table was altered. Like file-modification-times
>on UNIX files. Then we could cheaply check whether _any_ table has
>changed.
>
>Surely, finding the greatest timestamp in a table involves a search of the
>whole table? Or do you index on the timestamp?

As there is no guarantee that people will be willing to respond when you signal them, if you minimize the costs of polling, it works fine since it allows you to poll with relatively high frequency. One relatively inexpensive way of checking for changed records is described below.

For each table to be monitored for changed records, have:

1. a sequence number
2. a status table, where the table characteristics are
3. a counter field
4. only one record since the table indicates only 1 piece of information, namely:

             how many records have been "processed" (where "processed"
             means examining a modified/new record) in the table to be
             monitored

The sequence number and status table perform the following functions:

1. sequence number: the total number of records that have ever needed to be processed
2. status table : the total number of records that have ever been processed.

The ideas is to have:

1. the (sequence number - the reader field in the status table) represent the number of unprocessed records. This makes it very inexpensive to determine if there are records to be processed.
2. the unprocessed records can all have a sequence number field whose value is >= to the value of the reader field in the status table.

Assuming that table writers update/insert records and that table readers process these records, then the above are utilized by reader and writer processes as follows:

1. table writers:

         after writing a record (update/insert), the writer proceses
         increments the sequence number.  Note that it makes sense to
         have a sequence number field in the table to be monitored so
         that the reader processes can search for all records whose
         "sequence number" exceeds a particular value.

  2 table readers:

• the reader compares the sequence number to the reader field in the status table and when the reader field < sequence number then records have changed and the reader process needs to look at them.
• After processing the records, the reader copies the value that the sequence number had before it began processing records into the reader field in the status table
• Note that this assumes that only 1 READER PROCESS RUNS AT A TIME.

Note that a hierarchical system could be built from the above so that it would be easy to tell whether any of a set of tables was changed.

Tim McCardle Received on Mon Mar 16 1992 - 16:33:02 CET