Pages

Friday, June 26, 2009

In the third part I'm going to examine all restrictions for aggregate materialized views, as described in the documentation.

For convenience of this post, I have numbered and slightly rearranged them to contain one restriction per number:

1) All restrictions from "General Restrictions on Fast Refresh".2) All tables in the materialized view must have materialized view logs3) The materialized view logs must contain all columns from the table referenced in the materialized view.4) None of the columns in the base table, referred to in the materialized view log, can be encrypted.5) All tables in the materialized view must have materialized view logs specified with ROWID6) All tables in the materialized view must have materialized view logs specified with INCLUDING NEW VALUES.7) 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.8) Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.9) COUNT(*) must be specified.10) 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.11) For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present. Oracle recommends that SUM(expr) be specified.12) If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified.13) The SELECT list must contain all GROUP BY columns.14) If the materialized view has MIN or MAX aggregates, then fast refresh is supported only on conventional DML inserts and direct loads.15) If the materialized view has SUM(expr) but no COUNT(expr), then fast refresh is supported only on conventional DML inserts and direct loads.16) If the materialized view has no COUNT(*), then fast refresh is supported only on conventional DML inserts and direct loads.17) A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.18) Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged.19) 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.20) 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)".21) If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.22) 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.23) Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified.24) 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.25) If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

As mentioned in the first blogpost in this series, there is an extra restriction regarding aggregate MV's:

26) It cannot contain a HAVING clause with a subquery.

So this will be quite a lengthy and even tedious post, as you can imagine by the list above ... but for a good cause :-)

Then I set the encryption key once. For subsequent runs it is not needed to set anymore, so I commented it out. The step creates a file called "ewallet.p12" in the location specified in the previous step.

rwijk@ORA11GR1> --alter system set encryption key identified by rwijkrwijk@ORA11GR1> --/

Next action is to open the encryption wallet and encrypt the deptno column:

rwijk@ORA11GR1> alter system set encryption wallet open identified by rwijk 2 /

18) 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.

19) 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)".

24) If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

First recreate the primary key on mydept and then change the where clause from "e.deptno = d.deptno (+)" to "e.deptno between d.deptno (+) and 100". Functionally the same, as all deptno's are less than 100.