In Line View Performance Over SORT MERGE JOIN

I am ecstatic the formatting does not look like garbage.
The reporting was designed to allow users to select a pre-calculated date which is refreshed each day against the current date using materialized view. 

Example:
Interval: DAILY
Date:     3 years from Current Date
SQL> SELECT t1.day_id FROM DYNAMIC_TIME_VIEW t1 WHERE t1.TIME_POINT_NAME='DAILY-CURR_DT_MINUS_36_MONTHS';
DAY_ID
---------
15-DEC-09

Interval: DAILY
Date:     Current Date
SQL> SELECT t2.day_id FROM DYNAMIC_TIME_VIEW t2 WHERE t2.TIME_POINT_NAME='DAILY-CURR_DT';
DAY_ID
---------
15-DEC-12

SQL>

Users would select from LOV the 2 dates which will be used to filtered the data.
Here is the explain plan with TABLE JOIN. Performance was horrendous with SORT MERGE JOIN.
The 2 ^SORT JOIN ^MERGE and 2 %SORT JOIN %MERGE

explain plan for 
SELECT a.dt 
FROM   bigtable a, DYNAMIC_TIME_VIEW t1, DYNAMIC_TIME_VIEW t2
WHERE  t1.TIME_POINT_NAME = 'DAILY-CURR_DT_MINUS_36_MONTHS'
AND    t2.TIME_POINT_NAME = 'DAILY-CURR_DT' 
AND    a.dt BETWEEN t1.DAY_ID AND t2.DAY_ID
;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 1569934958
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |  3432M|    83G| 75712  (50)| 00:00:03 |
|   1 |  %MERGE JOIN                          |                          |  3432M|    83G| 75712  (50)| 00:00:03 |
|   2 |   %SORT JOIN                          |                          |    80M|  1300M| 39527   (4)| 00:00:02 |
|   3 |    ^MERGE JOIN                        |                          |    80M|  1300M| 39527   (4)| 00:00:02 |
|   4 |     ^SORT JOIN                        |                          |    36M|   278M| 39048   (3)| 00:00:02 |
|   5 |       INDEX FULL SCAN                 | INK_bigtable_dt          |    36M|   278M| 39048   (3)| 00:00:02 |
|*  6 |     ^SORT JOIN                        |                          |    45 |   405 |     7  (15)| 00:00:01 |
|   7 |       VIEW                            | DYNAMIC_TIME_VIEW        |    45 |   405 |     6   (0)| 00:00:01 |
|   8 |        UNION-ALL                      |                          |       |       |            |          |
|   9 |         MAT_VIEW ACCESS BY INDEX ROWID| DYNAMIC_TIME_POINT_MV    |     1 |    39 |     1   (0)| 00:00:01 |
|* 10 |          INDEX UNIQUE SCAN            | UX$DYNAMIC_TIME_POINT_MV |     1 |       |     0   (0)| 00:00:01 |
|* 11 |         INDEX FAST FULL SCAN          | PK_DAY_FORMATTED_MVIEW   |    44 |   352 |     5   (0)| 00:00:01 |
|* 12 |   %SORT JOIN                          |                          |    45 |   405 |     7  (15)| 00:00:01 |
|  13 |     VIEW                              | DYNAMIC_TIME_VIEW        |    45 |   405 |     6   (0)| 00:00:01 |
|  14 |      UNION-ALL                        |                          |       |       |            |          |
|  15 |       MAT_VIEW ACCESS BY INDEX ROWID  | DYNAMIC_TIME_POINT_MV    |     1 |    39 |     1   (0)| 00:00:01 |
|* 16 |        INDEX UNIQUE SCAN              | UX$DYNAMIC_TIME_POINT_MV |     1 |       |     0   (0)| 00:00:01 |
|* 17 |       INDEX FAST FULL SCAN            | PK_DAY_FORMATTED_MVIEW   |    44 |   352 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(INTERNAL_FUNCTION("A"."dt")>=INTERNAL_FUNCTION("T1"."DAY_ID"))
       filter(INTERNAL_FUNCTION("A"."dt")>=INTERNAL_FUNCTION("T1"."DAY_ID"))
  10 - access("TIME_POINT_NAME"='DAILY-CURR_DT_MINUS_36_MONTHS')
  11 - filter(TO_CHAR(INTERNAL_FUNCTION("DAY_ID"),'mm/dd/yyyy')='DAILY-CURR_DT_MINUS_36_MONTHS')
  12 - access("A"."dt"<="T2"."DAY_ID")
       filter("A"."dt"<="T2"."DAY_ID")   
  16 - access("TIME_POINT_NAME"='DAILY-CURR_DT')   
  17 - filter(TO_CHAR(INTERNAL_FUNCTION("DAY_ID"),'mm/dd/yyyy')='DAILY-CURR_DT') 

