Example: Materialized View & Query Rewrite

We can speed-up data, when query table by using materialized view & query rewrite;

This is only example to show creating and statistics with materialized view & query rewrite.

- Create "TEST" table

SQL> create table TEST nologging as select * from all_objects;

- Assume we have this statement often... (select sum(object_id), owner from TEST group by owner)

SQL> set autot trace explain;

SQL> select sum(object_id), owner from TEST group by owner;

Execution Plan
----------------------------------------------------------
Plan hash value: 3837450812

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46319 | 1357K| 249 (4)| 00:00:02 |
| 1 | HASH GROUP BY | | 46319 | 1357K| 249 (4)| 00:00:02 |
| 2 | TABLE ACCESS FULL| TEST | 46319 | 1357K| 243 (1)| 00:00:02 |
----------------------------------------------------------------------------


-Begin create materialized view...

SQL> CREATE MATERIALIZED VIEW LOG ON TEST WITH ROWID INCLUDING NEW VALUES;

Materialized view log created.

- Add filter columns to the materialized view log

SQL> ALTER MATERIALIZED VIEW LOG FORCE ON TEST ADD ROWID, SEQUENCE(object_id,owner) including new values;

Materialized view log altered.

- create materialized view & enable query rewrite

SQL> CREATE MATERIALIZED VIEW TEST_mv REFRESH FAST WITH ROWID ENABLE QUERY REWRITE as select sum(object_id) c1, owner c2 from TEST group by owner;

Materialized view created.

- Test query again

SQL> set autot trace explain

SQL> select sum(object_id), owner from TEST group by owner;

Execution Plan
----------------------------------------------------------
Plan hash value: 2827064948

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 1470 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| TEST_MV | 49 | 1470 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

*** we found oracle use query rewrite and better performance....


Anyway We should know about QUERY_REWRITE_INTEGRITY parameter = { enforced | trusted | stale_tolerated }

- enforced : Oracle enforces and guarantees consistency and integrity.
- trusted : Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle.
- stale_tolerated : Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.


And the session must enabled QUERY_REWRITE_ENABLED.

SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE;

Session altered.

SQL> select owner, sum(object_id) from TEST group by owner;

Execution Plan
----------------------------------------------------------
Plan hash value: 3837450812

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46319 | 1357K| 249 (4)| 00:00:02 |
| 1 | HASH GROUP BY | | 46319 | 1357K| 249 (4)| 00:00:02 |
| 2 | TABLE ACCESS FULL| TEST | 46319 | 1357K| 243 (1)| 00:00:02 |
----------------------------------------------------------------------------


SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

Session altered.

SQL> select owner, sum(object_id) from TEST group by owner;

Execution Plan
----------------------------------------------------------
Plan hash value: 2827064948

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 1470 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| TEST_MV | 49 | 1470 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------