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

Materialized View - Refresh Fast on Complex SQL

VijayaraghavanKrishnan Pro
Currently Being Moderated
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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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

Legend

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