IncrementalLoadStatuses@eol and IncrementalLoadStatuses@eol not accurate?

with the statement below I found that the IncrementalLoadStatuses@eol and IncrementalLoadStatuses@eol are not accurate. Is it possible ?

for verification purpose I would like to check the status of the @eol cache.
IncrementalLoadStatuses@eol give immediate results whereas the max() and count() on my dataset requires 2m30s.

select 
  I.division
  ,I.cache_MaxtimeStamp
  ,S.MaxSeenTimeStampCurrent
  ,I.cache_rowcount
  ,S.RowCount
from
(
  select 
     division
     , max(timestamp) as cache_MaxtimeStamp
     ,count(*) as cache_rowcount 
  from transactionlinesincremental@eol
  group by division
) I
left join IncrementalLoadStatuses@eol S on I.division = S.PartitionCode and S.Tablenamerequested like '%TransactionLinesIncremental%'

It sounds as designed: the status tables just inspect the history.

What is the expected behavior you are missing?

I see that the values extracted from IncrementalLoadStatuses@eol are not up to date as seen in the screen capture above.

I would like to get the max(timestamp) and count(*) of all divisions and in the cached version transactionlinesincremental@eol, thus I would like to know if I can rely on IncrementalLoadStatuses@eol which gives instant result, or need to execute the following stmt:

  select 
     division
     , max(timestamp) as cache_MaxtimeStamp
     ,count(*) as cache_rowcount 
  from transactionlinesincremental@eol
  group by division

which will require 3 to 4 mins to run in my case (this will trigger calls to EOL endpoints, updating the Invantive’s cache, then computing the statement itself.)

For up-to-date results it will be necessary to run for instance this query. This will - as a side-effect - update the contents of Incremental LoadStatuses@eol.

A faster alternative can be for max(timestamp) to use:

declare
  l_timestamp_max number;
begin
  for r
  in 
  ( select PartitionCode
    ,      max(MaxSeenTimestampCurrent) MaxSeenTimestampCurrent
    from   IncrementalLoadStatuses@eol
    group
    by     PartitionCode
  )
  loop
    select max(timestamp)
    into   l_timestamp_max
    from   SyncTransactionLines@eol
    where  division = r.PartitionCode
    and    timestamp >= r.MaxSeenTimestampCurrent
    ;
    dbms_output.put_line('Current max for ' || r.PartitionCode || ' is ' || to_char(l_timestamp_max));
  end loop;
end;

This will only retrieve the pages after the last seen timestamp and typically takes a few seconds per thousand records created since then.

For count(*) a faster alternative is presented in:

This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.