Materialized view logs in de praktijk Rob van

Materialized view logs in de praktijk Rob van Wijk Planboard DBA Symposium 17 juni 2014

Agenda • Inleiding • Hoe ziet een MV log eruit? • Beperkingen • Casus 1: Valideren bedrijfsregels • Casus 2: Synchroniseren datamodel

Verversingen • Compleet • Incrementeel • transactie • handmatig • Per partitie (PCT) • Synchroon (12 c)

Hoe ziet een MV log eruit? WITH • OBJECT ID • PRIMARY KEY • ROWID • SEQUENCE • COMMIT SCN • (kolommen) • INCLUDING / EXCLUDING NEW VALUES mvl 1. sql

Algoritme MV-log met snaptime$$ 1) Insert into MV-log met snaptime$$ = 4000 A. D. 2) Tijdens verversing: update set snaptime$$ = sysdate 3) Propageer rijen tussen voorlaatste ververstijd (exclusief) en nieuwste ververstijd (inclusief) naar MV 4) Verwijder rijen uit MV-log met snaptime <= ververstijd (sysdate uit stap 2)

Algoritme MV-log met commit SCN 1) Insert into MV-log met xid$$ 2) Tijdens verversing: haal huidige SCN op 3) Propageer rijen tussen voorlaatste SCN (exclusief) en nieuwste SCN (inclusief) naar MV 4) Verwijder rijen uit MV-log met xid$$ < huidige xid$$ via SCN (uit stap 2) geen update in stap 2, wel join met ALL_SUMMAP

Beperkingen op commit SCN 1) Kan niet op een tabel met een LOB: leidt tot ORA-32421 2) Kan geen MV maken op MV logs van verschillende types (timestamp MV logs versus commit SCN MV logs) 3) Gaat niet samen met FOR SYNCHRONOUS REFRESH

Uitdaging van incrementeel verversen A Δ B ? Δ C Δ select , , from a. col 1 sum(b. col 2) som_col 2 max(c. col 3) max_col 3 a inner join b on (b. a_fk = a. pk) inner join c on (c. a_fk = a. pk) where a. status = ’X’ and b. einddatum < sysdate group by a. col 1

Algemene beperkingen 1. The materialized view must not contain references to nonrepeating expressions like SYSDATE and ROWNUM. 2. The materialized view must not contain references to RAW or LONG RAW data types. 3. It cannot contain a SELECT list subquery. 4. It cannot contain analytic functions (for example, RANK) in the SELECT clause. 5. It cannot contain a MODEL clause. 6. It cannot contain nested queries that have ANY, ALL, or NOT EXISTS. 7. It cannot contain a [START WITH …] CONNECT BY clause. 8. It cannot contain multiple detail tables at different sites. 9. ON COMMIT materialized views cannot have remote detail tables. mvl 2. sql

Beperkingen op join-MV’s 1. 2. 3. 4. 5. 6. All restrictions from "General Restrictions on Fast Refresh". Rowids of all the tables in the FROM list must appear in the SELECT list of the query. They cannot have GROUP BY clauses or aggregates. Materialized view logs must exist with rowids for all the base tables in the FROM list of the query. You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement. Also, the refresh method you choose will not be optimally efficient if: – The defining query uses an outer join that behaves like an inner join. If the defining query contains such a join, consider rewriting the defining query to contain an inner join. – The SELECT list of the materialized view contains expressions on columns from multiple tables.

Beperkingen op aggregatie-MV’s 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. All restrictions from "General Restrictions on Fast Refresh ". All tables in the materialized view must have materialized view logs The materialized view logs must contain all columns from the table referenced in the materialized view. None of the columns in the base table, referred to in the materialized view log, can be encrypted. All tables in the materialized view must have materialized view logs specified with ROWID All tables in the materialized view must have materialized view logs specified with INCLUDING NEW VALUES. All tables in the materialized view must have materialized view logs, specified with the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates. Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh. COUNT(*) must be specified. Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(x)) or AVG(x)+ AVG(x) are not allowed. For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present. Oracle recommends that SUM(expr) be specified. If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified. The SELECT list must contain all GROUP BY columns. If the materialized view has MIN or MAX aggregates, then fast refresh is supported only on conventional DML inserts and direct loads. If the materialized view has SUM(expr) but no COUNT(expr), then fast refresh is supported only on conventional DML inserts and direct loads. If the materialized view has no COUNT(*), then fast refresh is supported only on conventional DML inserts and direct loads. A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause. Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged. For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable. For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a )". If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause. The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view. Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided unique constraints exist on the join columns of the inner join table. If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator. It cannot contain a HAVING clause with a subquery.

