1 2 Previous Next 18 Replies Latest reply: Dec 4, 2012 3:30 PM by Mark Malakanov (user11181920) RSS

    Materialized View - Refresh Fast on Complex SQL

    VijayaraghavanKrishnan
      Hi All,

      I am tuning some of the Data Warehousing Batch Jobs, in which some of the batch jobs are not running due to temp tablespace running of out of space. The reason because base tables hold 10 years of data and some of the table holds 60M records.The batch jobs need 1 Week of records, So I am thinking to create Materialized View on the base tables to hold 6 Months of record so the performance will be improved.

      To be frank enough last two days I went through the Materialized View concepts on docs and tried some of the options.

      For example: I have a base table as below
      Test
      ------
      SNo Number(8)
      Create_Ts TIMESTAMP(6)

      When I create the materialized view as below it is allowing me to do the fast refresh on commit

      CREATE MATERIALIZED VIEW TEST_MV REFRESH FAST ON COMMIT AS
      SELECT * FROM TEST WHERE CREATE_TS > '01-JAN-12'

      but the below one is not allowing me since oracle consider this as a complex
      CREATE MATERIALIZED VIEW TEST_MV REFRESH FAST ON COMMIT AS
      SELECT * FROM TEST WHERE CREATE_TS > SYSDATE - 365;

      I created it as Force Refresh allowing oracle refresh it ever 30 Mintues. On every 30 Mins it is doing a complete refresh. Is there a way can we have the date dynamically and do the Fast Refresh On Commit.

      Oracle Version: 10g R2
      OS: Linux

      Thanks,
      Vijay
        • 1. Re: Materialized View - Refresh Fast on Complex SQL
          JustinCave
          You aren't going to be able to do an incremental refresh on a materialized view that contains a non-deterministic function like SYSDATE (see the General Restrictions on Fast Refresh in the Data Warehousing Guide).

          Are you licensed to use partitioning? Is the table partitioned? If so, is it partitioned by CREATE_TS?

          Justin
          • 2. Re: Materialized View - Refresh Fast on Complex SQL
            VijayaraghavanKrishnan
            Hi Justin,

            We are licensed for partitioning. The table is Partitioned by a Client ID column where all the OLTP transactions run against the Client ID. Only the Data Warehousing Extract Jobs are running against the CREATE_TS, The partition on CREATE_TS will not help out and it will affect the OLTP.

            Thanks,
            Vijay
            • 3. Re: Materialized View - Refresh Fast on Complex SQL
              JustinCave
              Do the batch jobs do some aggregation on the data? Or do they need the detail-level data? Could you create a materialized view that preaggreages the data for each client for each time period (e.g. for each day) and use that instead?

              Justin
              • 4. Re: Materialized View - Refresh Fast on Complex SQL
                VijayaraghavanKrishnan
                Hi Justin,

                The job needs detail level data, we don't have any aggregation on the query or in batch job. We can create a materialized view on basis of client but how do I limit the number of records, the only way we can restrict is using the date.

                Thanks,
                Vijay
                • 5. Re: Materialized View - Refresh Fast on Complex SQL
                  JustinCave
                  Then I'm not sure that I see how a materialized view can help you. You can't have an incrementally refreshed materialized view that depends on a non-deterministic function like SYSDATE. You don't want to pre-aggregate the data so building the materialized view would involve doing a full refresh every time using, essentially, the query that the batch jobs are already using. And the table is designed to make that sort of query particularly inefficient since it needs to hit every partition.

                  Is there an index on the CREATE_TS? Is that a local or a global index? How many CLIENT_ID partitions are there? Is the query that the batch jobs execute using parallel query?

                  Justin
                  • 6. Re: Materialized View - Refresh Fast on Complex SQL
                    VijayaraghavanKrishnan
                    Is there an index on the CREATE_TS? Is that a local or a global index? How many CLIENT_ID partitions are there? Is the query that the batch jobs execute using parallel query?
                    There is a global index on the CREATE_TS column, it is timestamp(6) datatype. The CLIENT_ID is hash Partitioned and has 60 Partitions.

                    The batch job queries are not running in Parallel.

                    The main reason we want to go for Materialized View is to limit the number of rows so that the query will perform better with smaller subset of rows.


                    Thanks,
                    Vijay
                    • 7. Re: Materialized View - Refresh Fast on Complex SQL
                      JustinCave
                      Is there any benefit, though, if you have to do a full refresh of the materialized view each time? That's going to take as long as it currently takes for the queries the batch job executes to extract the data it needs to process. Unless you can refresh the materialized view in advance (e.g. there is a window of time where the detail data is static where you could build the materialized view where you couldn't have just started the batch job-- because the batch job is waiting for some other input for example) I don't see a potential for improving the performance of the batch job.

                      Could the batch job's queries run in parallel (so that each session uses a larger fraction of the database's available resources)? If there are idle resources on the server, that could make your jobs run faster. But it may decrease the performance of other things that are running at the same time.

                      Justin
                      • 8. Re: Materialized View - Refresh Fast on Complex SQL
                        Mark Malakanov (user11181920)
                        The main reason we want to go for Materialized View is to limit the number of rows so that the query will perform better with smaller subset of rows.
                        Index on CREATE_TS already "limits" number of rows to read.
                        Does your query uses this index?

                        MV will just repeat whatever the query does and also it will consume space for result.

                        If the performance of the query is concern, show us the query and its plan.
                        • 9. Re: Materialized View - Refresh Fast on Complex SQL
                          VijayaraghavanKrishnan
                          HI Justin,

                          I am planning to create the Materialized View for six months with hard coded date which will bring my table row count from 60M to 60K (We do have 8 tables that do have 60M records which I don't need it), So the batch jobs will run without any issues.
                          CREATE MATERIALIZED VIEW TEST_MV REFRESH FAST ON COMMIT AS
                          SELECT * FROM TEST WHERE CREATE_TS >= '01-JAN-12'
                          Every six months or one year Planning to create a DBMS job to drop and recreate the materialized View.

                          Thanks,
                          Vijay
                          • 10. Re: Materialized View - Refresh Fast on Complex SQL
                            VijayaraghavanKrishnan
                            Hi,

                            This is one of the query that run against the Test Server and the plan for it.
                            SELECT   filter.client_id_k, filter.extrn_system_id, tableobj.set_id,
                                     tableobj.wage_typ_cd_code_k, tableobj.monthly_avg_amt,
                                     tableobj.tcs_monthly_avg_amt, tableobj.hrly_rate,
                                     tableobj.non_provider_nam, tableobj.monthly_hrs,
                                     tableobj.unemp_justification_cd_code_k, tableobj.start_dte,
                                     tableobj.empmt_end_dte, tableobj.amt_begin_dte, tableobj.amt_end_dte,
                                     tableobj.delete_code, tableobj.ver_obj_list AS ver_code,
                                     tableobj.ver_table, tableobj.trigger_table_nam,
                                     TO_CHAR (tableobj.last_updt_ts, 'yyyymmddhh24missffffff') ts
                                FROM (SELECT t1.client_id_k, t1.set_id, t1.wage_typ_cd_code_k,
                                             NULL monthly_avg_amt, NULL tcs_monthly_avg_amt,
                                             NULL hrly_rate, NULL non_provider_nam, NULL monthly_hrs,
                                             NULL unemp_justification_cd_code_k, NULL start_dte,
                                             NULL empmt_end_dte, NULL amt_begin_dte, NULL amt_end_dte,
                                             t1.ver_id_k, 'CLIENT_INC_EARNINGS' ver_table,
                                             DECODE (t1.exists_flg, 'N', 'AE', NULL) delete_code,
                                             'CLIENT_INC_EARNINGS' trigger_table_nam, t1.last_updt_ts, v1.ver_obj_list
                                        FROM ver v1, client_inc_earnings t1
                                       WHERE t1.ver_id_k = v1.ID
                                         AND v1.exists_flg = 'Y'
                                         AND v1.verified_flg = 'Y'
                                         AND t1.set_id IN (SELECT DISTINCT set_id
                                                                      FROM client_inc_earnings
                                                                     WHERE exists_flg = 'Y')
                                         AND t1.last_updt_ts >= TO_DATE ('2012-01-01', 'YYYY-MM-DD')
                                         AND t1.last_updt_ts < TO_DATE ('2012-12-31', 'YYYY-MM-DD')
                                      UNION ALL
                                      SELECT   r1.client_id_k, NULL set_id, NULL wage_typ_cd_code_k,
                                                 t1.ff_monthly_avg_amt
                                               + t1.fs_monthly_avg_amt AS monthly_avg_amt,
                                               t1.tcs_monthly_avg_amt, NULL hrly_rate,
                                               NULL non_provider_nam, NULL monthly_hrs,
                                               NULL unemp_justification_cd_code_k, NULL start_dte,
                                               NULL empmt_end_dte, NULL amt_begin_dte, NULL amt_end_dte,
                                               NULL ver_id_k, NULL ver_table, NULL delete_code,
                                               'CLIENT_INC_EARNINGS_AVG_AMT' trigger_table_nam,
                                               t1.last_updt_ts, NULL ver_obj_list
                                          FROM client_inc_earnings r1, client_inc_earnings_avg_amt t1
                                         WHERE r1.ID = t1.client_inc_earnings_id_k
                                           AND t1.last_updt_ts >= TO_DATE ('2012-01-01', 'YYYY-MM-DD')
                                           AND t1.last_updt_ts < TO_DATE ('2012-12-31', 'YYYY-MM-DD')
                                      UNION ALL
                                      SELECT   r1.client_id_k, NULL set_id, NULL wage_typ_cd_code_k,
                                               NULL monthly_avg_amt, NULL tcs_monthly_avg_amt,
                                               t1.hrly_rate, r2.nam AS non_provider_nam, t1.monthly_hrs,
                                               NULL unemp_justification_cd_code_k, NULL start_dte,
                                               NULL empmt_end_dte, NULL amt_begin_dte, NULL amt_end_dte,
                                               t1.ver_id_k, 'CLIENT_INC_EARNINGS_DTL' ver_table,
                                               NULL delete_code,
                                               'CLIENT_INC_EARNINGS_DTL' trigger_table_nam,
                                               t1.last_updt_ts,v1.ver_obj_list
                                          FROM client_inc_earnings r1,
                                               non_provider_org r2,
                                               ver v1,
                                               client_inc_earnings_dtl t1
                                         WHERE t1.ver_id_k = v1.ID
                                           AND v1.exists_flg = 'Y'
                                           AND v1.verified_flg = 'Y'
                                           AND r1.ID = t1.client_inc_earnings_id_k
                                           AND r2.ID = t1.non_provider_org_id_k
                                           AND t1.last_updt_ts >= TO_DATE ('2012-01-01', 'YYYY-MM-DD')
                                           AND t1.last_updt_ts < TO_DATE ('2012-12-31', 'YYYY-MM-DD')
                                      UNION ALL
                                      SELECT   r1.client_id_k, NULL set_id, NULL wage_typ_cd_code_k,
                                               NULL monthly_avg_amt, NULL tcs_monthly_avg_amt,
                                               NULL hrly_rate, NULL non_provider_nam, NULL monthly_hrs,
                                               t1.unemp_justification_cd_code_k, NULL start_dte,
                                               NULL empmt_end_dte, NULL amt_begin_dte, NULL amt_end_dte,
                                               NULL ver_id_k, NULL ver_table,
                                               DECODE (t1.exists_flg, 'N', 'AE', NULL) delete_code,
                                               'CLIENT_INC_EARNINGS_UNEMP' trigger_table_nam,
                                               t1.last_updt_ts,v1.ver_obj_list
                                          FROM client_inc_earnings r1,
                                               ver v1,
                                               client_inc_earnings_unemp t1
                                         WHERE t1.ver_id_k = v1.ID
                                           AND v1.exists_flg = 'Y'
                                           AND v1.verified_flg = 'Y'
                                           AND r1.ID = t1.client_inc_earnings_id_k
                                           AND t1.last_updt_ts >= TO_DATE ('2012-01-01', 'YYYY-MM-DD')
                                           AND t1.last_updt_ts < TO_DATE ('2012-12-31', 'YYYY-MM-DD')
                                      UNION ALL
                                      SELECT   r1.client_id_k, NULL set_id, NULL wage_typ_cd_code_k,
                                               NULL monthly_avg_amt, NULL tcs_monthly_avg_amt,
                                               NULL hrly_rate, NULL non_provider_nam, NULL monthly_hrs,
                                               NULL unemp_justification_cd_code_k, t1.start_dte,
                                               NULL empmt_end_dte, NULL amt_begin_dte, NULL amt_end_dte,
                                               NULL ver_id_k, NULL ver_table, NULL delete_code,
                                               'CLIENT_INC_EARNINGS_DTE' trigger_table_nam,
                                               t1.last_updt_ts,v1.ver_obj_list
                                          FROM client_inc_earnings r1, ver v1, client_inc_earnings_dte t1
                                         WHERE t1.ver_id_k = v1.ID
                                           AND v1.exists_flg = 'Y'
                                           AND v1.verified_flg = 'Y'
                                           AND r1.ID = t1.client_inc_earnings_id_k
                                           AND t1.last_updt_ts >= TO_DATE ('2012-01-01', 'YYYY-MM-DD')
                                           AND t1.last_updt_ts < TO_DATE ('2012-12-31', 'YYYY-MM-DD')
                                      UNION ALL
                                      SELECT   r1.client_id_k, NULL set_id, NULL wage_typ_cd_code_k,
                                               NULL monthly_avg_amt, NULL tcs_monthly_avg_amt,
                                               NULL hrly_rate, NULL non_provider_nam, NULL monthly_hrs,
                                               NULL unemp_justification_cd_code_k, NULL start_dte,
                                               t1.empmt_end_dte, NULL amt_begin_dte, NULL amt_end_dte,
                                               NULL ver_id_k, NULL ver_table, NULL delete_code,
                                               'CLIENT_INC_EARNINGS_EMPMT_END' trigger_table_nam,
                                               t1.last_updt_ts,v1.ver_obj_list
                                          FROM client_inc_earnings r1,
                                               ver v1,
                                               client_inc_earnings_empmt_end t1
                                         WHERE t1.ver_id_k = v1.ID
                                           AND v1.exists_flg = 'Y'
                                           AND v1.verified_flg = 'Y'
                                           AND r1.ID = t1.client_inc_earnings_id_k
                                           AND t1.last_updt_ts >= TO_DATE ('2012-01-01', 'YYYY-MM-DD')
                                           AND t1.last_updt_ts < TO_DATE ('2012-12-31', 'YYYY-MM-DD')
                                      UNION ALL
                                      SELECT   r1.client_id_k, NULL set_id, NULL wage_typ_cd_code_k,
                                               NULL monthly_avg_amt, NULL tcs_monthly_avg_amt,
                                               NULL hrly_rate, NULL non_provider_nam, NULL monthly_hrs,
                                               NULL unemp_justification_cd_code_k, NULL start_dte,
                                               NULL empmt_end_dte, r2.amt_begin_dte, r2.amt_end_dte,
                                               NULL ver_id_k, NULL ver_table,
                                               DECODE (t1.exists_flg, 'N', 'AE', NULL) delete_code,
                                               'CLIENT_INC_EARNINGS_AMT' trigger_table_nam,
                                               t1.last_updt_ts,v1.ver_obj_list
                                          FROM client_inc_earnings r1,
                                               (SELECT   client_id_k, MIN (period_dte) AS amt_begin_dte,
                                                         MAX (period_dte) AS amt_end_dte,
                                                         client_inc_earnings_id_k
                                                    FROM client_inc_earnings_amt
                                                GROUP BY client_id_k, client_inc_earnings_id_k) r2,
                                               ver v1,
                                               client_inc_earnings_amt t1
                                         WHERE t1.ver_id_k = v1.ID
                                           AND v1.exists_flg = 'Y'
                                           AND v1.verified_flg = 'Y'
                                           AND r1.ID = t1.client_inc_earnings_id_k
                                           AND r1.ID = r2.client_inc_earnings_id_k
                                           AND t1.last_updt_ts >= TO_DATE ('2012-01-01', 'YYYY-MM-DD')
                                           AND t1.last_updt_ts < TO_DATE ('2012-12-31', 'YYYY-MM-DD')) tableobj,
                                      --ver,
                                     (SELECT  distinct ecam_obj.client_id_k, extrn_system_id
                                          FROM (SELECT au1.ID, au1.pgm_cd_code_k
                                                  FROM au au1
                                                 WHERE au1.pgm_cd_code_k IN ('FF', 'FS', 'TCM', 'CC')) au_obj,
                                               (SELECT ausubtype_obj.au_id_k AS au_id_k,
                                                       pgm_obj.pgm_cd_code_k AS sub_pgm_code
                                                  FROM (SELECT au_subtyp1.au_id_k,
                                                               au_subtyp1.pgm_struct_id_k
                                                          FROM au_subtyp au_subtyp1
                                                         WHERE au_subtyp1.create_ts =
                                                                  (SELECT MAX (au_subtyp2.create_ts)
                                                                     FROM au_subtyp au_subtyp2
                                                                    WHERE au_subtyp2.au_id_k =
                                                                                        au_subtyp1.au_id_k
                                                                      AND au_subtyp2.create_ts <
                                                                             TO_DATE ('2013-01-01',
                                                                                      'YYYY-MM-DD'
                                                                                     )
                                                                      AND au_subtyp2.eff_dte =
                                                                             (SELECT MAX
                                                                                        (au_subtyp3.eff_dte
                                                                                        )
                                                                                FROM au_subtyp au_subtyp3
                                                                               WHERE au_subtyp3.au_id_k =
                                                                                        au_subtyp2.au_id_k
                                                                                 AND au_subtyp3.create_ts <
                                                                                        TO_DATE
                                                                                            ('2013-01-01',
                                                                                             'YYYY-MM-DD'
                                                                                            )
                                                                                 AND au_subtyp3.eff_dte <=
                                                                                        TO_DATE
                                                                                            ('2012-12-31',
                                                                                             'YYYY-MM-DD'
                                                                                            )))
                                                           AND au_subtyp1.exists_flg = 'Y') ausubtype_obj,
                                                       (SELECT pgm1.ID, pgm1.pgm_cd_code_k
                                                          FROM (SELECT     ID, pgm_cd_code_k
                                                                      FROM pgm_struct
                                                                     WHERE eff_dte <=
                                                                              TO_DATE ('2012-12-31',
                                                                                       'YYYY-MM-DD'
                                                                                      )
                                                                       AND (   end_dte >=
                                                                                  TO_DATE ('2012-12-31',
                                                                                           'YYYY-MM-DD'
                                                                                          )
                                                                            OR end_dte IS NULL
                                                                           )
                                                                START WITH pgm_struct_id_parent_k IS NULL
                                                                CONNECT BY PRIOR ID =
                                                                                    pgm_struct_id_parent_k) pgm1
                                                         WHERE pgm1.pgm_cd_code_k IN
                                                                  ('ADCI',
                                                                   'ADCR',
                                                                   'FFUP',
                                                                   'EFS',
                                                                   'TFS',
                                                                   'MAFC',
                                                                   'MAJ1',
                                                                   'MAJ2',
                                                                   'MAJ3',
                                                                   'MAPL',
                                                                   'MA S',
                                                                   'TCC',
                                                                   'EMMO',
                                                                   'TMMO'
                                                                  )) pgm_obj
                                                 WHERE ausubtype_obj.pgm_struct_id_k = pgm_obj.ID) ausubpgm_obj,
                                               (SELECT aus1.au_id_k, aus1.edbc_id_k
                                                  FROM au_stat aus1
                                                 WHERE aus1.create_ts =
                                                          (SELECT MAX (aus2.create_ts)
                                                             FROM au_stat aus2
                                                            WHERE aus2.au_id_k = aus1.au_id_k
                                                              AND aus2.create_ts <
                                                                      TO_DATE ('2013-01-01', 'YYYY-MM-DD')
                                                              AND aus2.eff_dte =
                                                                     (SELECT MAX (aus3.eff_dte)
                                                                        FROM au_stat aus3
                                                                       WHERE aus3.au_id_k = aus2.au_id_k
                                                                         AND aus3.create_ts <
                                                                                TO_DATE ('2013-01-01',
                                                                                         'YYYY-MM-DD'
                                                                                        )
                                                                         AND aus3.eff_dte <=
                                                                                TO_DATE ('2012-12-31',
                                                                                         'YYYY-MM-DD'
                                                                                        )))
                                                   AND aus1.edbc_id_k IS NOT NULL
                                                   AND aus1.au_stat_cd_code_k IN ('ACTIVE', 'CLOSED')) au_stat_obj,
                                               (SELECT ecam1.au_id_k, ecam1.client_id_k, ecam1.edbc_id_k,
                                                       last_updt_ts
                                                  FROM edbc_client_au_memb ecam1
                                                 WHERE ecam1.create_ts =
                                                          (SELECT MAX (ecam2.create_ts)
                                                             FROM edbc_client_au_memb ecam2
                                                            WHERE ecam2.au_id_k = ecam1.au_id_k
                                                              AND ecam2.client_id_k = ecam1.client_id_k
                                                            --  AND ecam2.edbc_id_k = ecam1.edbc_id_k
                                                              AND ecam2.create_ts <
                                                                      TO_DATE ('2013-01-01', 'YYYY-MM-DD')
                                                              AND ecam2.eff_dte =
                                                                     (SELECT MAX (ecam3.eff_dte)
                                                                        FROM edbc_client_au_memb ecam3
                                                                       WHERE ecam3.au_id_k = ecam2.au_id_k
                                                                         AND ecam3.client_id_k = ecam2.client_id_k
                                                                      --   AND ecam3.edbc_id_k = ecam2.edbc_id_k
                                                                         AND ecam3.create_ts <
                                                                                TO_DATE ('2013-01-01',
                                                                                         'YYYY-MM-DD'
                                                                                        )
                                                                         AND ecam3.eff_dte <=
                                                                                TO_DATE ('2012-12-31',
                                                                                         'YYYY-MM-DD'
                                                                                        )))) ecam_obj,
                            --                    (SELECT hhmemb1.hh_id_k, hhmemb1.client_id_k
                            --                       FROM hh_memb hhmemb1, ver
                            --                      WHERE hhmemb1.ver_id_k = ver.ID
                            --                        AND ver.exists_flg = 'Y'
                            --                        AND ver.verified_flg = 'Y'
                            --                        AND hhmemb1.create_ts =
                            --                               (SELECT MAX (hhmemb2.create_ts)
                            --                                  FROM hh_memb hhmemb2
                            --                                 WHERE hhmemb2.hh_id_k = hhmemb1.hh_id_k
                            --                                   AND hhmemb2.client_id_k =
                            --                                                            hhmemb1.client_id_k
                            --                                   AND hhmemb2.create_ts <
                            --                                           TO_DATE ('2013-01-01', 'YYYY-MM-DD')
                            --                                   AND hhmemb2.eff_dte =
                            --                                          (SELECT MAX (hhmemb3.eff_dte)
                            --                                             FROM hh_memb hhmemb3
                            --                                            WHERE hhmemb3.hh_id_k =
                            --                                                                hhmemb2.hh_id_k
                            --                                              AND hhmemb3.client_id_k =
                            --                                                            hhmemb2.client_id_k
                            --                                              AND hhmemb3.create_ts <
                            --                                                     TO_DATE ('2013-01-01',
                            --                                                              'YYYY-MM-DD'
                            --                                                             )
                            --                                              AND hhmemb3.eff_dte <=
                            --                                                     TO_DATE ('2012-12-31',
                            --                                                              'YYYY-MM-DD'
                            --                                                             )))) hhmemb_obj,
                            --                    (SELECT au_id_k, case_cat_seq
                            --                       FROM au_extrn_cross_refnc) aecr_obj,
                                               (SELECT extrn_system_id, client_id_k,
                                                       extrn_system_cd_code_k
                                                  FROM client_extrn_cross_refnc
                                                 WHERE extrn_system_cd_code_k = 'ACCRID') cecr_obj
                                         WHERE au_obj.ID = au_stat_obj.au_id_k
                                        --   AND au_obj.ID = aecr_obj.au_id_k
                                           AND au_stat_obj.au_id_k = ecam_obj.au_id_k
                                           AND ecam_obj.edbc_id_k = au_stat_obj.edbc_id_k
                                           AND ecam_obj.au_id_k = ausubpgm_obj.au_id_k
                                        --   AND ecam_obj.client_id_k = hhmemb_obj.client_id_k
                                           AND ecam_obj.client_id_k = cecr_obj.client_id_k) filter
                               WHERE filter.client_id_k = tableobj.client_id_k 
                               --AND tableobj.ver_id_k = ver.ID(+)
                            --   and filter.extrn_system_id = '11039653887'
                            ORDER BY filter.extrn_system_id, tableobj.last_updt_ts
                            Edited by: Vijayaraghavan Krishnan on Dec 3, 2012 5:26 PM
                            • 11. Re: Materialized View - Refresh Fast on Complex SQL
                              VijayaraghavanKrishnan
                              Explain Plan


                              Explain Plan -- From Test environment
                              Plan hash value: 1220894557
                               
                              -------------------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                             | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                              -------------------------------------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT                      |                               |     2 |   784 |       |   763K  (3)| 02:32:43 |
                              |   1 |  SORT ORDER BY                        |                               |     2 |   784 |       |   763K  (3)| 02:32:43 |
                              |*  2 |   HASH JOIN                           |                               |     2 |   784 |       |   763K  (3)| 02:32:43 |
                              |   3 |    VIEW                               |                               |     1 |    19 |       |   540K  (4)| 01:48:09 |
                              |   4 |     HASH UNIQUE                       |                               |     1 |   178 |       |   540K  (4)| 01:48:09 |
                              |*  5 |      FILTER                           |                               |       |       |       |            |          |
                              |*  6 |       HASH JOIN                       |                               | 35989 |  6255K|       |   331K  (1)| 01:06:21 |
                              |*  7 |        VIEW                           |                               |    25 |   200 |       |     3   (0)| 00:00:01 |
                              |*  8 |         FILTER                        |                               |       |       |       |            |          |
                              |*  9 |          CONNECT BY WITH FILTERING    |                               |       |       |       |            |          |
                              |* 10 |           TABLE ACCESS FULL           | PGM_STRUCT                    |     6 |   174 |       |     3   (0)| 00:00:01 |
                              |* 11 |           HASH JOIN                   |                               |       |       |       |            |          |
                              |  12 |            CONNECT BY PUMP            |                               |       |       |       |            |          |
                              |  13 |            TABLE ACCESS FULL          | PGM_STRUCT                    |    25 |   525 |       |     3   (0)| 00:00:01 |
                              |* 14 |        HASH JOIN                      |                               | 51825 |  8603K|       |   331K  (1)| 01:06:21 |
                              |* 15 |         HASH JOIN                     |                               | 11340 |  1417K|       |   327K  (1)| 01:05:27 |
                              |* 16 |          HASH JOIN                    |                               | 11187 |  1168K|       |   326K  (1)| 01:05:23 |
                              |* 17 |           HASH JOIN                   |                               | 11052 |  1046K|  4416K|   325K  (1)| 01:05:01 |
                              |* 18 |            HASH JOIN                  |                               | 57193 |  3742K|  2184K|   306K  (1)| 01:01:20 |
                              |  19 |             VIEW                      | VW_SQ_4                       | 57193 |  1508K|       |   228K  (1)| 00:45:48 |
                              |  20 |              HASH GROUP BY            |                               | 57193 |  1619K|       |   228K  (1)| 00:45:48 |
                              |  21 |               VIEW                    |                               | 57193 |  1619K|       |   228K  (1)| 00:45:48 |
                              |* 22 |                FILTER                 |                               |       |       |       |            |          |
                              |  23 |                 HASH GROUP BY         |                               | 57193 |  5417K|  1583M|   228K  (1)| 00:45:48 |
                              |* 24 |                  HASH JOIN            |                               |    14M|  1306M|   740M|   117K  (1)| 00:23:28 |
                              |* 25 |                   INDEX FAST FULL SCAN| ECAUM_PK                      |    14M|   579M|       | 18109   (2)| 00:03:38 |
                              |* 26 |                   INDEX FAST FULL SCAN| ECAUM_PK                      |    14M|   727M|       | 18063   (2)| 00:03:37 |
                              |  27 |             TABLE ACCESS FULL         | EDBC_CLIENT_AU_MEMB           |    14M|   538M|       | 42685   (1)| 00:08:33 |
                              |* 28 |            TABLE ACCESS FULL          | AU_STAT                       |  2729K|    78M|       | 12728   (2)| 00:02:33 |
                              |* 29 |           TABLE ACCESS FULL           | AU                            |   446K|  4357K|       |  1836   (1)| 00:00:23 |
                              |* 30 |          INDEX FAST FULL SCAN         | CECR_PK                       | 68911 |  1413K|       |   324   (2)| 00:00:04 |
                              |* 31 |         INDEX FAST FULL SCAN          | IDX_TEST                      |  1402K|    56M|       |  4446   (2)| 00:00:54 |
                              |  32 |       SORT AGGREGATE                  |                               |     1 |    47 |       |            |          |
                              |* 33 |        HASH JOIN                      |                               |     1 |    47 |       |     7  (15)| 00:00:01 |
                              |* 34 |         INDEX RANGE SCAN              | AUS_PK                        |    14 |   350 |       |     3   (0)| 00:00:01 |
                              |  35 |         VIEW                          | VW_SQ_2                       |    14 |   308 |       |     3   (0)| 00:00:01 |
                              |  36 |          SORT GROUP BY                |                               |    14 |   434 |       |     3   (0)| 00:00:01 |
                              |* 37 |           INDEX RANGE SCAN            | AUS_PK                        |    14 |   434 |       |     3   (0)| 00:00:01 |
                              |  38 |         SORT AGGREGATE                |                               |     1 |    47 |       |            |          |
                              |* 39 |          HASH JOIN                    |                               |     1 |    47 |       |     7  (15)| 00:00:01 |
                              |  40 |           VIEW                        | VW_SQ_3                       |     9 |   198 |       |     3   (0)| 00:00:01 |
                              |  41 |            SORT GROUP BY              |                               |     9 |   279 |       |     3   (0)| 00:00:01 |
                              |* 42 |             INDEX RANGE SCAN          | AUSU_PK                       |     9 |   279 |       |     3   (0)| 00:00:01 |
                              |* 43 |           INDEX RANGE SCAN            | AUSU_PK                       |     9 |   225 |       |     3   (0)| 00:00:01 |
                              |  44 |    VIEW                               |                               |   138K|    49M|       |   222K  (1)| 00:44:35 |
                              |  45 |     UNION-ALL                         |                               |       |       |       |            |          |
                              |* 46 |      HASH JOIN SEMI                   |                               |  4097 |   196K|       | 11453   (1)| 00:02:18 |
                              |  47 |       NESTED LOOPS                    |                               |  4097 |   172K|       |  9827   (1)| 00:01:58 |
                              |* 48 |        TABLE ACCESS FULL              | CLIENT_INC_EARNINGS           |  4097 |   124K|       |  1628   (2)| 00:00:20 |
                              |* 49 |        TABLE ACCESS BY INDEX ROWID    | VER                           |     1 |    12 |       |     2   (0)| 00:00:01 |
                              |* 50 |         INDEX UNIQUE SCAN             | VER_PK                        |     1 |       |       |     1   (0)| 00:00:01 |
                              |* 51 |       TABLE ACCESS FULL               | CLIENT_INC_EARNINGS           |   321K|  1880K|       |  1624   (2)| 00:00:20 |
                              |* 52 |      HASH JOIN                        |                               | 19402 |   663K|       |  1623   (2)| 00:00:20 |
                              |* 53 |       TABLE ACCESS FULL               | CLIENT_INC_EARNINGS_AVG_AMT   | 19402 |   435K|       |  1180   (2)| 00:00:15 |
                              |  54 |       INDEX FAST FULL SCAN            | CIERN_UK                      |   642K|  7523K|       |   438   (2)| 00:00:06 |
                              |  55 |      NESTED LOOPS                     |                               | 32005 |  2687K|       | 68104   (1)| 00:13:38 |
                              |* 56 |       HASH JOIN                       |                               | 32006 |  2312K|       |  4056   (2)| 00:00:49 |
                              |* 57 |        HASH JOIN                      |                               | 31850 |  1524K|       |  3455   (2)| 00:00:42 |
                              |* 58 |         TABLE ACCESS FULL             | CLIENT_INC_EARNINGS_DTL       | 31850 |  1150K|       |  3012   (2)| 00:00:37 |
                              |  59 |         INDEX FAST FULL SCAN          | CIERN_UK                      |   642K|  7523K|       |   438   (2)| 00:00:06 |
                              |  60 |        TABLE ACCESS FULL              | NON_PROVIDER_ORG              |   385K|  9414K|       |   598   (2)| 00:00:08 |
                              |* 61 |       TABLE ACCESS BY INDEX ROWID     | VER                           |     1 |    12 |       |     2   (0)| 00:00:01 |
                              |* 62 |        INDEX UNIQUE SCAN              | VER_PK                        |     1 |       |       |     1   (0)| 00:00:01 |
                              |  63 |      NESTED LOOPS                     |                               |  7078 |   380K|       | 15423   (1)| 00:03:06 |
                              |* 64 |       HASH JOIN                       |                               |  7078 |   297K|       |  1260   (2)| 00:00:16 |
                              |* 65 |        TABLE ACCESS FULL              | CLIENT_INC_EARNINGS_UNEMP     |  7078 |   214K|       |   817   (2)| 00:00:10 |
                              |  66 |        INDEX FAST FULL SCAN           | CIERN_UK                      |   642K|  7523K|       |   438   (2)| 00:00:06 |
                              |* 67 |       TABLE ACCESS BY INDEX ROWID     | VER                           |     1 |    12 |       |     2   (0)| 00:00:01 |
                              |* 68 |        INDEX UNIQUE SCAN              | VER_PK                        |     1 |       |       |     1   (0)| 00:00:01 |
                              |* 69 |      HASH JOIN                        |                               |  5759 |   309K|       | 13719   (1)| 00:02:45 |
                              |  70 |       NESTED LOOPS                    |                               |  5759 |   241K|       | 13276   (1)| 00:02:40 |
                              |* 71 |        TABLE ACCESS FULL              | CLIENT_INC_EARNINGS_DTE       |  5985 |   181K|       |  1299   (2)| 00:00:16 |
                              |* 72 |        TABLE ACCESS BY INDEX ROWID    | VER                           |     1 |    12 |       |     2   (0)| 00:00:01 |
                              |* 73 |         INDEX UNIQUE SCAN             | VER_PK                        |     1 |       |       |     1   (0)| 00:00:01 |
                              |  74 |       INDEX FAST FULL SCAN            | CIERN_UK                      |   642K|  7523K|       |   438   (2)| 00:00:06 |
                              |  75 |      NESTED LOOPS                     |                               | 17409 |   867K|       | 36680   (1)| 00:07:21 |
                              |* 76 |       HASH JOIN                       |                               | 17409 |   663K|       |  1843   (2)| 00:00:23 |
                              |* 77 |        TABLE ACCESS FULL              | CLIENT_INC_EARNINGS_EMPMT_END | 17409 |   459K|       |  1400   (2)| 00:00:17 |
                              |  78 |        INDEX FAST FULL SCAN           | CIERN_UK                      |   642K|  7523K|       |   438   (2)| 00:00:06 |
                              |* 79 |       TABLE ACCESS BY INDEX ROWID     | VER                           |     1 |    12 |       |     2   (0)| 00:00:01 |
                              |* 80 |        INDEX UNIQUE SCAN              | VER_PK                        |     1 |       |       |     1   (0)| 00:00:01 |
                              |  81 |      HASH GROUP BY                    |                               | 52625 |  5396K|    11M| 75882   (1)| 00:15:11 |
                              |* 82 |       HASH JOIN                       |                               | 52625 |  5396K|  3152K| 74623   (1)| 00:14:56 |
                              |  83 |        NESTED LOOPS                   |                               | 33252 |  2760K|       | 69979   (1)| 00:14:00 |
                              |* 84 |         HASH JOIN                     |                               | 33252 |  1980K|       |  3438   (2)| 00:00:42 |
                              |* 85 |          TABLE ACCESS FULL            | CLIENT_INC_EARNINGS_AMT       | 33252 |  1201K|       |  2995   (2)| 00:00:36 |
                              |  86 |          INDEX FAST FULL SCAN         | CIERN_UK                      |   642K|    14M|       |   438   (2)| 00:00:06 |
                              |* 87 |         TABLE ACCESS BY INDEX ROWID   | VER                           |     1 |    24 |       |     2   (0)| 00:00:01 |
                              |* 88 |          INDEX UNIQUE SCAN            | VER_PK                        |     1 |       |       |     1   (0)| 00:00:01 |
                              |  89 |        TABLE ACCESS FULL              | CLIENT_INC_EARNINGS_AMT       |   994K|    18M|       |  2973   (1)| 00:00:36 |
                              -------------------------------------------------------------------------------------------------------------------------------
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                               
                                 2 - access("FILTER"."CLIENT_ID_K"="TABLEOBJ"."CLIENT_ID_K")
                                 5 - filter("AUS1"."CREATE_TS"= (SELECT MAX("AUS2"."CREATE_TS") FROM "AU_STAT" "AUS2", (SELECT MAX("AUS3"."EFF_DTE") 
                                            "VW_COL_1","AUS3"."AU_ID_K" "AU_ID_K" FROM "AU_STAT" "AUS3" WHERE "AUS3"."EFF_DTE"<=TO_DATE(' 2012-12-31 00:00:00', 
                                            'syyyy-mm-dd hh24:mi:ss') AND "AUS3"."AU_ID_K"=:B1 AND "AUS3"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00' GROUP BY 
                                            "AUS3"."AU_ID_K") "VW_SQ_2" WHERE "AU_ID_K"="AUS2"."AU_ID_K" AND "AUS2"."EFF_DTE"="VW_COL_1" AND "AUS2"."AU_ID_K"=:B2 
                                            AND "AUS2"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00') AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))= (SELECT 
                                            MAX("AU_SUBTYP2"."CREATE_TS") FROM "AU_SUBTYP" "AU_SUBTYP2", (SELECT MAX("AU_SUBTYP3"."EFF_DTE") 
                                            "VW_COL_1","AU_SUBTYP3"."AU_ID_K" "AU_ID_K" FROM "AU_SUBTYP" "AU_SUBTYP3" WHERE "AU_SUBTYP3"."EFF_DTE"<=TO_DATE(' 
                                            2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AU_SUBTYP3"."AU_ID_K"=:B3 AND "AU_SUBTYP3"."CREATE_TS"<TIMESTAMP' 
                                            2013-01-01 00:00:00' GROUP BY "AU_SUBTYP3"."AU_ID_K") "VW_SQ_3" WHERE "AU_SUBTYP2"."AU_ID_K"=:B4 AND 
                                            "AU_ID_K"="AU_SUBTYP2"."AU_ID_K" AND "AU_SUBTYP2"."EFF_DTE"="VW_COL_1" AND "AU_SUBTYP2"."CREATE_TS"<TIMESTAMP' 
                                            2013-01-01 00:00:00'))
                                 6 - access("AU_SUBTYP1"."PGM_STRUCT_ID_K"="PGM1"."ID")
                                 7 - filter("PGM1"."PGM_CD_CODE_K"='ADCI' OR "PGM1"."PGM_CD_CODE_K"='ADCR' OR "PGM1"."PGM_CD_CODE_K"='EFS' OR 
                                            "PGM1"."PGM_CD_CODE_K"='EMMO' OR "PGM1"."PGM_CD_CODE_K"='FFUP' OR "PGM1"."PGM_CD_CODE_K"='MA S' OR 
                                            "PGM1"."PGM_CD_CODE_K"='MAFC' OR "PGM1"."PGM_CD_CODE_K"='MAJ1' OR "PGM1"."PGM_CD_CODE_K"='MAJ2' OR 
                                            "PGM1"."PGM_CD_CODE_K"='MAJ3' OR "PGM1"."PGM_CD_CODE_K"='MAPL' OR "PGM1"."PGM_CD_CODE_K"='TCC' OR 
                                            "PGM1"."PGM_CD_CODE_K"='TFS' OR "PGM1"."PGM_CD_CODE_K"='TMMO')
                                 8 - filter("EFF_DTE"<=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("END_DTE">=TO_DATE(' 
                                            2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "END_DTE" IS NULL))
                                 9 - access("PGM_STRUCT_ID_PARENT_K"=PRIOR "ID")
                                10 - filter("PGM_STRUCT_ID_PARENT_K" IS NULL)
                                11 - access("PGM_STRUCT_ID_PARENT_K"=PRIOR "ID")
                                14 - access("ECAM1"."AU_ID_K"="AU_SUBTYP1"."AU_ID_K")
                                15 - access("ECAM1"."CLIENT_ID_K"="CLIENT_ID_K")
                                16 - access("AU1"."ID"="AUS1"."AU_ID_K")
                                17 - access("AUS1"."AU_ID_K"="ECAM1"."AU_ID_K" AND "ECAM1"."EDBC_ID_K"="AUS1"."EDBC_ID_K")
                                18 - access("ECAM1"."CREATE_TS"="VW_COL_1" AND "AU_ID_K"="ECAM1"."AU_ID_K" AND "CLIENT_ID_K"="ECAM1"."CLIENT_ID_K")
                                22 - filter("ECAM2"."EFF_DTE"=MAX("ECAM3"."EFF_DTE"))
                                24 - access("ECAM3"."AU_ID_K"="ECAM2"."AU_ID_K" AND "ECAM3"."CLIENT_ID_K"="ECAM2"."CLIENT_ID_K")
                                25 - filter("ECAM3"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00' AND "ECAM3"."EFF_DTE"<=TO_DATE(' 2012-12-31 
                                            00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
                                26 - filter("ECAM2"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00')
                                28 - filter(("AUS1"."AU_STAT_CD_CODE_K"='ACTIVE' OR "AUS1"."AU_STAT_CD_CODE_K"='CLOSED') AND "AUS1"."EDBC_ID_K" IS 
                                            NOT NULL)
                                29 - filter("AU1"."PGM_CD_CODE_K"='CC' OR "AU1"."PGM_CD_CODE_K"='FF' OR "AU1"."PGM_CD_CODE_K"='FS' OR 
                                            "AU1"."PGM_CD_CODE_K"='TCM')
                                30 - filter("EXTRN_SYSTEM_CD_CODE_K"='ACCRID')
                                31 - filter("AU_SUBTYP1"."EXISTS_FLG"='Y')
                                33 - access("AUS2"."EFF_DTE"="VW_COL_1" AND "AU_ID_K"="AUS2"."AU_ID_K")
                                34 - access("AUS2"."AU_ID_K"=:B1 AND "AUS2"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00')
                                     filter("AUS2"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00')
                                37 - access("AUS3"."AU_ID_K"=:B1 AND "AUS3"."EFF_DTE"<=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
                                            "AUS3"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00')
                                     filter("AUS3"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00')
                                39 - access("AU_SUBTYP2"."EFF_DTE"="VW_COL_1" AND SYS_OP_DESCEND("AU_SUBTYP2"."EFF_DTE")=SYS_OP_DESCEND("VW_COL_1") 
                                            AND "AU_ID_K"="AU_SUBTYP2"."AU_ID_K")
                                42 - access("AU_SUBTYP3"."AU_ID_K"=:B1 AND "AU_SUBTYP3"."EFF_DTE"<=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd 
                                            hh24:mi:ss') AND "AU_SUBTYP3"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00')
                                     filter("AU_SUBTYP3"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00')
                                43 - access("AU_SUBTYP2"."AU_ID_K"=:B1 AND "AU_SUBTYP2"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00')
                                     filter("AU_SUBTYP2"."CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00')
                                46 - access("T1"."SET_ID"="SET_ID")
                                48 - filter("T1"."VER_ID_K" IS NOT NULL AND "T1"."LAST_UPDT_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND 
                                            "T1"."LAST_UPDT_TS"<TIMESTAMP' 2012-12-31 00:00:00')
                                49 - filter("V1"."EXISTS_FLG"='Y' AND "V1"."VERIFIED_FLG"='Y')
                                50 - access("T1"."VER_ID_K"="V1"."ID")
                                51 - filter("EXISTS_FLG"='Y')
                                52 - access("R1"."ID"="T1"."CLIENT_INC_EARNINGS_ID_K")
                                53 - filter("T1"."LAST_UPDT_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "T1"."LAST_UPDT_TS"<TIMESTAMP' 2012-12-31 
                                            00:00:00')
                                56 - access("R2"."ID"="T1"."NON_PROVIDER_ORG_ID_K")
                                57 - access("R1"."ID"="T1"."CLIENT_INC_EARNINGS_ID_K")
                                58 - filter("T1"."LAST_UPDT_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "T1"."NON_PROVIDER_ORG_ID_K" IS NOT NULL AND 
                                            "T1"."LAST_UPDT_TS"<TIMESTAMP' 2012-12-31 00:00:00')
                                61 - filter("V1"."EXISTS_FLG"='Y' AND "V1"."VERIFIED_FLG"='Y')
                                62 - access("T1"."VER_ID_K"="V1"."ID")
                                64 - access("R1"."ID"="T1"."CLIENT_INC_EARNINGS_ID_K")
                                65 - filter("T1"."LAST_UPDT_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "T1"."VER_ID_K" IS NOT NULL AND 
                                            "T1"."LAST_UPDT_TS"<TIMESTAMP' 2012-12-31 00:00:00')
                                67 - filter("V1"."EXISTS_FLG"='Y' AND "V1"."VERIFIED_FLG"='Y')
                                68 - access("T1"."VER_ID_K"="V1"."ID")
                                69 - access("R1"."ID"="T1"."CLIENT_INC_EARNINGS_ID_K")
                                71 - filter("T1"."LAST_UPDT_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "T1"."LAST_UPDT_TS"<TIMESTAMP' 2012-12-31 
                                            00:00:00')
                                72 - filter("V1"."EXISTS_FLG"='Y' AND "V1"."VERIFIED_FLG"='Y')
                                73 - access("T1"."VER_ID_K"="V1"."ID")
                                76 - access("R1"."ID"="T1"."CLIENT_INC_EARNINGS_ID_K")
                                77 - filter("T1"."LAST_UPDT_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "T1"."VER_ID_K" IS NOT NULL AND 
                                            "T1"."LAST_UPDT_TS"<TIMESTAMP' 2012-12-31 00:00:00')
                                79 - filter("V1"."EXISTS_FLG"='Y' AND "V1"."VERIFIED_FLG"='Y')
                                80 - access("T1"."VER_ID_K"="V1"."ID")
                                82 - access("R1"."ID"="CLIENT_INC_EARNINGS_ID_K")
                                84 - access("R1"."ID"="T1"."CLIENT_INC_EARNINGS_ID_K")
                                85 - filter("T1"."LAST_UPDT_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "T1"."LAST_UPDT_TS"<TIMESTAMP' 2012-12-31 
                                            00:00:00')
                                87 - filter("V1"."EXISTS_FLG"='Y' AND "V1"."VERIFIED_FLG"='Y')
                                88 - access("T1"."VER_ID_K"="V1"."ID")
                              • 12. Re: Materialized View - Refresh Fast on Complex SQL
                                Mark Malakanov (user11181920)
                                Most of work is going in this fragment
                                SELECT ecam1.au_id_k, ecam1.client_id_k, ecam1.edbc_id_k,
                                                           last_updt_ts
                                                      FROM edbc_client_au_memb ecam1
                                                     WHERE ecam1.create_ts =
                                                              (SELECT MAX (ecam2.create_ts)
                                                                 FROM edbc_client_au_memb ecam2
                                                                WHERE ecam2.au_id_k = ecam1.au_id_k
                                                                  AND ecam2.client_id_k = ecam1.client_id_k
                                                                --  AND ecam2.edbc_id_k = ecam1.edbc_id_k
                                                                  AND ecam2.create_ts <
                                                                          TO_DATE ('2013-01-01', 'YYYY-MM-DD')
                                                                  AND ecam2.eff_dte =
                                                                         (SELECT MAX (ecam3.eff_dte)
                                                                            FROM edbc_client_au_memb ecam3
                                                                           WHERE ecam3.au_id_k = ecam2.au_id_k
                                                                             AND ecam3.client_id_k = ecam2.client_id_k
                                                                          --   AND ecam3.edbc_id_k = ecam2.edbc_id_k
                                                                             AND ecam3.create_ts <
                                                                                    TO_DATE ('2013-01-01',
                                                                                             'YYYY-MM-DD'
                                                                                            )
                                                                             AND ecam3.eff_dte <=
                                                                                    TO_DATE ('2012-12-31',
                                                                                             'YYYY-MM-DD'
                                                                                            )))
                                And it does not use index on CREATE_TS. It uses PK instead, that I assume is (au_id_k,client_id_k...)

                                I'd try to change it to
                                SELECT ecam1.au_id_k, ecam1.client_id_k, ecam1.edbc_id_k,
                                                           last_updt_ts
                                                      FROM 
                                ----- This is a kind of your MV, but is not materialized
                                 (select au_id_k, client_id_k, edbc_id_k, last_updt_ts 
                                 from edbc_client_au_memb 
                                 where create_ts < TO_DATE ('2013-01-01','YYYY-MM-DD') ) ecam1
                                -----
                                                     WHERE ecam1.create_ts =
                                                              (SELECT MAX (ecam2.create_ts)
                                                                 FROM edbc_client_au_memb ecam2
                                                                WHERE ecam2.au_id_k = ecam1.au_id_k
                                                                  AND ecam2.client_id_k = ecam1.client_id_k
                                                                --  AND ecam2.edbc_id_k = ecam1.edbc_id_k
                                                                  AND ecam2.create_ts <
                                                                          TO_DATE ('2013-01-01', 'YYYY-MM-DD')
                                                                  AND ecam2.eff_dte =
                                                                         (SELECT MAX (ecam3.eff_dte)
                                                                            FROM edbc_client_au_memb ecam3
                                                                           WHERE ecam3.au_id_k = ecam2.au_id_k
                                                                             AND ecam3.client_id_k = ecam2.client_id_k
                                                                          --   AND ecam3.edbc_id_k = ecam2.edbc_id_k
                                                                             AND ecam3.create_ts <
                                                                                    TO_DATE ('2013-01-01',
                                                                                             'YYYY-MM-DD'
                                                                                            )
                                                                             AND ecam3.eff_dte <=
                                                                                    TO_DATE ('2012-12-31',
                                                                                             'YYYY-MM-DD'
                                                                                            )))
                                • 13. Re: Materialized View - Refresh Fast on Complex SQL
                                  VijayaraghavanKrishnan
                                  Hi Thanks for your reply,

                                  On both the cases it is not picking the CREATE_TS index. This query is using lot of Temp Space that will hurt a lot.

                                  Plan hash value: 1583612918
                                   
                                  ----------------------------------------------------------------------------------------------------------
                                  | Id  | Operation                  | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                  ----------------------------------------------------------------------------------------------------------
                                  |   0 | SELECT STATEMENT           |                     |     2 |   252 |       |   447K  (1)| 01:29:32 |
                                  |*  1 |  FILTER                    |                     |       |       |       |            |          |
                                  |   2 |   HASH GROUP BY            |                     |     2 |   252 |       |   447K  (1)| 01:29:32 |
                                  |*  3 |    HASH JOIN               |                     |    14M|  1697M|   862M|   446K  (1)| 01:29:20 |
                                  |*  4 |     TABLE ACCESS FULL      | EDBC_CLIENT_AU_MEMB |    14M|   700M|       | 42921   (2)| 00:08:36 |
                                  |*  5 |     HASH JOIN              |                     |    14M|   996M|   471M|   303K  (1)| 01:00:38 |
                                  |   6 |      VIEW                  | VW_SQ_1             |    14M|   309M|       |   219K  (1)| 00:43:52 |
                                  |   7 |       HASH GROUP BY        |                     |    14M|   794M|  2164M|   219K  (1)| 00:43:52 |
                                  |*  8 |        INDEX FAST FULL SCAN| ECAUM_PK            |    14M|   794M|       | 18109   (2)| 00:03:38 |
                                  |*  9 |      INDEX FAST FULL SCAN  | ECAUM_PK            |    14M|   687M|       | 18063   (2)| 00:03:37 |
                                  ----------------------------------------------------------------------------------------------------------
                                   
                                  Predicate Information (identified by operation id):
                                  ---------------------------------------------------
                                   
                                     1 - filter("CREATE_TS"=MAX("ECAM2"."CREATE_TS"))
                                     3 - access("ECAM2"."AU_ID_K"="AU_ID_K" AND "ECAM2"."CLIENT_ID_K"="CLIENT_ID_K")
                                     4 - filter("CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00')
                                     5 - access("ECAM2"."EFF_DTE"="VW_COL_1" AND "AU_ID_K"="ECAM2"."AU_ID_K" AND 
                                  Thanks,
                                  Vijay

                                  Edited by: Vijayaraghavan Krishnan on Dec 4, 2012 10:40 AM
                                  • 14. Re: Materialized View - Refresh Fast on Complex SQL
                                    Mark Malakanov (user11181920)
                                    On both the cases it is not picking the CREATE_TS index.
                                    Huh,
                                    Fileter: "CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00'
                                    What is data type of edbc_client_au_memb.CREATE_TS?

                                    If it is TIMESTAMP then try to change all TO_DATE calls to TO_TIMESTAMP calls.


                                    And. Sorry. Of course "CREATE_TS"<TIMESTAMP' 2013-01-01 00:00:00' will produce full scan because it will return ALL rows from table.

                                    The condition in the dynamic view should be
                                    "CREATE_TS" > TO_TIMESTAMP('2012-01-01 00:00:00')

                                    or whatever TS you assumed for your MV.

                                    Edited by: user11181920 on Dec 4, 2012 11:55 AM
                                    1 2 Previous Next