0

I have a query that is used in a BIRT report (in IBM's Maximo Asset Management platform).

I'm considering storing the query in a materialized view in order to improve performance and reduce the load on the system.

Question:

Is a way to deliberately make the report fail if the MV is out-of-date?

  • In other words, we'd rather have no data in the report, than wrong data.

Edit:

I removed the excessive details from the question.

4
+100

Why bother at all with a new column, when you can just check LAST_REFRESH_DATE in ALL_MVIEWS before executing the actual query.

ALL_MVIEWS

And there is basic/advanced query rewrite as well.

2
  • Even with last refresh date of today the data can still be stale. – ik_zelf Feb 15 at 8:56
  • @ik_zelf Obviously. The original question was completely rewritten. – Balazs Papp Feb 15 at 8:59
0

To build on @BalazsPapp's answer:

I can check the LAST_REFRESH_DATE in ALL_MVIEWS or USER_MVIEWS:

select
    *
from
    wo_mv
where
    exists (select 1
            from all_mviews
            where mview_name = 'WO_MV'
                  and trunc(last_refresh_date) = trunc(sysdate) 
           )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.