1 Reply Latest reply: Mar 13, 2012 12:45 AM by NikolayIvankin RSS

    refresh of materialized views slow

    846220
      Hi,

      This relates to a datawarehouse, used for a data migration (Oracle 11g).

      To refresh a set of materialized views I am using DBMS_MVIEW.REFRESH.

      However, it seems that refreshing the views is much slower( about 4 to 5 times slower) than dropping and re-creating them.

      Any thoughts on this?

      To speed up the refresh method, I truncate beforehand and this seems to help the peformance, but not by much.

      So, basically when I run:

      TRUNCATE TABLE MY_MVIEW;
      EXECUTE DBMS_MVIEW.REFRESH ('MY_MVIEW','C',PARALLELISM=>8,ATOMIC_REFRESH=>false);

      this is still about 3 times slower then;

      DROP MATERIALIZED VIEW MY_MVIEW;
      CREATE MATERIALIZED VIEW "MY_MVIEW"
      PARALLEL 8
      BUILD IMMEDIATE
      USING INDEX
      REFRESH FORCE ON DEMAND
      ENABLE QUERY REWRITE
      AS SELECT * FROM MY_TAB;


      Cheers.
        • 1. Re: refresh of materialized views slow
          NikolayIvankin
          843217 wrote:
          So, basically when I run:

          TRUNCATE TABLE MY_MVIEW;
          EXECUTE DBMS_MVIEW.REFRESH ('MY_MVIEW','C',PARALLELISM=>8,ATOMIC_REFRESH=>false);

          this is still about 3 times slower then;

          DROP MATERIALIZED VIEW MY_MVIEW;
          CREATE MATERIALIZED VIEW "MY_MVIEW"
          PARALLEL 8
          BUILD IMMEDIATE
          USING INDEX
          REFRESH FORCE ON DEMAND
          ENABLE QUERY REWRITE
          AS SELECT * FROM MY_TAB;
          You use USING INDEX clause, that's why it is slower.

          But why don't use a fast refresh?
          your query quite simple and it allows to use fast refresh, not complete. Create MView log for MY_TAB and refresh only new/modified/deleted records.