Hi Masters, I have a question relating to materialized views.
We have 3 tables: INVOICE_MAIN, INVOICE_BALANCE and INVOICE_LINE and one view INVOICE joining INVOICE_MAIN and INVOICE_BALANCE.
In the table INVOICE_LINE there are the columns INVOICE_ID (FK to table INVOICE), TOTAL_EXCL_VAT and TOTAL_VAT.
In the table INVOICE_BALANCE there are the columns INVOICE_ID (FK to table INVOICE), TOTAL_EXCL_VAT and TOTAL_VAT - both containing the sums.
The table INVOICE_BALANCE is kept up to date through som PL/SQL code and a work table, so that inserts into the INVOICE_LINE table on an existing invoice does not block other inserts for the same invoice (they become updates on INVOICE_BALANCE). There is a scheduled job that reads off the work table and performs a merge into the INVOICE_BALANCE table. Everything works OK with multiple sessions (more than 20) inserting.
Can we replace the INVOICE_BALANCE table with a materialized view log on INVOICE_LINE and a materialized view and avoid the locks?
The rationale being that Oracle can write better code than we can, and the current solution is a bit "clumsy", requiring a scheduled jobs that basically never stops.
Other than being "clumsy", what specific problem do you have with your current solution? What benefit are you expecting to get by using MVs?
Not knowing the specifics of your environment, maybe MVs will work out better, maybe they won't.
You certainly could sum up the values in an MV to get the totals.
If you make it fast refresh on commit, then Oracle Database will update it as part of the transaction that changes lines.
But only one process at a time can refresh an MV. So this could be an issue if you have 20+ concurrent transactions on the tables.
That said, your current method of submitting a job means updating the balance is a separate transaction. So you have a short period where the balances aren't up-to-date anyway. So you may be able to get away with frequent scheduled refreshes (e.g. every minute).
Or, as you're using 12.2, you could check out real-time materialized views. These give best of both worlds: the ability to have up-to-date results from an MV. But only refreshing it periodically. These work by applying the changes in the MV log when you run your query.
Read more about these at:
https://blogs.oracle.com/sql/12-things-developers-will-love-about-oracle-database-12c-release-2#real-time-mv https://oracle-base.com/articles/12c/real-time-materialized-views-12cr2 https://richardfoote.wordpress.com/2017/07/10/12-2-introduction-to-real-time-materialized-views-the-view/ Ultimately to decide whether an MV is better, you need to decide how you're going to evaluate this. And benchmark your current method to ensure it doesn't introduce issues elsewhere.