Insertion

Query

Execution plan

Retrieval

Query

SELECT 
/*+ QB_NAME(MAIN) INDEX(COOL_C2@MAIN ("CHANNEL_ID")) 
    INDEX(COOL_I3@MAIN ("CHANNEL_ID" "IOV_SINCE" "IOV_UNTIL")) 
    LEADING(COOL_C2@MAIN COOL_I3@MAIN) USE_NL(COOL_I3@MAIN) 
    INDEX(@MAX1 COOL_I1@MAX1 ("CHANNEL_ID" "IOV_SINCE" "IOV_UNTIL"))  */ 
    COOL_I3."OBJECT_ID", COOL_I3."CHANNEL_ID", 
    COOL_I3."IOV_SINCE", COOL_I3."IOV_UNTIL", 
    COOL_I3."USER_TAG_ID", COOL_I3."SYS_INSTIME", 
    COOL_I3."LASTMOD_DATE", COOL_I3."ORIGINAL_ID", 
    COOL_I3."NEW_HEAD_ID", COOL_I3."I" 
FROM COOL_TEST_CLIENT."N6086250_F0001_CHANNELS" COOL_C2, 
    COOL_TEST_CLIENT."N6086250_F0001_IOVS" COOL_I3 
WHERE COOL_I3."CHANNEL_ID"=COOL_C2."CHANNEL_ID" 
AND COOL_I3."IOV_SINCE">=COALESCE(
   ( SELECT /*+ QB_NAME(MAX1)  */ 
     MAX(COOL_I1."IOV_SINCE") 
     FROM COOL_TEST_CLIENT.N6086250_F0001_IOVS COOL_I1 
     WHERE COOL_I1."CHANNEL_ID"=COOL_C2."CHANNEL_ID" 
     AND COOL_I1."IOV_SINCE"<=:"since1" ),
   :"sinc3s") 
AND COOL_I3."IOV_SINCE"<=:"until3" 
AND COOL_I3."IOV_UNTIL">:"sinc3u" 
ORDER BY COOL_I3."CHANNEL_ID" ASC, COOL_I3."IOV_SINCE" ASC
 

Execution plan

-------------------------------------------------------------------------+-----------------------------------+
| Id  | Operation                         | Name                         | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                  |                              |       |       |   153 |           |
| 1   |  SORT ORDER BY                    |                              |     1 |    94 |   153 |  00:00:02 |
| 2   |   TABLE ACCESS BY INDEX ROWID     | N6086250_F0001_IOVS          |   249 |   22K |    71 |  00:00:01 |
| 3   |    NESTED LOOPS                   |                              |   249 |   23K |    72 |  00:00:01 |
| 4   |     INDEX FULL SCAN               | N6086250_F0001_CHANNELS_PK   |     1 |     3 |     1 |  00:00:01 |
| 5   |      SORT AGGREGATE               |                              |     1 |     7 |       |           |
| 6   |       FIRST ROW                   |                              |  4977 |   34K |     2 |  00:00:01 |
| 7   |        INDEX RANGE SCAN (MIN/MAX) | N6086250_F0001_IOVS_CSU_3INDX|  4977 |   34K |     2 |  00:00:01 |
| 8   |     INDEX RANGE SCAN              | N6086250_F0001_IOVS_CSU_3INDX|   254 |       |     2 |  00:00:01 |
| 9   |      SORT AGGREGATE               |                              |     1 |     7 |       |           |
| 10  |       FIRST ROW                   |                              |  4977 |   34K |     2 |  00:00:01 |
| 11  |        INDEX RANGE SCAN (MIN/MAX) | N6086250_F0001_IOVS_CSU_3INDX|  4977 |   34K |     2 |  00:00:01 |
-------------------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - filter(COALESCE(,:sinc3s)<=:until3)
7 - access("COOL_I1"."CHANNEL_ID"=:B1 AND "COOL_I1"."IOV_SINCE"<=:since1)
8 - access("COOL_I3"."CHANNEL_ID"="COOL_C2"."CHANNEL_ID" AND "COOL_I3"."IOV_SINCE">=COALESCE(,:sinc3s) AND "COOL_I3"."IOV_UNTIL">:sinc3u AND "COOL_I3"."IOV_SINCE"<=:until3 AND "COOL_I3"."IOV_UNTIL" IS NOT NULL)
8 - filter("COOL_I3"."IOV_UNTIL">:sinc3u)
11 - access("COOL_I1"."CHANNEL_ID"=:B1 AND "COOL_I1"."IOV_SINCE"<=:since1)
 
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"MAX1")
      OUTLINE_LEAF(@"MAIN")
      OUTLINE(@"MAX1")
      OUTLINE(@"MAIN")
      INDEX(@"MAIN" "COOL_C2"@"MAIN" ("N6086250_F0001_CHANNELS"."CHANNEL_ID"))
      INDEX(@"MAIN" "COOL_I3"@"MAIN" ("N6086250_F0001_IOVS"."CHANNEL_ID" "N6086250_F0001_IOVS"."IOV_SINCE" "N6086250_F0001_IOVS"."IOV_UNTIL"))
      LEADING(@"MAIN" "COOL_C2"@"MAIN" "COOL_I3"@"MAIN")
      USE_NL(@"MAIN" "COOL_I3"@"MAIN")
      INDEX(@"MAX1" "COOL_I1"@"MAX1" ("N6086250_F0001_IOVS"."CHANNEL_ID" "N6086250_F0001_IOVS"."IOV_SINCE" "N6086250_F0001_IOVS"."IOV_UNTIL"))
    END_OUTLINE_DATA
  */
 

-- RomainBasset - 04 Nov 2008