Beperkingen op union-all-MV’s 1) 2) The defining query must have the UNION ALL operator at the top level. The UNION ALL operator cannot be embedded inside a subquery, with one exception: The UNION ALL can be in a subquery in the FROM clause provided the defining query is of the form SELECT * FROM (view or subquery with UNION ALL) as in the following example: CREATE VIEW view_with_unionall AS (SELECT c. rowid crid, c. cust_id, 2 umarker FROM customers c WHERE c. cust_last_name = 'Smith' UNION ALL SELECT c. rowid crid, c. cust_id, 3 umarker FROM customers c WHERE c. cust_last_name = 'Jones'); CREATE MATERIALIZED VIEW unionall_inside_view_mv REFRESH FAST ON DEMAND AS SELECT * FROM view_with_unionall; Note that the view_with_unionall satisfies the requirements for fast refresh. 3) 4) 5) 6) 7) 8) Each query block in the UNION ALL query must satisfy the requirements of a fast refreshable materialized view with aggregates or a fast refreshable materialized view with joins. The appropriate materialized view logs must be created on the tables as required for the corresponding type of fast refreshable materialized view. Note that the Oracle Database also allows the special case of a single table materialized view with joins only provided the ROWID column has been included in the SELECT list and in the materialized view log. This is shown in the defining query of the view_with_unionall. The SELECT list of each query must include a UNION ALL marker, and the UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block. See "UNION ALL Marker" for more information regarding UNION ALL markers. Some features such as outer joins, insert-only aggregate materialized view queries and remote tables are not supported for materialized views with. UNION ALL. Note, however, that materialized views used in replication, which do not contain joins or aggregates, can be fast refreshed when UNIONALL or remote tables are used. The compatibility initialization parameter must be set to 9. 2. 0 or higher to create a fast refreshable materialized view with UNION ALL.

Beperkingen op geneste MV’s 1) All parent and base materialized views must contain joins or aggregates. 2) All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log. 3) You cannot create both a materialized view and a prebuilt materialized view on the same table.

Een foutmelding. En nu? ORA-12032: cannot use rowid column from materialized view log on … ORA-12033: cannot use filter columns from materialized view log on … ORA-12052: cannot fast refresh materialized view … ORA-12053: this is not a valid nested materialized view ORA-22818: subquery expressions not allowed here ORA-23413: table … does not have a materialized view log ORA-32334: cannot create prebuilt materialized view on a table already referenced by a MV ORA-32401: materialized view log on … does not have new values ORA-32412: encrypted column … not allowed in the materialized view log ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view and “The materialized view is created, but just doesn't fast refresh”

Uh-oh… een ORA-12054 • • • • • • Restriction 1 for basic MV fast refresh: The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM. Restriction 7 for basic MV fast refresh: It cannot contain nested queries that have ANY, ALL, or NOT EXISTS. Restriction 8 for basic MV fast refresh: It cannot contain a [START WITH. . . ] CONNECT BY clause. Restriction 10 for basis MV fast refresh: ON COMMIT materialized views cannot have remote detail tables. Restriction 5 for join MV fast refresh: You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement. Restriction 6 for join MV fast refresh: ANSI joins are not possible Restriction 8 for aggregate MV fast refresh: Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh. Restriction 10 for aggregate MV fast refresh: Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(x)) or AVG(x)+ AVG(x) are not allowed. Restriction 12 for aggregate MV fast refresh: If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified. Restriction 13 for aggregate MV fast refresh: The SELECT list must contain all GROUP BY columns. Restriction 18 for aggregate MV fast refresh: For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable. Restriction 19 for aggregate MV fast refresh: For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)". Restriction 21 for aggregate MV fast refresh: The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view. Restriction 23 for aggregate MV fast refresh: Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided unique constraints exist on the join columns of the inner join table. Restriction 24 for aggregate MV fast refresh: If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator. Restriction 25 for aggregate MV fast refresh: Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged. Restriction 26 for aggregate MV fast refresh: It cannot contain a HAVING clause with a subquery. Restriction 1 for union all MV fast refresh: The defining query must have the UNION ALL operator at the top level. Restriction 3 for union all MV fast refresh: The SELECT list of each query must include a UNION ALL marker, and the UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block. Restriction 5 for union all MV fast refresh: Insert-only aggregate materialized view queries are not supported for materialized views with UNION ALL. Restriction 6 for union all MV fast refresh: Remote tables are not supported for materialized views with UNION ALL.

Commentaar op blogpost The worst part of the whole situation is that it's difficult to tell from the documentation whether the process you're trying to implement is even supported. You have to poke, prod, and tweak various settings until you hit that nirvana-like state where Oracle finally gives up and says "Okay, I'll let you Fast Refresh now".

MV_CAPABILITIES_TABLE • zelfde principe als PLAN_TABLE • dbms_mview. explain_mview • FAST_REFRESH_% en POSSIBLE mvl 3. sql

Casus 1: valideren bedrijfsregels • Statische entiteitregel mvl 4 a. sql • Statische interentiteitsregel mvl 4 b. sql • enq: JI - contention mvl 4 c. sql

Casus 2: Synchroniseren datamodel NETBEHEERDERS # id * ean_code * naam AANSLUITINGEN # id * nbr_id * ean_code AANSLUITING_ PROFIELEN # id * asg_id * kolom * waarde * begindatum o einddatum C-AR AANSLUITING # ean_code * nbr_naam * kolom 1 -waarde o kolom 2 -waarde mvl 5. sql