Oracle materialized view and query rewrite

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>