9 Replies Latest reply on Jul 3, 2020 2:27 AM by L. Fernigrini

    MV replication

    epipko

      Oracle 19.3 on Win2019

      I have about 200 tables replicated to reporting database every 5 min.

      I'd like to keep replication data at no longer than 3 years. How would I approach something like this?

        • 1. Re: MV replication
          John Thorton

          epipko wrote:

           

          Oracle 19.3 on Win2019

          I have about 200 tables replicated to reporting database every 5 min.

          I'd like to keep replication data at no longer than 3 years. How would I approach something like this?

          EVERY  table needs to have a DATE column that can be used to determine how long row has been in table. Without such a column, you can't know which rows to remove.

          • 2. Re: MV replication
            epipko

            Most, if not all tables have "created_date" columns in them. Do I simply run a delete statement to remove all, but 3 years worth of data? But the source has 10 years worth of data and next time replication runs, will it move missing rows right back in?

            • 3. Re: MV replication
              L. Fernigrini

              MV Replication means "Materialized View"?

               

              If so, you should be able to apply a filter on the view. That means you do not need to delete anything, it is automatically done when the view is refreshed.

               

              If you are not talking about Materialized Views, please explain how that replication is done.

              • 4. Re: MV replication
                JohnWatson2

                epipko wrote:

                 

                Most, if not all tables have "created_date" columns in them. Do I simply run a delete statement to remove all, but 3 years worth of data? But the source has 10 years worth of data and next time replication runs, will it move missing rows right back in?

                If you create the MVs as updatable, you can do DML against them. But you'll lose the changes on the next refresh.

                • 5. Re: MV replication
                  epipko

                  Yes, I have MV Logs on the source side and MVs on the destination side. How would I apply a filter on the view to keep 3 years of data only?

                  • 6. Re: MV replication
                    L. Fernigrini

                    CREATE MATERIALIZED VIEW ....

                    AS

                    SELECT .....

                    FROM YourTable1 t1

                         JOIN YourTable2 t2 ON ....

                    WHERE t1.DateCreated > ADD_MONTHS(TRUNC(SYSDATE),-36);

                     

                    You can use any date arithmetic to determine the limit, I prefer to have it truncated and instead of substracting days substract months... that is up to you, you can use the same logi you were planning to use on the "DELETE" but instead of deleting older rows, you just keep newer in the view.

                    • 7. Re: MV replication
                      L. Fernigrini

                      Here is a VERY basic example:

                       

                      CREATE TABLE t1 (ID Number, DateCreated DATE);

                      INSERT INTO t1 VALUES (1, SYSDATE);

                      INSERT INTO t1 VALUES (2, SYSDATE - 10);

                      INSERT INTO t1 VALUES (3, SYSDATE - 20);

                      --

                      CREATE MATERIALIZED VIEW v1

                      AS

                      SELECT * FROM t1

                      WHERE DateCreated > TRUNC(SYSDATE) - 15;

                      --

                      SELECT * FROM v1;

                      --

                      INSERT INTO t1 VALUES (4, SYSDATE - 12);

                      INSERT INTO t1 VALUES (5, SYSDATE - 22);

                      --

                      UPDATE t1 SET DateCreated = SYSDATE - 30 WHERE ID = 2;

                      --

                      BEGIN

                          DBMS_MVIEW.REFRESH('v1');

                      END;

                      /

                      --

                      SELECT * FROM v1;

                      --

                      DROP TABLE t1 PURGE;

                      DROP MATERIALIZED VIEW v1;

                       

                       

                      Output:

                       

                      • 8. Re: MV replication
                        JohnWatson2

                        Problem is that your view is not fast refreshable:

                         

                        orclz> CREATE MATERIALIZED VIEW v1 refresh fast
                          2  AS
                          3  SELECT * FROM t1
                          4  WHERE DateCreated > TRUNC(SYSDATE) - 15;
                        WHERE DateCreated > TRUNC(SYSDATE) - 15
                                                  *
                        ERROR at line 4:
                        ORA-12015: cannot create a fast refresh materialized view from a complex query
                        

                         

                        It may be that there is no solution to this using the supplied declarative techniques. Rather, it will be necessary to design a programmatic solution. Probably based on triggers and advanced queues and Scheduler jobs.

                        • 9. Re: MV replication
                          L. Fernigrini

                          Yes, you are right, I did not saw / paid attention to the 5 minutes refresh!!!! I was thinking in something more static.

                           

                          Your suggestion looks OK.

                           

                           

                          Maybe creating a set of MV for each table, with specific date ranges, and a view on top of it (that does union all from all MV):

                           

                          Sales2017Q3

                          Sales2017Q4

                          ...

                          Sales2020Q3 (new data will "go" here)

                          Sales2020Q4 (empty)

                           

                           

                          When 2020Q3 ends, create a new empty Sales2021Q1 materialized view, recreate the view to select from Sales2017Q4 to Sales2020Q4, and drop Sales2017Q3, leaving the solution like this:

                           

                          Sales2017Q4

                          Sales2018Q1

                          ...

                          Sales2020Q4 (new data will "go" here)

                          Sales2021Q1 (empty)

                           

                           

                          I have used this approach in the past (poor man`s partitioning) not with MVs, but I not with MV... but may work, requires some programming to handle the "switchs" between months/quarters/years.

                           

                          I made a simple exmaple with months:

                           

                           

                          CREATE TABLE t1 (ID Number NOT NULL PRIMARY KEY , DateCreated DATE);

                          --

                          CREATE MATERIALIZED VIEW LOG ON t1  WITH PRIMARY KEY INCLUDING NEW VALUES;

                          --

                          INSERT INTO t1 VALUES (1, SYSDATE);

                          INSERT INTO t1 VALUES (2, SYSDATE - 30);

                          INSERT INTO t1 VALUES (3, SYSDATE - 60);

                          --

                          CREATE MATERIALIZED VIEW v202007 REFRESH FAST

                          AS

                          SELECT * FROM t1

                          WHERE DateCreated >= DATE '2020-07-01'

                            AND DateCreated < DATE '2020-08-01';

                          --

                          CREATE MATERIALIZED VIEW v202006 REFRESH FAST

                          AS

                          SELECT * FROM t1

                          WHERE DateCreated >= DATE '2020-06-01'

                            AND DateCreated < DATE '2020-07-01';

                          --

                          CREATE VIEW vSales AS

                          SELECT * FROM v202006

                          UNION ALL

                          SELECT * FROM v202007;

                          --

                          SELECT * FROM vSales;

                          --

                          INSERT INTO t1 VALUES (4, SYSDATE - 10);

                          INSERT INTO t1 VALUES (5, SYSDATE - 1);

                          --

                          UPDATE t1 SET DateCreated = SYSDATE - 50 WHERE ID = 2;

                          --

                          COMMIT;

                          --

                          BEGIN

                              DBMS_MVIEW.REFRESH('v202006');

                              DBMS_MVIEW.REFRESH('v202007');

                          END;

                          /

                          SELECT * FROM vSales;

                          --

                          DROP TABLE t1 PURGE;

                          DROP MATERIALIZED VIEW v202006;

                          DROP MATERIALIZED VIEW v202007;

                          DROP  VIEW vSales;