Materialized view refresh speed
I have a materialized view that has 3 context index on it. "Script provided below."
The problem i have is that it takes 45min to 75 min to refresh the materialized view.
If i drop those 3 context index, it is very fast to refresh. 3 minutes!
But I need those content indexes. And i cannot drop them, refresh, then re-create them, since query are run during the refresh process.
Also, the table wish my materialized view is based on gets truncated, then all rows are re-inserted. Then i refresh my mv base on that table.
That is why my refresh takes a long time, since all rows have changed.
But my create materialized view takes 30 seconds! Why the refresh takes 45min!
How can i speed up the refresh process?
Also, will this refresh is in process, the entire database is slow. So i absolutely have to reduce that refresh time. And the refresh can be made 5-10 times a day.
Here is my script:
CREATE MATERIALIZED VIEW MV_SONG2
CACHE
PARALLEL
NOLOGGING
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE AS
SELECT * FROM TBL_SONG;
CREATE INDEX IDX1 ON MV_SONG2
(ALB_TITLE)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('stoplist ext_gen3_stoplist sync (on commit)');
CREATE INDEX IDX2 ON MV_SONG2
(SON_ART_NAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('stoplist ext_gen3_stoplist sync (on commit)');
CREATE INDEX IDX3 ON MV_SONG2
(SON_TITLE)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('stoplist ext_gen3_stoplist sync (on commit)');
What happen is the following:
truncate table tbl_song;
insert into /*+ append */ tbl_song
SELECT BLABLA;
COMMIT;
execute DBMS_MVIEW.refresh(list => 'MV_SONG2',method => '?',rollback_seg => NULL,push_deferred_rpc => TRUE,refresh_after_errors => FALSE,purge_option => 1,parallelism => 5,heap_size => 5,atomic_refresh => TRUE,nested => FALSE);
Takes 45 minutes!
