Materialized views are partitioned for
1) PCT Refresh, there by improving refresh performance.
2) Partition pruning: only relevant aggregated groups are accessed, there by reducing query processing cost.
rajesh@ORA11GR2>
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:29.65
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
2 add constraint emp_pk primary key(empno);
Table altered.
Elapsed: 00:00:14.29
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:16.15
rajesh@ORA11GR2>
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> 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:07.79
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno not null;
Table altered.
Elapsed: 00:00:00.07
rajesh@ORA11GR2>
1) PCT Refresh, there by improving refresh performance.
2) Partition pruning: only relevant aggregated groups are accessed, there by reducing query processing cost.
rajesh@ORA11GR2>
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:29.65
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
2 add constraint emp_pk primary key(empno);
Table altered.
Elapsed: 00:00:14.29
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:16.15
rajesh@ORA11GR2>
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> 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:07.79
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno not null;
Table altered.
Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> create materialized view emp_dept_mv
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 build immediate
12 refresh on demand
13 enable query rewrite
14 as
15 select e.deptno,count(*)
16 from dept d,
17 emp e
18 where e.deptno = d.deptno
19 group by e.deptno;
Materialized view created.
Elapsed: 00:00:07.56
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'EMP_DEPT_MV');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*)
2 from emp
3 where deptno =1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1215826357
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 6 | | | | |
| 2 | PARTITION LIST SINGLE | | 1 | 6 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT_MV | 1 | 6 | 3 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
The query rewrite happens as expected. Now we will make the partition p1 to Stale by deleting some data's from the detailed table.
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> delete from emp
2 where deptno = 1
3 and rownum = 1;
1 row deleted.
Elapsed: 00:00:00.03
rajesh@ORA11GR2> commit;
Commit complete.
Elapsed: 00:00:00.09
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.00
rajesh@ORA11GR2>
Refreshing the (partitioned) materialized view, will make its stale segments to Fresh.
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.01
rajesh@ORA11GR2> set echo off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_DEPT_MV');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.57
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.
But what happened behind the scene is, Oracle did a delete on Mview and re-inserted the new data's from detail table.
So what happens if the Stale partition in Mview has many records? then we will spend more time in deleting the data from stale partition.
SQL ID: 88sv43hhjtc0y
Plan Hash: 1443979106
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 4 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 4 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 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=0 us cost=3 size=3 card=1)
1 MAT_VIEW ACCESS FULL EMP_DEPT_MV PARTITION: 1 1 (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.10 1.25 2894 2906 5 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.10 1.25 2894 2906 5 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=2906 pr=2894 pw=0 time=0 us)
1 SORT GROUP BY NOSORT (cr=2905 pr=2894 pw=0 time=0 us cost=806 size=26000 card=2000)
199999 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2905 pr=2894 pw=0 time=3421974 us cost=806 size=26000 card=2000)
199999 TABLE ACCESS FULL EMP PARTITION: 1 1 (cr=2905 pr=2894 pw=0 time=2845781 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 82 0.03 1.15
--------------------------------------------------------------------------------
So the right way to refresh (partitioned) Mview is to go nonatomic (atomic=>false), So when you do this Oracle will Truncate the partitions rather than deleting it.
rajesh@ORA11GR2> delete from emp
2 where deptno = 1
3 and rownum = 1;
1 row deleted.
Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
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 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.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> @trace.sql;
rajesh@ORA11GR2> alter session set timed_statistics=true;
Session altered.
Elapsed: 00:00:00.01
rajesh@ORA11GR2> alter session set events '10046
2 trace name context forever, level 12';
Session altered.
Elapsed: 00:00:00.04
rajesh@ORA11GR2> set echo off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_DEPT_MV',atomic_refresh=>false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.85
rajesh@ORA11GR2>
Tkprof shows me this.
SQL ID: a7rykky0gvrq9
Plan Hash: 0
LOCK TABLE "RAJESH"."EMP_DEPT_MV" PARTITION ("P1") IN EXCLUSIVE MODE NOWAIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 (recursive depth: 2)
--------------------------------------------------------------------------------
SQL ID: c3yz7q6n5d2h0
Plan Hash: 1999525523
ALTER TABLE "RAJESH"."EMP_DEPT_MV" TRUNCATE PARTITION P1 UPDATE GLOBAL
INDEXES
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 3 1 0
Execute 1 0.04 0.21 4 0 38 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.04 0.24 4 3 39 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 2 0.03 0.05
reliable message 2 0.00 0.00
enq: RO - fast object reuse 2 0.00 0.00
db file sequential read 4 0.02 0.02
local write wait 3 0.00 0.00
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL ID: 80rm69rmu73s5
Plan Hash: 1312300534
insert /*+ RELATIONAL("EMP_DEPT_MV") PARALLEL("EMP_DEPT_MV",1) APPEND
NESTED_TABLE_SET_SETID NO_REF_CASCADE */ into "RAJESH"."EMP_DEPT_MV"
partition ("P1") (select /*+ RELATIONAL("EMP_DEPT_MV")
PARALLEL("EMP_DEPT_MV",1) */ * from "RAJESH"."EMP_DEPT_MV" partition
("P1") ) delete global indexes
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.02 0 3 4 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.03 0 3 4 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD AS SELECT (cr=3 pr=0 pw=0 time=0 us)
1 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
1 MAT_VIEW ACCESS FULL EMP_DEPT_MV PARTITION: 1 1 (cr=3 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
--------------------------------------------------------------------------------
No comments:
Post a Comment