This discussion is archived
1 Reply Latest reply: Mar 12, 2012 10:45 PM by NikolayIvankin RSS

refresh of materialized views slow

846220 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points