Virtual Columns and Fast Refreshable Materialized Views on Oracle 11g

Previous to Oracle 11g Rel 2, two very common and useful features previously worked well together, they being fast refreshable materialized views and the introduction of virtual columns due to the creation of function-based indexes.

To illustrate, I create and populate a little demo table: 

SQL> create table bowie (a number, b number, c number);

 
Table created.
 
SQL> insert into bowie select rownum, mod(rownum,10), mod(rownum,100)
from dual connect by level<= 100000;
 
100000rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> alter table bowie add primary key (a);
 
Table altered.
Now create a simple little function-based index:

SQL> create index bowie_func_i on bowie(b+c);

 
Index created.
If we look at the columns in the table via DBA_TAB_COLS:
SQL> select column_name, data_default, virtual_column,
hidden_column from dba_tab_cols where table_name = 'BOWIE';

 
COLUMN_NAME  DATA_DEFAULT VIR HID
------------ ------------ --- ---
SYS_NC00004$ "B"+"C"YES YES
C                         NO  NO
B                         NO  NO
A                         NO  NO
We notice Oracle has introduced a new, hidden virtual column (SYS_NC00004$), required to store statistics for use by the Cost Based Optimizer.Next we create a materialized view log on this table and a fast refreshable materialized view:

SQL> create materialized view log on bowie WITH PRIMARY KEY,SEQUENCE, ROWID (b,c) INCLUDING NEW VALUES;

 
Materialized view log created.
 
SQL> create materialized view bowie_mv
  build immediate
  refresh fast
  with primary key
  enable query rewrite
  as
  select b, count(*) from bowie group by b;
 
Materialized view created.
Collect a few statistics and we note the Materialized View does indeed get used during a query rewrite scenario:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=>null, cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE 1')

 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_MV', estimate_percent=>null, cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE 1')
 
PL/SQL procedure successfully completed. 
SQL> select b, count(*) from bowie having b > 3group by b;
 
         B   COUNT(*)
---------- ----------
         10000
         10000
         10000
         10000
         10000
         10000
 
6rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 593592962
 
---------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes 
--------------------------------------------------------------
|   0| SELECT STATEMENT             |          |     7|    42
|*  1|  MAT_VIEW REWRITE ACCESS FULL| BOWIE_MV |     7|    42
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1- filter("BOWIE_MV"."B">3)
 
Statistics
------------ ----------------------------------------------
          recursive calls
          db blockgets
          consistent gets
          physical reads
          redo size
        538 bytes sent via SQL*Net to client
        395 bytes received via SQL*Net from client
          SQL*Net roundtrips to/from client
          sorts (memory)
          sorts (disk)
          rows processed
And indeed the materialized view is fast refreshable:

SQL> insert into bowie values (100001, 5, 42);

 
1row created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_mview.refresh('BOWIE_MV', 'F');
 
PL/SQL procedure successfully completed.
 
 
SQL> select b, count(*) from bowie having b > 3group by b;
 
         B   COUNT(*)
---------- ----------
         10000
         10000
         10001
         10000
         10000
         10000
 
6rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 593592962
 
---------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost 
---------------------------------------------------------------
|   0| SELECT STATEMENT             |          |     7|    42  
|*  1|  MAT_VIEW REWRITE ACCESS FULL| BOWIE_MV |     7|    42     
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1- filter("BOWIE_MV"."B">3)
Statistics
----------------------------------------------------------
          recursive calls
          db blockgets
          consistent gets
          physical reads
          redo size
        546 bytes sent via SQL*Net to client
        395 bytes received via SQL*Net from client
          SQL*Net roundtrips to/from client
          sorts (memory)
          sorts (disk)
          rows processed
Notice how the materialized view does indeed displayed the correct updated information via the query rewrite operation . So the materialized view behaved and worked as expected even though the underlining master table has a virtual column due to the creation of the function-based index (note that QUERY_REWRITE_INTEGRITY is set to STALE_TOLERATED)Unfortunately, things go off the rails somewhat since Oracle 11g Rel 2 when a virtual column is introduced due to one of the 11g new features. For example, I now collect some Extended Statistics on this table:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', method_opt=> 'FOR COLUMNS (A,B,C) SIZE 254');

 
PL/SQL procedure successfully completed.
 
SQL> select column_name, data_default, virtual_column, hidden_column from dba_tab_cols where table_name = 'BOWIE';
 