36 rows selected. 

Here is the explain plan with INLINE VIEW.

explain plan for  
SELECT a.dt  
FROM   bigtable a 
WHERE  dt  
BETWEEN 
(SELECT t1.day_id FROM DYNAMIC_TIME_VIEW t1 WHERE t1.TIME_POINT_NAME='DAILY-CURR_DT_MINUS_36_MONTHS') 
AND     
(SELECT t2.day_id FROM DYNAMIC_TIME_VIEW t2 WHERE t2.TIME_POINT_NAME = 'DAILY-CURR_DT')
; 

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 2375256111
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          | 91238 |   712K|   193   (3)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN                 | INK_bigtable_dt          | 91238 |   712K|   181   (3)| 00:00:01 |
|   2 |   VIEW                            | DYNAMIC_TIME_VIEW        |    45 |  1395 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL                      |                          |       |       |            |          |
|   4 |     MAT_VIEW ACCESS BY INDEX ROWID| DYNAMIC_TIME_POINT_MV    |     1 |    39 |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN            | UX$DYNAMIC_TIME_POINT_MV |     1 |       |     0   (0)| 00:00:01 |
|*  6 |     INDEX FAST FULL SCAN          | PK_DAY_FORMATTED_MVIEW   |    44 |   352 |     5   (0)| 00:00:01 |
|   7 |   VIEW                            | DYNAMIC_TIME_VIEW        |    45 |   405 |     6   (0)| 00:00:01 |
|   8 |    UNION-ALL                      |                          |       |       |            |          |
|   9 |     MAT_VIEW ACCESS BY INDEX ROWID| DYNAMIC_TIME_POINT_MV    |     1 |    39 |     1   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN            | UX$DYNAMIC_TIME_POINT_MV |     1 |       |     0   (0)| 00:00:01 |
|* 11 |     INDEX FAST FULL SCAN          | PK_DAY_FORMATTED_MVIEW   |    44 |   352 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("dt">= (SELECT "T1"."DAY_ID" FROM  ( (SELECT "TIME_POINT_NAME"
            "TIME_POINT_NAME","DAY_ID" "DAY_ID" FROM "DYNAMIC_TIME_POINT_MV" "DYNAMIC_TIME_POINT_MV"
             WHERE "TIME_POINT_NAME"='DAILY-CURR_DT_MINUS_36_MONTHS') UNION ALL  (SELECT
             TO_CHAR(INTERNAL_FUNCTION("DAY_ID"),'mm/dd/yyyy') "TIME_POINT_NAME","DAY_ID" "DAY_ID" FROM
             "DAY_FORMATTED_MVIEW" "DAY_FORMATTED_MVIEW" WHERE
             TO_CHAR(INTERNAL_FUNCTION("DAY_ID"),'mm/dd/yyyy')='DAILY-CURR_DT_MINUS_36_MONTHS')) "T1") AND "dt"<= 
             (SELECT "T2"."DAY_ID" FROM  ( (SELECT "DAY_ID" "DAY_ID" FROM "DYNAMIC_TIME_POINT_MV"     
             "DYNAMIC_TIME_POINT_MV" WHERE "TIME_POINT_NAME"='DAILY-CURR_DT') UNION ALL  (SELECT "DAY_ID" "DAY_ID"
             FROM "DAY_FORMATTED_MVIEW" "DAY_FORMATTED_MVIEW" WHERE
             TO_CHAR(INTERNAL_FUNCTION("DAY_ID"),'mm/dd/yyyy')='DAILY-CURR_DT')) "T2"))    
   5 - access("TIME_POINT_NAME"='DAILY-CURR_DT_MINUS_36_MONTHS')    
   6 - filter(TO_CHAR(INTERNAL_FUNCTION("DAY_ID"),'mm/dd/yyyy')='DAILY-CURR_DT_MINUS_36_MONTHS')   
  10 - access("TIME_POINT_NAME"='DAILY-CURR_DT')   
  11 - filter(TO_CHAR(INTERNAL_FUNCTION("DAY_ID"),'mm/dd/yyyy')='DAILY-CURR_DT') 36 rows selected. 

Noticed how Oracle was able to access bigtable with the range from the inline view.

About these ads