Partition change Tracking is the ability to identify which rows in materialized view's are affected by certain fact table partition changes. When a partition is modified in fact table those rows become stale and all others remain fresh, rather than considering entire materialized view as stale this allows query rewrite to use those rows that are fresh while the query_rewrite_integrity is set to ENFORCED or TRUSTED.
A new data dictionary dba_mview_detail_partition was introduced in Oracle 11g will show which partitions are are stale or fresh.
Here is the snipped of code to demonstrate Partition change Tracking
rajesh@ORA11GR2> create table emp
2 partition by list(deptno)
3 (
4 partition p1 values (1),
5 partition p2 values (2),
6 partition p3 values (3),
7 partition p4 values (4),
8 partition p5 values (5),
9 partition pmax values (default)
10 )
11 nologging
12 as
13 select level as empno,
14 dbms_random.string('A',30) as ename,
15 dbms_random.string('B',7) as job,
16 sysdate - mod(level,100) as hire_date,
17 dbms_random.value(1,1000) as comm,
18 dbms_random.value(1,10000) as salary,
19 mod(level,5)+1 as deptno
20 from dual
21 connect by level <= 1000000;
Table created.
Elapsed: 00:01:21.53
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
2 add constraint emp_pk primary key(empno);
Table altered.
Elapsed: 00:00:11.25
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname=>user,
4 tabname=>'EMP',
5 estimate_percent=>dbms_stats.auto_sample_size,
6 cascade=>false,
7 method_opt=>'for all indexed columns size 254');
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.25
rajesh@ORA11GR2> create table dept
2 as
3 select level as deptno,
4 dbms_random.string('C',10) as dname,
5 dbms_random.string('E',7) as loc
6 from dual
7 connect by level <= 5;
Table created.
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table dept
2 add constraint dept_pk primary key(deptno);
Table altered.
Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname=>user,
4 tabname=>'DEPT',
5 estimate_percent=>dbms_stats.auto_sample_size,
6 cascade=>false,
7 method_opt=>'for all indexed columns size 254');
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@ORA11GR2> alter table emp add
2 constraint emp_fk
3 foreign key(deptno) references dept;
Table altered.
Elapsed: 00:00:05.96
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno not null;
Table altered.
Elapsed: 00:00:00.06
rajesh@ORA11GR2>
The fact table is partition by list one for each department.
rajesh@ORA11GR2> create materialized view emp_dept_mv
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select e.deptno,count(*)
7 from dept d,
8 emp e
9 where e.deptno = d.deptno
10 group by e.deptno;
Materialized view created.
Elapsed: 00:00:06.34
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'EMP_DEPT_MV');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
2 from dba_mview_detail_partition
3 where mview_name ='EMP_DEPT_MV'
4 /
MVIEW_NAME DETAIL_PARTITION_NAME DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV P1 1 FRESH
EMP_DEPT_MV P2 2 FRESH
EMP_DEPT_MV P3 3 FRESH
EMP_DEPT_MV P4 4 FRESH
EMP_DEPT_MV P5 5 FRESH
EMP_DEPT_MV PMAX 6 FRESH
6 rows selected.
Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2> show parameter query_rewrite;
NAME TYPE VALUE
------------------------------------ ----------- -----------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select count(*)
2 from emp
3 where deptno =1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2723375566
---------------------------------------------------------------------------------------------
| 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| EMP_DEPT_MV | 1 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP_DEPT_MV"."DEPTNO"=1)
rajesh@ORA11GR2> set autotrace off;
Now the query got rewritten to use materialized view to produce it's results.
Marking any changes to fact table, those rows become stale in Materialized views.
rajesh@ORA11GR2> delete from emp
2 where deptno = 1
3 and rownum = 1;
1 row deleted.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
2 from dba_mview_detail_partition
3 where mview_name ='EMP_DEPT_MV'
4 /
MVIEW_NAME DETAIL_PARTITION_NAME DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV P1 1 STALE
EMP_DEPT_MV P2 2 FRESH
EMP_DEPT_MV P3 3 FRESH
EMP_DEPT_MV P4 4 FRESH
EMP_DEPT_MV P5 5 FRESH
EMP_DEPT_MV PMAX 6 FRESH
6 rows selected.
Elapsed: 00:00:00.04
Now the query rewrite is not possible for partition p1 since it became STALE. Accessing data from Mview will produce incorrect results, so the optimizer went scanning the base tables to answer this query.
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*)
2 from emp
3 where deptno =1;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2365616264
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 806 (1)| 00:00:10 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION LIST SINGLE| | 2000 | 26000 | 806 (1)| 00:00:10 | KEY | KEY |
| 3 | TABLE ACCESS FULL | EMP | 2000 | 26000 | 806 (1)| 00:00:10 | 1 | 1 |
-----------------------------------------------------------------------------------------------
But still query rewrite is possible for FRESH partitions.
rajesh@ORA11GR2> select count(*)
2 from emp
3 where deptno =2;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2723375566
---------------------------------------------------------------------------------------------
| 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| EMP_DEPT_MV | 1 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP_DEPT_MV"."DEPTNO"=2)
A new data dictionary dba_mview_detail_partition was introduced in Oracle 11g will show which partitions are are stale or fresh.
Here is the snipped of code to demonstrate Partition change Tracking
rajesh@ORA11GR2> create table emp
2 partition by list(deptno)
3 (
4 partition p1 values (1),
5 partition p2 values (2),
6 partition p3 values (3),
7 partition p4 values (4),
8 partition p5 values (5),
9 partition pmax values (default)
10 )
11 nologging
12 as
13 select level as empno,
14 dbms_random.string('A',30) as ename,
15 dbms_random.string('B',7) as job,
16 sysdate - mod(level,100) as hire_date,
17 dbms_random.value(1,1000) as comm,
18 dbms_random.value(1,10000) as salary,
19 mod(level,5)+1 as deptno
20 from dual
21 connect by level <= 1000000;
Table created.
Elapsed: 00:01:21.53
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
2 add constraint emp_pk primary key(empno);
Table altered.
Elapsed: 00:00:11.25
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname=>user,
4 tabname=>'EMP',
5 estimate_percent=>dbms_stats.auto_sample_size,
6 cascade=>false,
7 method_opt=>'for all indexed columns size 254');
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.25
rajesh@ORA11GR2> create table dept
2 as
3 select level as deptno,
4 dbms_random.string('C',10) as dname,
5 dbms_random.string('E',7) as loc
6 from dual
7 connect by level <= 5;
Table created.
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table dept
2 add constraint dept_pk primary key(deptno);
Table altered.
Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname=>user,
4 tabname=>'DEPT',
5 estimate_percent=>dbms_stats.auto_sample_size,
6 cascade=>false,
7 method_opt=>'for all indexed columns size 254');
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@ORA11GR2> alter table emp add
2 constraint emp_fk
3 foreign key(deptno) references dept;
Table altered.
Elapsed: 00:00:05.96
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno not null;
Table altered.
Elapsed: 00:00:00.06
rajesh@ORA11GR2>
The fact table is partition by list one for each department.
rajesh@ORA11GR2> create materialized view emp_dept_mv
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select e.deptno,count(*)
7 from dept d,
8 emp e
9 where e.deptno = d.deptno
10 group by e.deptno;
Materialized view created.
Elapsed: 00:00:06.34
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'EMP_DEPT_MV');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
2 from dba_mview_detail_partition
3 where mview_name ='EMP_DEPT_MV'
4 /
MVIEW_NAME DETAIL_PARTITION_NAME DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV P1 1 FRESH
EMP_DEPT_MV P2 2 FRESH
EMP_DEPT_MV P3 3 FRESH
EMP_DEPT_MV P4 4 FRESH
EMP_DEPT_MV P5 5 FRESH
EMP_DEPT_MV PMAX 6 FRESH
6 rows selected.
Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2> show parameter query_rewrite;
NAME TYPE VALUE
------------------------------------ ----------- -----------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select count(*)
2 from emp
3 where deptno =1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2723375566
---------------------------------------------------------------------------------------------
| 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| EMP_DEPT_MV | 1 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP_DEPT_MV"."DEPTNO"=1)
rajesh@ORA11GR2> set autotrace off;
Now the query got rewritten to use materialized view to produce it's results.
Marking any changes to fact table, those rows become stale in Materialized views.
rajesh@ORA11GR2> delete from emp
2 where deptno = 1
3 and rownum = 1;
1 row deleted.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
2 from dba_mview_detail_partition
3 where mview_name ='EMP_DEPT_MV'
4 /
MVIEW_NAME DETAIL_PARTITION_NAME DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV P1 1 STALE
EMP_DEPT_MV P2 2 FRESH
EMP_DEPT_MV P3 3 FRESH
EMP_DEPT_MV P4 4 FRESH
EMP_DEPT_MV P5 5 FRESH
EMP_DEPT_MV PMAX 6 FRESH
6 rows selected.
Elapsed: 00:00:00.04
Now the query rewrite is not possible for partition p1 since it became STALE. Accessing data from Mview will produce incorrect results, so the optimizer went scanning the base tables to answer this query.
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*)
2 from emp
3 where deptno =1;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2365616264
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 806 (1)| 00:00:10 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION LIST SINGLE| | 2000 | 26000 | 806 (1)| 00:00:10 | KEY | KEY |
| 3 | TABLE ACCESS FULL | EMP | 2000 | 26000 | 806 (1)| 00:00:10 | 1 | 1 |
-----------------------------------------------------------------------------------------------
But still query rewrite is possible for FRESH partitions.
rajesh@ORA11GR2> select count(*)
2 from emp
3 where deptno =2;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2723375566
---------------------------------------------------------------------------------------------
| 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| EMP_DEPT_MV | 1 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP_DEPT_MV"."DEPTNO"=2)
What happens when we refresh Mview? The affected rows in Materialized view are deleted and re-inserted.
rajesh@ORA11GR2>
rajesh@ORA11GR2> @trace.sql
rajesh@ORA11GR2> alter session set timed_statistics=true;
Session altered.
Elapsed: 00:00:00.00
rajesh@ORA11GR2> alter session set events '10046
2 trace name context forever, level 12';
Session altered.
Elapsed: 00:00:00.03
rajesh@ORA11GR2> set echo off;
rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_DEPT_MV');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.59
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
2 from dba_mview_detail_partition
3 where mview_name ='EMP_DEPT_MV'
4 /
MVIEW_NAME DETAIL_PARTITION_NAME DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV P1 1 FRESH
EMP_DEPT_MV P2 2 FRESH
EMP_DEPT_MV P3 3 FRESH
EMP_DEPT_MV P4 4 FRESH
EMP_DEPT_MV P5 5 FRESH
EMP_DEPT_MV PMAX 6 FRESH
6 rows selected.
Elapsed: 00:00:00.06
rajesh@ORA11GR2>
Tkprof shows me this:
--------------------------------------------------------------------------------
SQL ID: 88sv43hhjtc0y
Plan Hash: 3770894312
DELETE FROM "RAJESH"."EMP_DEPT_MV"
WHERE
( ( ( ("DEPTNO" IN (1)) )) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 3 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 3 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE EMP_DEPT_MV (cr=3 pr=0 pw=0 time=0 us)
1 MAT_VIEW ACCESS FULL EMP_DEPT_MV (cr=3 pr=0 pw=0 time=0 us cost=3 size=3 card=1)
--------------------------------------------------------------------------------
SQL ID: 2hk25tz0vayzs
Plan Hash: 3660430689
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "RAJESH"."EMP_DEPT_MV"SELECT /*+
X_DYN_PRUNE */ "E"."DEPTNO" , COUNT(*) FROM "EMP" "E","DEPT" "D" WHERE
("E"."DEPTNO"="D"."DEPTNO") AND ( ( ( ("E"."DEPTNO" IN (1)) ) ) )GROUP BY
"E"."DEPTNO"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.18 1.37 2894 2910 24 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.18 1.37 2894 2910 24 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=2910 pr=2894 pw=0 time=0 us)
1 SORT GROUP BY NOSORT (cr=2905 pr=2893 pw=0 time=0 us cost=806 size=26000 card=2000)
199999 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2905 pr=2893 pw=0 time=2304007 us cost=806 size=26000 card=2000)
199999 TABLE ACCESS FULL EMP PARTITION: 1 1 (cr=2905 pr=2893 pw=0 time=1748159 us cost=806 size=26000 card=2000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 81 0.03 1.25
Disk file operations I/O 1 0.00 0.00
db file sequential read 2 0.00 0.00
--------------------------------------------------------------------------------
thats a really great 11g feature.
ReplyDeleteThe example and explanation of the proof of concept is crystal clear.
Thanks for this great detail on PCT.
ReplyDeleteIt would be great if you tell how to associate Partition Key of the Base Table with the rows of the Materialized View.
ReplyDeleteHow to create materialized view with out using group by also i dont need sum operation? why should we use group by when going for PCT?
Thank in advance!
@MANOJ BABU:
ReplyDeleteHow to create materialized view with out using group by also i dont need sum operation? why should we use group by when going for PCT? you dont need aggregations for PCT to be available
rajesh@ORA11G> create table emp
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
8 select * from scott.emp;
Table created.
Elapsed: 00:00:02.50
rajesh@ORA11G>
rajesh@ORA11G> create table dept as
2 select * from scott.dept;
Table created.
Elapsed: 00:00:00.84
rajesh@ORA11G>
rajesh@ORA11G> alter table dept
2 add constraint dept_pk
3 primary key(deptno);
Table altered.
Elapsed: 00:00:01.21
rajesh@ORA11G>
rajesh@ORA11G> alter table emp
2 add constraint emp_fk
3 foreign key(deptno)
4 references dept;
Table altered.
Elapsed: 00:00:00.52
rajesh@ORA11G>
rajesh@ORA11G> alter table emp modify deptno not null;
Table altered.
Elapsed: 00:00:00.33
rajesh@ORA11G> create materialized view emp_dept_mv
2 build immediate
3 refresh on demand
4 enable query rewrite as
5 select ename, dname
6 from emp e, dept d
7 where e.deptno = d.deptno
8 /
Materialized view created.
Elapsed: 00:00:02.52
rajesh@ORA11G> select mview_name, detail_partition_name, detail_partition_position, freshness
2 from dba_mview_detail_partition
3 where mview_name ='EMP_DEPT_MV'
4 /
MVIEW_NAME DETAIL_PARTITION_NAME DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV P1 1 FRESH
EMP_DEPT_MV P2 2 FRESH
EMP_DEPT_MV P3 3 FRESH
EMP_DEPT_MV PMAX 4 FRESH
Elapsed: 00:00:00.49
rajesh@ORA11G>
For more details on PCT, i would request you to look at this link
http://docs.oracle.com/cd/E11882_01/server.112/e25554/advmv.htm#i1006649