COLUMN_NAME                    DATA_DEFAULT                      VIR HID
------------------------------ --------------------------------- --- ---
SYS_STUM4KJU$CCICS9C1UJ6UWC4YP SYS_OP_COMBINED_HASH("A","B","C") YES YES
SYS_NC00004$                   "B"+"C"                           YES YES
C                                                                NO  NO
B                                                                NO  NO
A        
Notice how extended statistics has resulted in another hidden virtual column (SYS_STUM4KJU$CCICS9C1UJ6UWC4YP) being created to store the resultant statistics.

However, if now attempt to perform a fast refresh on the Materialized View:

SQL> insert into bowie values (100002, 5, 42);

1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_mview.refresh('BOWIE_MV', 'F');
BEGIN dbms_mview.refresh('BOWIE_MV', 'F'); END;
 
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00904: "MAS$"."SYS_STUM4KJU$CCICS9C1UJ6UWC4YP": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
We get an error, complaining about the existence of this new virtual column.
If we attempted to drop and re-create the materialized view:

SQL> drop materialized view bowie_mv;

 
Materialized view dropped.
 
SQL> create materialized view bowie_mv
    build immediate
    refresh fast
    with primary key
    enable query rewrite
    as
    select b, count(*) from bowie group by b;
select b, count(*) from bowie group by b
                        *
ERROR at line 7:
ORA-12033: cannot use filter columns from materialized view log on "BOWIE"."BOWIE"
It fails, complaining that the materialized view log is somehow missing a filter column (which it isn’t).  Extended statistics and visible virtual columns are both potentially extremely useful new features introduced in 11g but unfortunately both can not be implemented on any table that needs to be fast refreshable within a complex materialized view. We get exactly the same set of issues if we add avisible virtual column via this new 11g capability: 
SQL> create table bowie2 (a number, b number, c number, d as (a+b+c));
 
Table created.
 
SQL> select column_name, data_default, virtual_column, hidden_column from dba_tab_cols where table_name = 'BOWIE2';
 
COLUMN_NAME  DATA_DEFAULT VIR HID
------------ ------------ --- ---
D            "A"+"B"+"C"  YES NO
C                         NO  NO
B                         NO  NO
A                         NO  NO
 
SQL> insert into bowie2 (a,b,c) select rownum, mod(rownum,10), mod(rownum,100) from dual connect by level <= 100000;
 
100000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> alter table bowie2 add primary key (a);
 
Table altered.
 
SQL> create materialized view log on bowie2 WITH PRIMARY KEY,SEQUENCE, ROWID (b,c) INCLUDING NEW VALUES;
 
Materialized view log created.
 
SQL> create materialized view bowie2_mv
    build immediate
    refresh fast
    with primary key
    enable query rewrite
    as
    select b, count(*) from bowie2 group by b;
select b, count(*) from bowie2 group by b
                        *
ERROR at line 7:
ORA-12033: cannot use filter columns from materialized view log on "BOWIE"."BOWIE2"

 

Advertisements

Configuring for Materialized Views

Oracle materialized views perform miracles in our goal to reduce repetitive I/O.  You want tips on tuning materialized views internal performance, see:

  • Oracle materialized views and partitioning
  • Materialized Views Tuning
  • Materialized Views Refreshing Performance

Oracle materialized views were first introduced in Oracle8, and in Oracle materialized views were enhanced to allow very fast dynamic creation of complex objects. Oracle materialized views allow sub-second response times by pre-computing aggregate information, and Oracle dynamically rewrites SQL queries to reference existing Oracle materialized views. In this article, we continue our discussion of Oracle materialized views and discuss how to set up and configure your Oracle database to use this powerful new feature. We begin with a look at the initialization parameters and continue with details of the effective management and use of Oracle materialized views.

Without Oracle materialized views you may see unnecessary repeating large-table full-table scans, as summaries are computed, over and over.
Prerequisites for using Oracle materialized views

In order to use Oracle  materialized views, the Oracle DBA must set special initialization parameters and grant special authority to the users of Oracle materialized views. You start by setting these initialization parameters within Oracle to enable the mechanisms for Oracle materialized views and query rewrite, as shown here:

Image

  • trusted:  Assumes that the Oracle materialized view is current.
  • enforced (default):  Always goes to Oracle materialized view with fresh data.
  • stale_tolerated Uses Oracle materialized view with both stale and fresh data

Next, you must grant several system privileges to all users who will be using the Oracle materialized views. In many cases, the Oracle DBA will encapsulate these grant statements into a single role and grant the role to the end users:

  • grant query rewrite to “—-“;
  • grant create materialized view to “—-“;
  • alter session set query_rewrite_enabled = “—-“;

Invoking SQL query rewrite

Once Oracle materialized views have been enabled, Oracle provides several methods for invoking query rewrite. Query rewrite is generally automatic, but you can explicitly enable it by using Isession, alter system, or SQL hints:

  • ALTER {SESSION|SYSTEM} DISABLE QUERY REWRITE
  • Select /*+REWRITE(mv1)*/…

