11g Virtual Columns and Fast Refreshable Materialized Views (What In The World)

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, we 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;
 
100000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> alter table bowie add primary key (a);
 
Table altered.

 
We 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
  2  build immediate
  3  refresh fast
  4  with primary key
  5  enable query rewrite
  6  as
  7  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 > 3 group by b;
 
         B   COUNT(*)
---------- ----------
         6      10000
         4      10000
         5      10000
         8      10000
         7      10000
         9      10000
 
6 rows selected.
 

Execution Plan
----------------------------------------------------------
Plan hash value: 593592962
 
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     7 |    42 |     2   (0)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| BOWIE_MV |     7 |    42 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("BOWIE_MV"."B">3)
 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        538  bytes sent via SQL*Net to client
        395  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

 
 

And indeed the materialized view is fast refreshable:
 

 
SQL> insert into bowie values (100001, 5, 42);
 
1 row 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 > 3 group by b;
 
         B   COUNT(*)
---------- ----------
         6      10000
         4      10000
         5      10001
         8      10000
         7      10000
         9      10000
 
6 rows selected.
 

Execution Plan
----------------------------------------------------------
Plan hash value: 593592962
 
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     7 |    42 |     2   (0)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| BOWIE_MV |     7 |    42 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("BOWIE_MV"."B">3)
 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        546  bytes sent via SQL*Net to client
        395  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  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                                                                NO  NO

 
 
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
  2  build immediate
  3  refresh fast
  4  with primary key
  5  enable query rewrite
  6  as
  7  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).  We get exactly the same set of issues if we add a visible 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
  2  build immediate
  3  refresh fast
  4  with primary key
  5  enable query rewrite
  6  as
  7  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"

 
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.

I raised this issue with Oracle Support who have raised bug 10281402 as a result as it occurs in both 11.2.0.1 and 11.2.0.2 on various platforms I’ve tested.