Materialized view refresh speed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Materialized view refresh speed

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    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!

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What's your oracle release?

    If it's 9i, then do not set "heap_size" parameter.

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Will it not need a materialized view log for table TBL_SONG?
    ---------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width