0 Replies Latest reply: Oct 22, 2009 12:23 PM by Donbot RSS

    Refreshing vs Creating Materialized Views

    Donbot
      I created a materialized view (which I will call THE_MATERIALIZED_VIEW) with 47 million records (and with NOLOGGING, BUILD IMMEDIATE, REFRESH COMPLETE WITH PRIMARY KEY), and it took 19 minutes to complete.

      However, when I executed DBMS_MVIEW.REFRESH('THE_MATERIALIZED_VIEW', 'C'), it took over an hour.

      Is there something obvious that I am missing that would speed this refresh up?
      (Besides trying a "fast refresh" - the materialzed view is meant to be refreshed only after the underlying tables are truncated and rebuilt.)

      If it's a case of "it would help if we saw the query", let me know - I didn't include it at first as it's long.