Just a simple example to show what it takes to get this to work…
SQL> set lines 1000 trims on pages 100 SQL> explain plan for select count(*) from member; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2991848243 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 586K (1)| 01:57:21 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MEMBER | 90M| 586K (1)| 01:57:21 | --------------------------------------------------------------------- 9 rows selected. SQL> create materialized view mvtest as select /*+ parallel(x,16) */ count(*) from expanalytics.member x; Materialized view created. SQL> explain plan for select count(*) from member; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2991848243 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 586K (1)| 01:57:21 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MEMBER | 90M| 586K (1)| 01:57:21 | --------------------------------------------------------------------- 9 rows selected. SQL> alter materialized view mvtest enable query rewrite; Materialized view altered. SQL> explain plan for select count(*) from member; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3317803245 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | MAT_VIEW REWRITE ACCESS FULL| MVTEST | 1 | 13 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) 13 rows selected. SQL> set timing on SQL> select count(*) from member; COUNT(*) ---------- 90881231 Elapsed: 00:00:00.00 SQL>