1 2 Previous Next 18 Replies Latest reply: Dec 4, 2012 3:30 PM by Mark Malakanov (user11181920) Go to original post RSS
      • 15. Re: Materialized View - Refresh Fast on Complex SQL
        VijayaraghavanKrishnan
        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)
          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
            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)
              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