conn hr/hr -- Prepare the large test table BIGTAB :) create table bigtab nologging as select * from all_objects union all select * from all_objects union all select * from all_objects / insert /*+ APPEND */ into bigtab select * from bigtab; commit; insert /*+ APPEND */ into bigtab select * from bigtab; commit; insert /*+ APPEND */ into bigtab select * from bigtab; commit; analyze table bigtab compute statistics; select count(*) from bigtab; COUNT(*) ---------- 1231224 -- Run query against this BIGTABLE, initially this quewry will require a full scan of the large table set autotrace on set timing on select owner, count(*) from bigtab group by owner; ... 35 rows selected. Elapsed: 00:00:01.33 Execution Plan ---------------------------------------------------------- Plan hash value: 4245726685 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 35 | 175 | 4882 (6)| 00:00:59 | | 1 | HASH GROUP BY | | 35 | 175 | 4882 (6)| 00:00:59 | | 2 | TABLE ACCESS FULL| BIGTAB | 1231K| 6011K| 4689 (2)| 00:00:57 | ----------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 16955 consistent gets 16944 physical reads 0 redo size 1234 bytes sent via SQL*Net to client 422 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 35 rows processed -- Now let’s create an appropriete Materialized View for the above query conn / as sysdba grant query rewrite to hr; conn hr/hr alter session set query_rewrite_enabled=true; alter session set query_rewrite_integrity=enforced; create materialized view mv_bigtab build immediate refresh on commit enable query rewrite as select owner, count(*) from bigtab group by owner / analyze table mv_bigtab compute statistics; set timing on set autotrace traceonly select owner, count(*) from bigtab group by owner; ... 35 rows selected. Elapsed: 00:00:00.05 Execution Plan ---------------------------------------------------------- Plan hash value: 2837210801 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 35 | 350 | 3 (0)|00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL| MV_BIGTAB | 35 | 350 | 3 (0)|00:00:01 | ------------------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 6 consistent gets <- !! check the i/o reduction :) 0 physical reads 0 redo size 1234 bytes sent via SQL*Net to client 422 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 35 rows processed set autotrace off set timing off -- Now lets add a new row to the BIGTAB table and commit the change, committing the change now includes the mv maintanance also -- this burden is heavy if you are creating a mv on a dml loaded table. For the new row cbo will agai use the mv, not the table. insert into bigtab (owner, object_name, object_type, object_id) values ('Owner', 'Object_name', 'object_type', 1111111); commit; set timing on set autotrace traceonly select owner, count(*) from bigtab where owner = 'Owner' group by owner; OWNER COUNT(*) ------------------------------ ---------- Owner 1 Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2837210801 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)|00:00:01 | |* 1 | MAT_VIEW REWRITE ACCESS FULL| MV_BIGTAB | 1 | 10 | 3 (0)|00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("MV_BIGTAB"."OWNER"='Owner') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets !!! 0 physical reads 0 redo size 472 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed set autotrace off set timing off -- and of course the beauty of query rewrite; if it can be retreived over mv cbo will choose to benetif this set timing on set autotrace traceonly select count(*) from bigtab where owner = 'SYS' ; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1005639138 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 10 | | | |* 2 | MAT_VIEW REWRITE ACCESS FULL| MV_BIGTAB | 1 | 10 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("MV_BIGTAB"."OWNER"='SYS') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets !!! 0 physical reads 0 redo size 413 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed set autotrace off set timing off