1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Creation of materialized view is very slow

Discussion in 'Server Administration and Options' started by dimpleboy, Aug 15, 2011.

  1. dimpleboy

    dimpleboy Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Makati, Philippines
    Hi guys, I am creating a materialized view with the following query:

    CREATE MATERIALIZED VIEW MVIEW_NAME TABLESPACE MVIEW_TS REFRESH FAST WITH ROWID ON DEMAND
    AS SELECT * FROM TABLE_NAME@DB_LINK;

    the master table had 3million rows, and my problem is upon executing this query it already eaten 2 days and still it is not yet finish.

    Is there any work around I can do to speed up the creation of the materialized view.


    THANK YOU SO MUCH!
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    There are multiple reasons why your Materialized view Query may be slow...

    First there is the DB_Link, second there is the materialized view select statement. You should have ideally run the Explain Plan to analyze the path oracle optimizer will take to run the query before running the query itself.

    Do this,

    Code (SQL):
    EXPLAIN PLAN FOR
    SELECT * FROM TABLE_NAME@DB_LINK;
    Next,

    Code (SQL):
    SET LINESIZE 130
    SET PAGESIZE 0
    SELECT *
    FROM   TABLE(DBMS_XPLAN.DISPLAY);
    That will give an idea what, if anything, is wrong.
     
  3. dimpleboy

    dimpleboy Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Makati, Philippines
    thanks sadik, ill run this as soon as I go to the office tomorrow, thank you so much.