We use list-based table partitioning in conjunction with FGAC (under Oracle 9i, moving to 10g this summer). The high-volume tables have one partition per customer, to take advantage of partition elimination in queries, plus speed of deleting customer data (using truncate or drop partition, depending on the need).
We use materialized views to speed up performance against this data warehouse. However, if we truncate partitions on the base tables, we are compelled to do complete refreshes, rather than fast refreshes, as the example below shows. However, these partitions in the base table can have millions (and tens of millions) of rows each, so using DELETE (as required by fast refresh) isn't a good option.
Is there a way to do a partition-wise refresh on a materialized view (especially when the partitioning key matches that of the base table)?
And, by the way, what exactly does the parallelism parameter on the dbms_mview.refresh procedure do? It doesn't seem to control the parallelism of the refresh itself. Perhaps something to do with replication?
CREATE SEQUENCE myobjects_seq;
DROP TABLE Myobjects;
CREATE TABLE myobjects
PARTITION BY LIST (customer_id)
(PARTITION cust1 VALUES (1),
PARTITION cust2 VALUES (2),
PARTITION cust3 VALUES (3) )
AS
SELECT 1 AS customer_id, all_objects.*
FROM all_objects;
INSERT INTO myobjects SELECT 2, all_objects.* FROM All_objects;
INSERT INTO myobjects SELECT 3, all_objects.* FROM All_objects;
ALTER TABLE myobjects ADD CONSTRAINT xpk_myobjects PRIMARY KEY(customer_id, object_id);
ALTER TABLE myobjects PARALLEL;
DROP MATERIALIZED VIEW LOG ON myobjects;
CREATE MATERIALIZED VIEW LOG ON myobjects
PARALLEL
WITH ROWID, SEQUENCE
(customer_id, object_type, object_id)
INCLUDING NEW VALUES;
DROP MATERIALIZED VIEW mv_obj_summary;
CREATE MATERIALIZED VIEW mv_obj_summary
PARTITION BY LIST(customer_id)
(PARTITION cust1 VALUES (1),
PARTITION cust2 VALUES (2),
PARTITION cust3 VALUES (3) )
PARALLEL
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT customer_id, object_type, count(*), sum(object_id)
FROM myobjects
GROUP BY customer_Id, object_type;
SQL> select * from mv_obj_summary;
CUSTOMER_ID OBJECT_TYPE COUNT(*) SUM(OBJECT_ID)
----------- ------------------ ---------- --------------
1 VIEW 1363 22416119
1 INDEX 78 3152182
1 TABLE 103 3532358
1 LIBRARY 14 322473
1 PACKAGE 389 7301317
1 SYNONYM 12596 205192775
1 FUNCTION 104 2009960
1 SEQUENCE 19 663960
1 INDEXTYPE 8 255224
1 JAVA CLASS 10195 171335493
1 LOB PARTITION 5 208142
1 CONSUMER GROUP 2 7690
1 EVALUATION CONTEXT 1 5847
1 LOB 2 80631
1 TYPE 673 24329174
1 CONTEXT 20 729500
1 OPERATOR 29 967878
1 DIRECTORY 1 29618
1 PROCEDURE 24 235260
1 PACKAGE BODY 21 852159
1 JAVA RESOURCE 197 4510542
1 INDEX PARTITION 385 16004693
1 TABLE PARTITION 118 4929434
3 LOB 2 80631
3 TYPE 673 24329174
3 INDEX 78 3152182
3 CONTEXT 20 729500
3 PACKAGE 389 7301317
3 SYNONYM 12596 205192775
3 FUNCTION 104 2009960
3 OPERATOR 29 967878
3 PROCEDURE 24 235260
3 TABLE PARTITION 118 4929434
3 EVALUATION CONTEXT 1 5847
3 VIEW 1363 22416119
3 TABLE 103 3532358
3 LIBRARY 14 322473
3 SEQUENCE 19 663960
3 DIRECTORY 1 29618
3 INDEXTYPE 8 255224
3 JAVA CLASS 10195 171335493
3 PACKAGE BODY 21 852159
3 JAVA RESOURCE 197 4510542
3 LOB PARTITION 5 208142
3 CONSUMER GROUP 2 7690
3 INDEX PARTITION 385 16004693
2 VIEW 1363 22416119
2 TABLE 103 3532358
2 CONTEXT 20 729500
2 LIBRARY 14 322473
2 PACKAGE 389 7301317
2 SYNONYM 12596 205192775
2 JAVA CLASS 10195 171335493
2 JAVA RESOURCE 197 4510542
2 CONSUMER GROUP 2 7690
2 LOB 2 80631
2 TYPE 673 24329174
2 INDEX 78 3152182
2 FUNCTION 104 2009960
2 OPERATOR 29 967878
2 SEQUENCE 19 663960
2 DIRECTORY 1 29618
2 INDEXTYPE 8 255224
2 PROCEDURE 24 235260
2 PACKAGE BODY 21 852159
2 LOB PARTITION 5 208142
2 INDEX PARTITION 385 16004693
2 TABLE PARTITION 118 4929434
2 EVALUATION CONTEXT 1 5847
alter table myobjects truncate partition cust2;
-- as expected, mv still has old data; it was refresh on demand
SQL> select * from mv_obj_summary;
CUSTOMER_ID OBJECT_TYPE COUNT(*) SUM(OBJECT_ID)
----------- ------------------ ---------- --------------
3 LOB 2 80631
3 TYPE 673 24329174
3 INDEX 78 3152182
3 CONTEXT 20 729500
3 PACKAGE 389 7301317
3 SYNONYM 12596 205192775
3 FUNCTION 104 2009960
3 OPERATOR 29 967878
3 PROCEDURE 24 235260
3 TABLE PARTITION 118 4929434
3 EVALUATION CONTEXT 1 5847
3 VIEW 1363 22416119
3 TABLE 103 3532358
3 LIBRARY 14 322473
3 SEQUENCE 19 663960
3 DIRECTORY 1 29618
3 INDEXTYPE 8 255224
3 JAVA CLASS 10195 171335493
3 PACKAGE BODY 21 852159
3 JAVA RESOURCE 197 4510542
3 LOB PARTITION 5 208142
3 CONSUMER GROUP 2 7690
3 INDEX PARTITION 385 16004693
1 VIEW 1363 22416119
1 INDEX 78 3152182
1 TABLE 103 3532358
1 LIBRARY 14 322473
1 PACKAGE 389 7301317
1 SYNONYM 12596 205192775
1 FUNCTION 104 2009960
1 SEQUENCE 19 663960
1 INDEXTYPE 8 255224
1 JAVA CLASS 10195 171335493
1 LOB PARTITION 5 208142
1 CONSUMER GROUP 2 7690
1 EVALUATION CONTEXT 1 5847
1 LOB 2 80631
1 TYPE 673 24329174
1 CONTEXT 20 729500
1 OPERATOR 29 967878
1 DIRECTORY 1 29618
1 PROCEDURE 24 235260
1 PACKAGE BODY 21 852159
1 JAVA RESOURCE 197 4510542
1 INDEX PARTITION 385 16004693
1 TABLE PARTITION 118 4929434
2 VIEW 1363 22416119
2 TABLE 103 3532358
2 CONTEXT 20 729500
2 LIBRARY 14 322473
2 PACKAGE 389 7301317
2 SYNONYM 12596 205192775
2 JAVA CLASS 10195 171335493
2 JAVA RESOURCE 197 4510542
2 CONSUMER GROUP 2 7690
2 LOB 2 80631
2 TYPE 673 24329174
2 INDEX 78 3152182
2 FUNCTION 104 2009960
2 OPERATOR 29 967878
2 SEQUENCE 19 663960
2 DIRECTORY 1 29618
2 INDEXTYPE 8 255224
2 PROCEDURE 24 235260
2 PACKAGE BODY 21 852159
2 LOB PARTITION 5 208142
2 INDEX PARTITION 385 16004693
2 TABLE PARTITION 118 4929434
2 EVALUATION CONTEXT 1 5847
69 rows selected.
-- complete is fine
exec dbms_mview.refresh('MV_OBJ_SUMMARY','C');
SQL> select * from mv_obj_summary;
CUSTOMER_ID OBJECT_TYPE COUNT(*) SUM(OBJECT_ID)
----------- ------------------ ---------- --------------
1 LOB 2 80631
1 TYPE 673 24329174
1 CONTEXT 20 729500
1 OPERATOR 29 967878
1 DIRECTORY 1 29618
1 PROCEDURE 24 235260
1 PACKAGE BODY 21 852159
1 JAVA RESOURCE 197 4510542
1 INDEX PARTITION 385 16004693
1 TABLE PARTITION 118 4929434
1 VIEW 1363 22416119
1 INDEX 78 3152182
1 TABLE 103 3532358
1 LIBRARY 14 322473
1 PACKAGE 389 7301317
1 SYNONYM 12596 205192775
1 FUNCTION 104 2009960
1 SEQUENCE 19 663960
1 INDEXTYPE 8 255224
1 JAVA CLASS 10195 171335493
1 LOB PARTITION 5 208142
1 CONSUMER GROUP 2 7690
1 EVALUATION CONTEXT 1 5847
3 VIEW 1363 22416119
3 TABLE 103 3532358
3 LIBRARY 14 322473
3 SEQUENCE 19 663960
3 DIRECTORY 1 29618
3 INDEXTYPE 8 255224
3 JAVA CLASS 10195 171335493
3 PACKAGE BODY 21 852159
3 JAVA RESOURCE 197 4510542
3 LOB PARTITION 5 208142
3 CONSUMER GROUP 2 7690
3 INDEX PARTITION 385 16004693
3 LOB 2 80631
3 TYPE 673 24329174
3 INDEX 78 3152182
3 CONTEXT 20 729500
3 PACKAGE 389 7301317
3 SYNONYM 12596 205192775
3 FUNCTION 104 2009960
3 OPERATOR 29 967878
3 PROCEDURE 24 235260
3 TABLE PARTITION 118 4929434
3 EVALUATION CONTEXT 1 5847
46 rows selected.
-- fast doesn't work
alter table myobjects truncate partition cust3;
exec dbms_mview.refresh('MV_OBJ_SUMMARY','F');
SQL> exec dbms_mview.refresh('MV_OBJ_SUMMARY','F');
BEGIN dbms_mview.refresh('MV_OBJ_SUMMARY','F'); END;
*
ERROR at line 1:
ORA-32313: REFRESH FAST of "DB"."MV_OBJ_SUMMARY" unsupported after PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1
parallelism is for updateable materialized view refreshes...
In 9i:
http://docs.oracle.com/cd/B10501_01/server.920/a96520/mv.htm#45519 you have to have range or composite (implies range)
In 10g, you can you range OR list (or composite)
http://docs.oracle.com/cd/B19306_01/server.102/b14223/advmv.htm#sthref575 So, in 9i, if you switch to range instead of list:
ops$tkyte%ORA9IR2> CREATE TABLE myobjects
2 PARTITION BY range (customer_id)
3 (PARTITION cust1 VALUES less than (2),
4 PARTITION cust2 VALUES less than (3),
5 PARTITION cust3 VALUES less than (4) )
6 AS
7 SELECT 1 AS customer_id, all_objects.*
8 FROM all_objects where rownum = 1;
Table created.
ops$tkyte%ORA9IR2> INSERT INTO myobjects SELECT 2, all_objects.* FROM All_objects where rownum = 1;
1 row created.
ops$tkyte%ORA9IR2> INSERT INTO myobjects SELECT 3, all_objects.* FROM All_objects where rownum = 1;
1 row created.
ops$tkyte%ORA9IR2> ALTER TABLE myobjects ADD CONSTRAINT xpk_myobjects PRIMARY KEY(customer_id, object_id);
Table altered.
ops$tkyte%ORA9IR2> ALTER TABLE myobjects PARALLEL;
Table altered.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> CREATE MATERIALIZED VIEW LOG ON myobjects
2 PARALLEL
3 WITH ROWID, SEQUENCE
4 (customer_id, object_type, object_id)
5 INCLUDING NEW VALUES;
Materialized view log created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> CREATE MATERIALIZED VIEW mv_obj_summary
2 PARTITION BY range(customer_id)
3 (PARTITION cust1 VALUES less than (2),
4 PARTITION cust2 VALUES less than (3),
5 PARTITION cust3 VALUES less than (4) )
6 PARALLEL
7 BUILD IMMEDIATE
8 REFRESH FAST ON DEMAND
9 ENABLE QUERY REWRITE
10 AS
11 SELECT customer_id, object_type, count(*), sum(object_id)
12 FROM myobjects
13 GROUP BY customer_Id, object_type;
Materialized view created.
ops$tkyte%ORA9IR2> alter table myobjects truncate partition cust3;
Table truncated.
ops$tkyte%ORA9IR2> exec dbms_mview.refresh('MV_OBJ_SUMMARY','F');
PL/SQL procedure successfully completed.