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%'
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.
