Oracle Database Discussions

Hi,

To improve the refresh performance for a materialized view, I set up it to be refreshed in parallel. The view can be refreshed successfully, however, I did not see the view is refreshed in parallel from session browser, can someone let me know if I miss any steps?

1) In DB A (running 8 CPUs), set up the base table to be parallelized, the base table is called table1

ALTER TABLE A.table1 PARALLEL ( DEGREE Default INSTANCES Default );

2) In Database A, set up the materialized view log

CREATE MATERIALIZED VIEW LOG
ON table1 WITH primary key
INCLUDING NEW VALUES;

3) In Database B (in the same server with Database A), there is an existing table called table1, prebuilt with millions of records in the table. Due to the size of table1, I have to use prebuilt option

Drop MATERIALIZED VIEW table1;

CREATE MATERIALIZED VIEW table1 ON PREBUILT TABLE
REFRESH FAST with primary key
AS
select /*+ PARALLEL(table1, 8) */ *
from table1@A;

4) in Database B, I executed this stored procedure -

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'table1',PARALLELISM=>8, METHOD=>'F');


Thanks in advance!

Liz