0 Replies Latest reply: Jan 18, 2013 5:48 AM by 856282 RSS

    Materialized view fast refresh on commit:performance

    856282
      I ask information about how run the refresh materialized views to type fast on commit.


      I have a DB Spatial composed of 25 regional themes that presents many common columns, which often must be interrogated by region and theme.
      I created a table with a spatial column geometric t_element_common_det with range partition (id_themes_fk, id_region_fk) with PK Id_seqn.
      Each themes then presents specific attributes, really few columns.I created a table for each thematic t_themes_n with (WITH n from 1 to 25) with PK Id_seqn,
      t_themes_n.Id_seqn with FK to t_element_common_det.id_seqn.

      Now to get the complete alphanumeric data of each themes I created a join-only MV for each theme,
      partitioned by region with fast refresh on commit.
      Each MV is created as follows:

      CREATE MATERIALIZED VIEW MV_themes_14
      partition by range (id_region_fk)
      (
      ..
      )
      BUILD IMMEDIATE
      REFRESH FAST ON COMMIT with rowid
      AS
      SELECT e.column_1,
             e.column_2,
             ....
             t.column_1,
             t.column_2,
             t.rowid rowid_theme,
             e.rowid rowid_ele
        FROM T_themes_14 T,
      T_element_common_det E
      WHERE T.ID_SEQN = E.ID_SEQN
        AND E.ID_themes_FK = 14 -- id_themes_fk

      Now the problem is in performance.In select there is no problem, but I have problems in insert or Update.
      For example, if I update a record on t_themes_14 okay (time of 3 of seconds), while if the upgrade t_element_common_det for a record with
      id_themes_fk = 14 I have performance problems because if I update / delete a record it takes between 60/90 seconds.
      I analized the trace file I changed only a recordupdate a record on t_themes_14 Oracle upgrade on all MV_themes_n (I'm 25)
      Performs tasks such as:

      / * MV_REFRESH (DEL) * /
      DELETE FROM "REGISTER". "MV_themes_8" SNA $
       WHERE "ROWID_ELE" IN
             (SELECT / * + NO_MERGE HASH_SJ * /
               *
                FROM (SELECT CHARTOROWID ("MAS $". "M_ROW $ $") $ RID
                        FROM "REGISTER". "MLOG $ _t_element_common_det" "MAS $"
                       WHERE "MAS $". XID = $ $: 1) $ MAS)

      / * MV_REFRESH (INS) * /
      INSERT INTO "REGISTER". "MV_themes_8"
        SELECT / * + NO_MERGE ("JV $") * /
         "JV $". "ID_SEQN"
         "JV $". "ID_themes_FK"
         .......
         "MAS $ 1". ROWID,
         "JV $". "$ RID"
          FROM (SELECT "MAS $". "ROWID" "RID $", "MAS $". *
                  FROM "REGISTER". "t_element_common_det" "MAS $"
                 WHERE ROWID IN (SELECT / * + HASH_SJ * /
                                  CHARTOROWID ("MAS $". "M_ROW $ $") $ RID
                                   FROM "REGISTER". "MLOG $ _t_element_common_det" "MAS $"
                                  WHERE "MAS $". XID $ $ =: 1)) "JV $"
               "T_themes_8" AS SNAPSHOT OF (: B_SCN) "MAS $ 1"
         WHERE "MAS $ 1". "ID_SEQN" = "JV $". "ID_SEQN"
           AND "JV $". "ID_ELEMENTO_FK" = 8


      Now my question is: exist a better way to set to refresh the materialized views regarding t_element_common_det, if you upadte a record
      the t_element_common_det with ID_themes_FK = 14 to refresh only on the MV_themes_14 and not all Materialized view?
      Any other suggestions are welcome.

      Thanks.