I have a huge oracle transactional table where I extract data at 4 hour intervals. I have a requirement to validate the count of this extracted data using a followup select count(*) from table query. However, both the select * from table where tend between sysdate-4hours and sysdate and select count(*) from table where tend between sysdate-4hours and sysdate queries are required to start at the same time, as if they're running in a race.

The objective is to tally the count, and this table receives sizeable no. of transactions at the minute level. I am adding the parallel hint for the select * query, it's giving good results, however when I am kicking off both the jobs, the extraction finishes long before even though the count query is running exceptionally longer. how to proceed?

CodePudding user response：

To me, it looks as if you're bothered by question what time is "right now"? because two queries, ran at different times, have different sense of "right now".

So, how about setting it explicitly? Something like this:

declare
  l_now date := sysdate;
begin
  -- extract data, e.g.
  insert into another_table (col1, ool2, ...)
  select col1, col2, ...
  from huge_table
  where date_column between l_now - interval '4' hour and l_now;
  
  -- you want to know number of rows? Easy-peasy
  l_cnt := sql%rowcount;
  
  -- you want to count rows explicitly? OK, if you must
  select count(*)
    into l_cnt
  from huge_table
  where date_column between l_now - interval '4' hour and l_now;
end;

CodePudding user response：

Don't use an offset from SYSDATE. Instead, store the last retrieved date_column value on the client side (or, if you prefer, in a control table somewhere) and on the next refresh start with that value, pulling all rows >=

(for dates with 1 second granularity, make sure to use >= and not just > )

This way your queries are consistent and it doesn't matter whether they run at exactly the same time.