привожу большой пример, демонстрирующий подход:
--sample detail fact table
drop table detail_table;
create table detail_table as
select *
from all_objects;
--sample dimension table
create table dimension_table as
select distinct object_type
from detail_table;
begin
DBMS_STATS.GATHER_TABLE_STATS ('scott','detail_table');
end;
begin
DBMS_STATS.GATHER_TABLE_STATS ('scott','dimension_table');
end;
--create prebuilt table for mv
create table test_mv2 as
select object_type, count(*) fcount
from detail_table
group by object_type;
--Jonathan Lewis:
--if you create the MV with the "never refresh" option you are allowed to update the underlying table directly.
drop materialized view scott.test_mv2;
--владельцу нужны права "QUERY REWRITE"
create materialized view test_mv2
on prebuilt table never refresh
ENABLE QUERY REWRITE
as
select object_type, count(*) fcount
from detail_table
group by object_type;
--collect statistics using the DBMS_STATS package. Oracle Database needs the
--statistics generated by this package to optimize query rewrite.
begin
DBMS_STATS.GATHER_TABLE_STATS ('scott','test_mv2');
end;
--пробуем DML:
select * from test_mv2
insert into test_mv2 values ('TEST_TYPE',5000000);
delete from scott.test_mv2
where object_type='TEST_TYPE'
--пробуем query rewrite
Analyzing Query Rewrites of Materialized Views
http://www.oracle.com/technology/obe/obe10gdb/bidw/mvplans/mvplans.htm
--какие права нужны выполняющему?
grant GLOBAL QUERY REWRITE to scott
grant ALTER ANY MATERIALIZED VIEW to scott
--владелец может с "QUERY REWRITE"
alter materialized view scott.test_mv2 ENABLE QUERY REWRITE
alter session set QUERY_REWRITE_ENABLED = true
alter session set QUERY_REWRITE_INTEGRITY = stale_tolerated
--из планов видно, что SELECT из detail_table перенаправлен на mat.view:
--а) как при простой выборке
explain plan for
select /*+ REWRITE_OR_ERROR */
object_type, count(*) fcount
from detail_table
group by object_type
order by fcount desc
SELECT * FROM TABLE(dbms_xplan.display);
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35 | 455 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 35 | 455 | 4 (25)| 00:00:01 |
| 2 | MAT_VIEW REWRITE ACCESS FULL| TEST_MV2 | 35 | 455 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
--б) так и при выборке из соединения:
explain plan for
select /*+ REWRITE_OR_ERROR */
dimension_table.object_type, count(*) fcount
from detail_table, dimension_table
where detail_table.object_type=dimension_table.object_type
group by dimension_table.object_type
order by fcount desc
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 792 | 9 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 36 | 792 | 9 (34)| 00:00:01 |
| 2 | HASH GROUP BY | | 36 | 792 | 9 (34)| 00:00:01 |
|* 3 | HASH JOIN | | 36 | 792 | 7 (15)| 00:00:01 |
| 4 | MAT_VIEW REWRITE ACCESS FULL| TEST_MV2 | 35 | 455 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DIMENSION_TABLE | 37 | 333 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TEST_MV2"."OBJECT_TYPE"="DIMENSION_TABLE"."OBJECT_TYPE")
--======
--разбор ошибок REWRITE
--1) создадим таблицу для приема рез-тов dbms_mview.EXPLAIN_REWRITE
CREATE TABLE REWRITE_TABLE(
statement_id VARCHAR2(30), -- id for the query
mv_owner VARCHAR2(30), -- owner of the MV
mv_name VARCHAR2(30), -- name of the MV
sequence INTEGER, -- sequence no of the error msg
query VARCHAR2(2000),-- user query
query_block_no INTEGER, -- block no of the current subquery
rewritten_txt VARCHAR2(2000),-- rewritten query
message VARCHAR2(512), -- EXPLAIN_REWRITE error msg
pass VARCHAR2(3), -- rewrite pass no
mv_in_msg VARCHAR2(30), -- MV in current message
measure_in_msg VARCHAR2(30), -- Measure in current message
join_back_tbl VARCHAR2(30), -- Join back table in current msg
join_back_col VARCHAR2(30), -- Join back column in current msg
original_cost INTEGER, -- Cost of original query
rewritten_cost INTEGER, -- Cost of rewritten query
flags INTEGER, -- associated flags
reserved1 INTEGER, -- currently not used
reserved2 VARCHAR2(10)) -- currently not used
--2)выдать в таблицу ошибки
begin
delete from REWRITE_TABLE;
dbms_mview.EXPLAIN_REWRITE
( query => 'select
object_type, count(*) fcount
from scott.detail_table
group by object_type'
, mv => 'scott.test_mv2'
, statement_id => 'EXPLAIN_REWRITE demo' );
end;
--3)посмотреть выдачу:
select * from REWRITE_TABLE