Refreshing materialized views

In Oracle, if you specify REFRESH FAST for a single-table aggregate Oracle materialized view, you must have created a materialized view log for the underlying table, or the refresh command will fail. When creating an Oracle materialized view, you have the option of specifying whether the refresh occurs manually (ON DEMAND) or automatically (ON COMMIT, DBMS_JOB). To use the fast warehouse refresh facility, you must specify the ON DEMAND mode. To refresh the Oracle materialized view, call one of the procedures in DBMS_MVIEW.

The DBMS_MVIEW package provides three types of refresh operations:

  • DBMS_MVIEW.REFRESH:  Refreshes one or more Oracle materialized views
  • DBMS_MVIEW.REFRESH_ALL_MVIEWS:  Refreshes all Oracle materialized views
  • DBMS_MVIEW.REFRESH_DEPENDENT:  Refreshes all table-based Oracle materialized views

Manual complete refresh

A complete refresh occurs when the Oracle materialized view is initially defined, unless it references a prebuilt table, and a complete refresh may be requested at any time during the life of the Oracle materialized view. Because the refresh involves reading the detail table to compute the results for the Oracle materialized view, this can be a very time-consuming process, especially if huge amounts of data need to be read and processed.

Manual fast (incremental) refresh

If you specify REFRESH FAST (which means that only deltas performed by UPDATE, INSERT, DELETE on the base tables will be refreshed), Oracle performs further verification of the query definition to ensure that fast refresh can always be performed if any of the detail tables change. These additional checks include the following:

  • An Oracle materialized view log must be present for each detail table.
  • The RowIDs of all the detail tables must appear in the SELECT list of the MVIEW query definition.
  • If there are outer joins, unique constraints must be placed on the join columns of the inner table.

You can use the DBMS_MVIEW package to manually invoke either a fast refresh or a complete refresh, where F equals Fast Refresh and C equals Complete Refresh:

EXECUTE DBMS_MVIEW.REFRESH(’emp_dept_sum’,’F’);

Automatic fast refresh of materialized views

The automatic fast refresh feature is completely new in Oracle, so you can refresh a snapshot with DBMS_JOB in a short interval according to the snapshot log. With Oracle, it’s possible to refresh automatically on the next COMMIT performed at the master table. This ON COMMIT refreshing can be used with materialized views on single-table aggregates and materialized views containing joins only. ON COMMIT MVIEW logs must be built as ROWID logs, not as primary-key logs. For performance reasons, it’s best to create indexes on the ROWIDs of the MVIEW. Note that the underlying table for the MVIEW can be prebuilt.

Below is an example of an Oracle materialized view with an ON COMMIT refresh.

CREATE MATERIALIZED VIEW
empdep
ON PREBUILT TABLE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS SELECT empno, ename, dname, loc,
e.rowid emp_rowid,
d.rowid dep_rowid
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Creating an Oracle materialized view

To see all the steps in the creation of a materialized view, let’s take it one step at a time. The code for each step is shown here:

Step 1

optimizer_mode = choose, first_rows, or all_rows
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced
compatible = 8.1.5.0.0 (or greater)

Step 2

CREATE MATERIALIZED VIEW emp_sum
ENABLE QUERY REWRITE
AS SELECT deptno,job,SUM(sal)
FROM emp
GROUP BY deptno,job
PCTFREE 5
PCTUSED 60
NOLOGGING PARALLEL 5
TABLESPACE users
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K)
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1/12;

Step 3

execute dbms_utility.analyze_schema(‘SCOTT’,’ESTIMATE’);
execute dbms_mview.refresh(’emp_sum’);

Step 4

set autotrace on explain
SELECT deptno, job, SUM(sal)
FROM emp
GROUP BY deptno, job;
Execution Plan
———————————–
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF ‘EMP_SUM’

Step 5

CREATE MATERIALIZED VIEW LOG ON
emp_sum
WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON
dept
WITH ROWID;

Step 6

EXECUTE DBMS_MVIEW.REFRESH(’emp_sum’);

  1. Set the initialization parameters and bounce the database.
  2. Create the materialized view table. Here, we specify that the materialized view will be refreshed every two hours with the refresh fast option. Instead of using DBMS_MVIEW, you can automatically refresh the MVIEW (Snapshot) using Oracle DBMS_JOB Management.
  3. Create the optimizer statistics and refresh the materialized view.
  4. Test the materialized view.
  5. Create the MVIEW log(s) MATERIALIZED VIEW.
  6. Execute a manual complete refresh.