Let me start explaining about what is a Join dependent expression?
An expression consisting of columns from tables directly or indirectly joined through equijoins to the partitioned detail tables on the partitioning key and which is either a dimensional attribute or a dimension hierarchical parent of the joining key is called a join dependent expression and the set of table on path to the detailed table are called join dependent table.
Here is the snippet of code to demonstrate how oracle does rewrite against MV if PCT is enabled through Join dependent expression.
rajesh@ORA11R2> create table t1 as
2 select * from scott.dept;
Table created.
Elapsed: 00:00:00.57
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t2
2 partition by list(deptno)
3 ( partition p1 values (10),
4 partition p2 values (20),
5 partition p3 values (30),
6 partition pmax values (default) )
7 as select * from scott.emp;
Table created.
Elapsed: 00:00:00.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t1 add constraint t1_pk
2 primary key(deptno);
Table altered.
Elapsed: 00:00:00.23
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t2 add constraint t2_fk
2 foreign key(deptno) references t1;
Table altered.
Elapsed: 00:00:00.15
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t2 modify deptno not null;
Table altered.
Elapsed: 00:00:00.21
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats(user,'T1');
3 dbms_stats.gather_table_stats(user,'T2');
4 end;
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.34
rajesh@ORA11GR2>
rajesh@ORA11GR2> create materialized view t1_t2_mv
2 build immediate
3 refresh on demand
4 enable query rewrite as
5 select t1.deptno,count(*)
6 from t1, t2
7 where t1.deptno = t2.deptno
8 group by t1.deptno;
Materialized view created.
Elapsed: 00:00:01.65
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T1_T2_MV');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@ORA11GR2>
Here the table 'T1' is joined to partitioned detail table 'T2' on the partioned key column. so PCT is enabled on this MV through join dependent table (T1). you can see this clearly as below.
rajesh@ORA11GR2> select capability_name,possible,
2 case when related_text is not null then
3 '( '||related_text||') '||msgtxt
4 else msgtxt end as related_text
5 from table ( explain_mview('T1_T2_MV'));
CAPABILITY_NAME P RELATED_TEXT
------------------------------ - ------------------------------------------------------------
PCT T
REFRESH_COMPLETE T
REFRESH_FAST T
REWRITE T
PCT_TABLE F ( T1) relation is not a partitioned table
PCT_TABLE T ( T2)
REFRESH_FAST_AFTER_INSERT F ( RAJESH.T1) the detail table does not have a materialized v
iew log
REFRESH_FAST_AFTER_INSERT F ( RAJESH.T2) the detail table does not have a materialized v
iew log
REFRESH_FAST_AFTER_ONETAB_DML F see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML F see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT T
REWRITE_FULL_TEXT_MATCH T
REWRITE_PARTIAL_TEXT_MATCH T
REWRITE_GENERAL T
REWRITE_PCT F general rewrite is not possible or PCT is not possible on an
y of the detail tables
PCT_TABLE_REWRITE F ( T1) relation is not a partitioned table
PCT_TABLE_REWRITE F ( T2) PCT is enabled through a join dependency
17 rows selected.
Elapsed: 00:00:00.96
rajesh@ORA11GR2> select detailobj_name, detail_partition_name, freshness
2 from dba_mview_detail_partition
3 where mview_name ='T1_T2_MV';
DETAILOBJ_NAME DETAIL_PARTITION_NAME FRESH
------------------------------ ------------------------------ -----
T2 P1 FRESH
T2 P2 FRESH
T2 P3 FRESH
T2 PMAX FRESH
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select count(*) from t2 where deptno = 10;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2337769562
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV | 1 | 6 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1_T2_MV"."DEPTNO"=10)
rajesh@ORA11GR2> select count(*) from t2 where deptno = 30;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2337769562
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV | 1 | 6 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1_T2_MV"."DEPTNO"=30)
rajesh@ORA11GR2> select count(*) from t2 where deptno = 20;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2337769562
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV | 1 | 6 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1_T2_MV"."DEPTNO"=20)
rajesh@ORA11GR2> set autotrace off;
so far everything looks fine. But what happens when a particular partition becomes STALE in this Mview ?
rajesh@ORA11GR2> delete from t2 where deptno = 20
2 and rownum = 1;
1 row deleted.
Elapsed: 00:00:00.00
rajesh@ORA11GR2> commit;
Commit complete.
Elapsed: 00:00:00.00
rajesh@ORA11GR2> select detailobj_name, detail_partition_name, freshness
2 from dba_mview_detail_partition
3 where mview_name ='T1_T2_MV';
DETAILOBJ_NAME DETAIL_PARTITION_NAME FRESH
------------------------------ ------------------------------ -----
T2 P1 FRESH
T2 P2 STALE
T2 P3 FRESH
T2 PMAX FRESH
Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select count(*) from t2 where deptno = 10;
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 1232830859
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION LIST SINGLE| | 3 | 9 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS FULL | T2 | 3 | 9 | 3 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------
rajesh@ORA11GR2> select count(*) from t2 where deptno = 30;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1232830859
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION LIST SINGLE| | 6 | 18 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS FULL | T2 | 6 | 18 | 3 (0)| 00:00:01 | 3 | 3 |
-----------------------------------------------------------------------------------------------
rajesh@ORA11GR2> select count(*) from t2 where deptno = 20;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1232830859
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION LIST SINGLE| | 5 | 15 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS FULL | T2 | 5 | 15 | 3 (0)| 00:00:01 | 2 | 2 |
-----------------------------------------------------------------------------------------------
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
Oracle does not rewrite against partial stale materialized views if partition change tracking on the changed table is enabled by the presence of join dependent expression in the materialized view.
this is clearly mentioned in product docs, so take care when defining MView with PCT enabled by Join dependent tracking
An expression consisting of columns from tables directly or indirectly joined through equijoins to the partitioned detail tables on the partitioning key and which is either a dimensional attribute or a dimension hierarchical parent of the joining key is called a join dependent expression and the set of table on path to the detailed table are called join dependent table.
Here is the snippet of code to demonstrate how oracle does rewrite against MV if PCT is enabled through Join dependent expression.
rajesh@ORA11R2> create table t1 as
2 select * from scott.dept;
Table created.
Elapsed: 00:00:00.57
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t2
2 partition by list(deptno)
3 ( partition p1 values (10),
4 partition p2 values (20),
5 partition p3 values (30),
6 partition pmax values (default) )
7 as select * from scott.emp;
Table created.
Elapsed: 00:00:00.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t1 add constraint t1_pk
2 primary key(deptno);
Table altered.
Elapsed: 00:00:00.23
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t2 add constraint t2_fk
2 foreign key(deptno) references t1;
Table altered.
Elapsed: 00:00:00.15
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t2 modify deptno not null;
Table altered.
Elapsed: 00:00:00.21
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats(user,'T1');
3 dbms_stats.gather_table_stats(user,'T2');
4 end;
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.34
rajesh@ORA11GR2>
rajesh@ORA11GR2> create materialized view t1_t2_mv
2 build immediate
3 refresh on demand
4 enable query rewrite as
5 select t1.deptno,count(*)
6 from t1, t2
7 where t1.deptno = t2.deptno
8 group by t1.deptno;
Materialized view created.
Elapsed: 00:00:01.65
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T1_T2_MV');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@ORA11GR2>
Here the table 'T1' is joined to partitioned detail table 'T2' on the partioned key column. so PCT is enabled on this MV through join dependent table (T1). you can see this clearly as below.
rajesh@ORA11GR2> select capability_name,possible,
2 case when related_text is not null then
3 '( '||related_text||') '||msgtxt
4 else msgtxt end as related_text
5 from table ( explain_mview('T1_T2_MV'));
CAPABILITY_NAME P RELATED_TEXT
------------------------------ - ------------------------------------------------------------
PCT T
REFRESH_COMPLETE T
REFRESH_FAST T
REWRITE T
PCT_TABLE F ( T1) relation is not a partitioned table
PCT_TABLE T ( T2)
REFRESH_FAST_AFTER_INSERT F ( RAJESH.T1) the detail table does not have a materialized v
iew log
REFRESH_FAST_AFTER_INSERT F ( RAJESH.T2) the detail table does not have a materialized v
iew log
REFRESH_FAST_AFTER_ONETAB_DML F see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML F see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT T
REWRITE_FULL_TEXT_MATCH T
REWRITE_PARTIAL_TEXT_MATCH T
REWRITE_GENERAL T
REWRITE_PCT F general rewrite is not possible or PCT is not possible on an
y of the detail tables
PCT_TABLE_REWRITE F ( T1) relation is not a partitioned table
PCT_TABLE_REWRITE F ( T2) PCT is enabled through a join dependency
17 rows selected.
Elapsed: 00:00:00.96
rajesh@ORA11GR2> select detailobj_name, detail_partition_name, freshness
2 from dba_mview_detail_partition
3 where mview_name ='T1_T2_MV';
DETAILOBJ_NAME DETAIL_PARTITION_NAME FRESH
------------------------------ ------------------------------ -----
T2 P1 FRESH
T2 P2 FRESH
T2 P3 FRESH
T2 PMAX FRESH
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select count(*) from t2 where deptno = 10;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2337769562
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV | 1 | 6 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1_T2_MV"."DEPTNO"=10)
rajesh@ORA11GR2> select count(*) from t2 where deptno = 30;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2337769562
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV | 1 | 6 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1_T2_MV"."DEPTNO"=30)
rajesh@ORA11GR2> select count(*) from t2 where deptno = 20;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2337769562
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV | 1 | 6 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1_T2_MV"."DEPTNO"=20)
rajesh@ORA11GR2> set autotrace off;
so far everything looks fine. But what happens when a particular partition becomes STALE in this Mview ?
rajesh@ORA11GR2> delete from t2 where deptno = 20
2 and rownum = 1;
1 row deleted.
Elapsed: 00:00:00.00
rajesh@ORA11GR2> commit;
Commit complete.
Elapsed: 00:00:00.00
rajesh@ORA11GR2> select detailobj_name, detail_partition_name, freshness
2 from dba_mview_detail_partition
3 where mview_name ='T1_T2_MV';
DETAILOBJ_NAME DETAIL_PARTITION_NAME FRESH
------------------------------ ------------------------------ -----
T2 P1 FRESH
T2 P2 STALE
T2 P3 FRESH
T2 PMAX FRESH
Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select count(*) from t2 where deptno = 10;
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 1232830859
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION LIST SINGLE| | 3 | 9 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS FULL | T2 | 3 | 9 | 3 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------
rajesh@ORA11GR2> select count(*) from t2 where deptno = 30;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1232830859
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION LIST SINGLE| | 6 | 18 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS FULL | T2 | 6 | 18 | 3 (0)| 00:00:01 | 3 | 3 |
-----------------------------------------------------------------------------------------------
rajesh@ORA11GR2> select count(*) from t2 where deptno = 20;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1232830859
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION LIST SINGLE| | 5 | 15 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS FULL | T2 | 5 | 15 | 3 (0)| 00:00:01 | 2 | 2 |
-----------------------------------------------------------------------------------------------
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
Oracle does not rewrite against partial stale materialized views if partition change tracking on the changed table is enabled by the presence of join dependent expression in the materialized view.
this is clearly mentioned in product docs, so take care when defining MView with PCT enabled by Join dependent tracking
