This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 4, 2012 1:30 PM by Mark Malakanov (user11181920) Go to original post RSS
  • 15. Re: Materialized View - Refresh Fast on Complex SQL
    VijayaraghavanKrishnan Pro
    Currently Being Moderated
    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.
    I know it is going to scan my whole table if I don't have a lower bound. I added the lower bound


    With Index on Create_TS Column
    Plan hash value: 1295886163
     
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                     |     1 |    92 |       | 39101   (2)| 00:07:50 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID  | EDBC_CLIENT_AU_MEMB |     1 |    51 |       |     3   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS                |                     |     1 |    92 |       | 39101   (2)| 00:07:50 |
    |   3 |    VIEW                       | VW_SQ_2             |     1 |    41 |       | 39098   (2)| 00:07:50 |
    |*  4 |     FILTER                    |                     |       |       |       |            |          |
    |   5 |      HASH GROUP BY            |                     |     1 |    29 |       | 39098   (2)| 00:07:50 |
    |   6 |       VIEW                    |                     |    65 |  1885 |       | 39098   (2)| 00:07:50 |
    |*  7 |        FILTER                 |                     |       |       |       |            |          |
    |   8 |         HASH GROUP BY         |                     |    65 |  7020 |       | 39098   (2)| 00:07:50 |
    |*  9 |          HASH JOIN            |                     | 15847 |  1671K|    24M| 39097   (2)| 00:07:50 |
    |* 10 |           INDEX FAST FULL SCAN| ECAUM_PK            |   473K|    19M|       | 18066   (2)| 00:03:37 |
    |* 11 |           INDEX FAST FULL SCAN| ECAUM_PK            |   473K|    29M|       | 18065   (2)| 00:03:37 |
    |* 12 |    INDEX RANGE SCAN           | IDX_EBDC_CL_1       |     1 |       |       |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter("AU_ID_K"="AU_ID_K" AND "CLIENT_ID_K"="CLIENT_ID_K")
       4 - filter(MAX("$vm_col_1")<TIMESTAMP' 2013-01-01 00:00:00' AND MAX("$vm_col_1")>=TIMESTAMP' 
                  2012-01-01 00:00:00')
       7 - filter("ECAM2"."EFF_DTE"=MAX("ECAM3"."EFF_DTE"))
       9 - access("ECAM3"."AU_ID_K"="ECAM2"."AU_ID_K" AND "ECAM3"."CLIENT_ID_K"="ECAM2"."CLIENT_ID_K")
      10 - filter("CREATE_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "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'))
      11 - filter("CREATE_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "ECAM2"."CREATE_TS"<TIMESTAMP' 
                  2013-01-01 00:00:00')
      12 - access("CREATE_TS"="VW_COL_1")
           filter("CREATE_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "CREATE_TS"<TIMESTAMP' 2013-01-01 
    Without Index on Create_TS column
    Plan hash value: 1611348960
     
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                     |     1 |    92 |       | 39101   (2)| 00:07:50 |
    |   1 |  TABLE ACCESS BY INDEX ROWID  | EDBC_CLIENT_AU_MEMB |     1 |    51 |       |     3   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS                |                     |     1 |    92 |       | 39101   (2)| 00:07:50 |
    |   3 |    VIEW                       | VW_SQ_2             |     1 |    41 |       | 39098   (2)| 00:07:50 |
    |*  4 |     FILTER                    |                     |       |       |       |            |          |
    |   5 |      HASH GROUP BY            |                     |     1 |    29 |       | 39098   (2)| 00:07:50 |
    |   6 |       VIEW                    |                     |    65 |  1885 |       | 39098   (2)| 00:07:50 |
    |*  7 |        FILTER                 |                     |       |       |       |            |          |
    |   8 |         HASH GROUP BY         |                     |    65 |  7020 |       | 39098   (2)| 00:07:50 |
    |*  9 |          HASH JOIN            |                     | 15847 |  1671K|    24M| 39097   (2)| 00:07:50 |
    |* 10 |           INDEX FAST FULL SCAN| ECAUM_PK            |   473K|    19M|       | 18066   (2)| 00:03:37 |
    |* 11 |           INDEX FAST FULL SCAN| ECAUM_PK            |   473K|    29M|       | 18065   (2)| 00:03:37 |
    |* 12 |    INDEX RANGE SCAN           | ECAUM_PK            |     1 |       |       |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       4 - filter(MAX("$vm_col_1")<TIMESTAMP' 2013-01-01 00:00:00' AND MAX("$vm_col_1")>=TIMESTAMP' 
                  2012-01-01 00:00:00')
       7 - filter("ECAM2"."EFF_DTE"=MAX("ECAM3"."EFF_DTE"))
       9 - access("ECAM3"."AU_ID_K"="ECAM2"."AU_ID_K" AND "ECAM3"."CLIENT_ID_K"="ECAM2"."CLIENT_ID_K")
      10 - filter("CREATE_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "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'))
      11 - filter("CREATE_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "ECAM2"."CREATE_TS"<TIMESTAMP' 
                  2013-01-01 00:00:00')
      12 - access("AU_ID_K"="AU_ID_K" AND "CREATE_TS"="VW_COL_1" AND "CLIENT_ID_K"="CLIENT_ID_K")
           filter("CREATE_TS">=TIMESTAMP' 2012-01-01 00:00:00' AND "CREATE_TS"<TIMESTAMP' 2013-01-01 
                  00:00:00' AND "CREATE_TS"="VW_COL_1" AND "CLIENT_ID_K"="CLIENT_ID_K")
    I dont see any performance improvement on the adding a separate index on Create_TS

    Thanks,
    Vijay
  • 16. Re: Materialized View - Refresh Fast on Complex SQL
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    I dont see any performance improvement on the adding a separate index on Create_TS
    You said that this index already exists.

    Do you see improvement after adding dynamic view with
    "CREATE_TS" > TO_TIMESTAMP('2012-01-01 00:00:00') ?
  • 17. Re: Materialized View - Refresh Fast on Complex SQL
    VijayaraghavanKrishnan Pro
    Currently Being Moderated
    Hi,

    Not much difference. I recently created this as part of tuning on the Test environment, I just thought of checking without the Index how query performs. I will create the index on this column if needed as I do have this column as part of composite Index.

    Thanks,
    Vijay
  • 18. Re: Materialized View - Refresh Fast on Complex SQL
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    Not much difference.
    I see some difference

    before
    |  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 |
    after
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                     |     1 |    92 |       | 39101   (2)| 00:07:50 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID  | EDBC_CLIENT_AU_MEMB |     1 |    51 |       |     3   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS                |                     |     1 |    92 |       | 39101   (2)| 00:07:50 |
    |   3 |    VIEW                       | VW_SQ_2             |     1 |    41 |       | 39098   (2)| 00:07:50 |
    |*  4 |     FILTER                    |                     |       |       |       |            |          |
    |   5 |      HASH GROUP BY            |                     |     1 |    29 |       | 39098   (2)| 00:07:50 |
    |   6 |       VIEW                    |                     |    65 |  1885 |       | 39098   (2)| 00:07:50 |
    |*  7 |        FILTER                 |                     |       |       |       |            |          |
    |   8 |         HASH GROUP BY         |                     |    65 |  7020 |       | 39098   (2)| 00:07:50 |
    |*  9 |          HASH JOIN            |                     | 15847 |  1671K|    24M| 39097   (2)| 00:07:50 |
    |* 10 |           INDEX FAST FULL SCAN| ECAUM_PK            |   473K|    19M|       | 18066   (2)| 00:03:37 |
    |* 11 |           INDEX FAST FULL SCAN| ECAUM_PK            |   473K|    29M|       | 18065   (2)| 00:03:37 |
    |* 12 |    INDEX RANGE SCAN           | IDX_EBDC_CL_1       |     1 |       |       |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------------
    This is the difference
    before
    | 27 | TABLE ACCESS FULL | EDBC_CLIENT_AU_MEMB | 14M| 538M| | 42685 (1)| 00:08:33 |
    after
    |* 12 | INDEX RANGE SCAN | IDX_EBDC_CL_1 | 1 | | | 2 (0)| 00:00:01 |
1 2 Previous Next

Legend

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