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

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
11/6/2015
|
3
|
©2013 Ciber, Inc.
Verversingen
• Compleet
• Incrementeel
• transactie
• handmatig
• Per partitie (PCT)
• Synchroon (12c)
Hoe ziet een MV log eruit?
WITH
• OBJECT ID
• PRIMARY KEY
• ROWID
• SEQUENCE
• COMMIT SCN
• (kolommen)
• INCLUDING / EXCLUDING NEW VALUES
mvl1.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
select
,
,
from
Δ
B
?
Δ
C
Δ
a.col1
sum(b.col2) som_col2
max(c.col3) max_col3
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.col1
Algemene beperkingen
1.
2.
3.
4.
5.
6.
7.
8.
9.
The materialized view must not contain references to nonrepeating expressions like SYSDATE and ROWNUM.
The materialized view must not contain references
to RAW or LONG RAW data types.
It cannot contain a SELECT list subquery.
It cannot contain analytic functions (for example, RANK) in
the SELECT clause.
It cannot contain a MODEL clause.
It cannot contain nested queries that have ANY, ALL,
or NOT EXISTS.
It cannot contain a [START WITH …] CONNECT BY clause.
It cannot contain multiple detail tables at different sites.
ON COMMIT materialized views cannot have remote detail
tables.
mvl2.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(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 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 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 withUNION 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(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
mvl3.sql
Casus 1: valideren bedrijfsregels
• Statische entiteitregel
mvl4a.sql
• Statische interentiteitsregel
mvl4b.sql
• enq: JI - contention
mvl4c.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_ean_code
* nbr_naam
* kolom1-waarde
o kolom2-waarde
mvl5.sql
Bedankt voor jullie aandacht
[email protected]
@rwijk
rwijk.blogspot.com
11/6/2015
|
22
|
©2013 Ciber, Inc.