[Oracle] SubQuery Hint

NO_UNNEST	서브쿼리를 FILTER동작방식으로 처리하고 싶을 경우, 서브쿼리에 NO_UNNEST 힌트를 사용
UNNEST	FILTER 동작방식을 선택하지 않고 조인 동작방식으로 처리하고자 할 때. 서브쿼리에 UNNEST 힌트 사용
NL_SJ	EXISTS나 IN조건 사용시 서브쿼리에 UNNEST와 함께 NL_SJ힌트를 사용하면, NESTED LOOPS JOIN SEMI로 처리되도록 유도
HASH_SJ	EXISTS나 IN조건을 사용한 경우 서브쿼리에 UNNEST와 함께 HASH_SJ 힌트를 부여하면 HASH JOIN SEMI로 처리하도록 제어
NL_AJ	NOT EXISTS나 NOT IN 조건을 사용한 경우 서브쿼리에 UNNEST와 함께 NL_AJ 힌트를 사용하면, NESTED LOOPS JOIN ANTI로 처리하도록 제어
HASH_AJ	NOT EXISTS나 NOT IN 조건을 사용한 경우 서브쿼리에 UNNEST와 함께 HASH_AJ 힌트를 사용하면 HASH JOIN ANTI로 처리하도록 제어
ORDERED	FROM절에 나열된 순서대로 수행하도록 조인 순서를 정하는 힌트. 서브쿼리가 존재한다면 서브쿼리가 가장 먼저 수행
QB_NAME	QUERY BLOCK의 이름을 지정
SWAP_JOIN_INPUTS	HASH JOIN시 조인 순서를 변경 가능. 명시된 테이블이 BUILD 테이블이 됨
NO_SWAP_JOIN_INPUTS	HASH JOIN시 조인 순서가 바뀌는 경우, 이를 강제적으로 변경되지 못하도록 제어
PUSH_SUBQ	서브쿼리가 먼저 수행하도록 제어. FILTER로 수행됨

 

--### 1. NO_UNNEST 사용 ###
메인쿼리가 항상 Driving 테이블로 사용됨 
서브쿼리의 filter가 가장나중 

SELECT /*+ leading( b a ) use_nl( b a ) index( b PK_SUB_SETTLMNT ) */
       b.mid AS id,
       b.settlmnt_dt AS settlmnt_dt,
       b.app_cnt AS app_cnt,
       b.app_amt AS app_amt,
       b.cc_cnt AS cc_cnt,
       b.cc_amt AS cc_amt,
       b.fee AS fee,
       b.vat AS vat,
       b.deposit_amt AS deposit_amt,
       nvl(b.coupon_amt, 0) coupon_amt
  FROM payment.tb_merchant a, payment.tb_sub_settlmnt b
 WHERE a.mid = b.mid
   AND b.settlmnt_dt BETWEEN substr(:settlmnt_dt, 1, 6) || '01' AND :settlmnt_dt
   AND NOT EXISTS (SELECT /*+ no_unnest */ 1                              
                     FROM payment.tb_id_group g
                    WHERE a.gid = g.gid
                      AND g.settlmnt_limit_use_cl = '1') ;


SQL_ID  4jv29rbu4vqww, child number 0
-------------------------------------
SELECT /*+ leading( b a ) use_nl( b a ) index( b PK_SUB_SETTLMNT ) */   
     b.mid AS id,        b.settlmnt_dt AS settlmnt_dt,        b.app_cnt 
AS app_cnt,        b.app_amt AS app_amt,        b.cc_cnt AS cc_cnt,     
   b.cc_amt AS cc_amt,        b.fee AS fee,        b.vat AS vat,        
b.deposit_amt AS deposit_amt,        nvl(b.coupon_amt, 0) coupon_amt   
FROM payment.tb_merchant a, payment.tb_sub_settlmnt b  WHERE a.mid = 
b.mid    AND b.settlmnt_dt BETWEEN substr(:settlmnt_dt, 1, 6) || '01' 
AND :settlmnt_dt    AND NOT EXISTS (SELECT /*+ no_unnest */ 1           
                                         FROM payment.tb_id_group g     
                WHERE a.gid = g.gid                       AND 
g.settlmnt_limit_use_cl = '1')
 
Plan hash value: 3806745586
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |        |       |   164K(100)|          |
|*  1 |  FILTER                         |                 |        |       |            |          |
|*  2 |   FILTER                        |                 |        |       |            |          |
|   3 |    NESTED LOOPS                 |                 |  27645 |  1997K| 97997   (1)| 00:00:04 |
|   4 |     NESTED LOOPS                |                 |  27645 |  1997K| 97997   (1)| 00:00:04 |
|   5 |      TABLE ACCESS BY INDEX ROWID| TB_SUB_SETTLMNT |  27645 |  1403K| 42694   (1)| 00:00:02 |
|*  6 |       INDEX RANGE SCAN          | PK_SUB_SETTLMNT |  49760 |       |   372   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN          | PK_MERCHANT     |      1 |       |     1   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID | TB_MERCHANT     |      1 |    22 |     2   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY INDEX ROWID   | TB_ID_GROUP     |      1 |    13 |     3   (0)| 00:00:01 |
|* 10 |    INDEX UNIQUE SCAN            | PK_ID_GROUP     |      1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   5 - SEL$1 / B@SEL$1
   6 - SEL$1 / B@SEL$1
   7 - SEL$1 / A@SEL$1
   8 - SEL$1 / A@SEL$1
   9 - SEL$2 / G@SEL$2
  10 - SEL$2 / G@SEL$2
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_optimizer_unnest_scalar_sq' 'false')
      OPT_PARAM('_optimizer_gather_stats_on_load' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_batch_table_access_by_rowid' 'false')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      OPT_PARAM('_optimizer_gather_stats_on_load_index' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("TB_SUB_SETTLMNT"."SETTLMNT_DT" "TB_SUB_SETTLMNT"."MID" 
              "TB_SUB_SETTLMNT"."SVC_CD" "TB_SUB_SETTLMNT"."SVC_PRDT_CD" "TB_SUB_SETTLMNT"."TR_DT" 
              "TB_SUB_SETTLMNT"."TRANS_TYPE" "TB_SUB_SETTLMNT"."SETTLMNT_CYCLE" 
              "TB_SUB_SETTLMNT"."SETTLMNT_SVC"))
      INDEX(@"SEL$1" "A"@"SEL$1" ("TB_MERCHANT"."MID"))
      LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
      USE_NL(@"SEL$1" "A"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "A"@"SEL$1")
      PQ_FILTER(@"SEL$1" SERIAL)
      INDEX_RS_ASC(@"SEL$2" "G"@"SEL$2" ("TB_ID_GROUP"."GID"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( IS NULL)
   2 - filter(:SETTLMNT_DT>=SUBSTR(:SETTLMNT_DT,1,6)||'01')
   6 - access("B"."SETTLMNT_DT">=SUBSTR(:SETTLMNT_DT,1,6)||'01' AND 
              "B"."SETTLMNT_DT"<=:SETTLMNT_DT)
   7 - access("A"."MID"="B"."MID")
   9 - filter("G"."SETTLMNT_LIMIT_USE_CL"='1')
  10 - access("G"."GID"=:B1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "B"."MID"[VARCHAR2,10], "B"."SETTLMNT_DT"[VARCHAR2,8], "B"."APP_CNT"[NUMBER,22], 
       "B"."APP_AMT"[NUMBER,22], "B"."CC_CNT"[NUMBER,22], "B"."CC_AMT"[NUMBER,22], 
       "B"."FEE"[NUMBER,22], "B"."VAT"[NUMBER,22], "B"."DEPOSIT_AMT"[NUMBER,22], 
       "B"."COUPON_AMT"[NUMBER,22]
   2 - "B"."MID"[VARCHAR2,10], "B"."SETTLMNT_DT"[VARCHAR2,8], "B"."APP_CNT"[NUMBER,22], 
       "B"."APP_AMT"[NUMBER,22], "B"."CC_CNT"[NUMBER,22], "B"."CC_AMT"[NUMBER,22], 
       "B"."FEE"[NUMBER,22], "B"."VAT"[NUMBER,22], "B"."DEPOSIT_AMT"[NUMBER,22], 
       "B"."COUPON_AMT"[NUMBER,22], "A"."GID"[VARCHAR2,10]
   3 - "B"."MID"[VARCHAR2,10], "B"."SETTLMNT_DT"[VARCHAR2,8], "B"."APP_CNT"[NUMBER,22], 
       "B"."APP_AMT"[NUMBER,22], "B"."CC_CNT"[NUMBER,22], "B"."CC_AMT"[NUMBER,22],

--### 2. NO_UNNEST + push_sudbq 사용 ###

SELECT /*+ leading( b a ) use_nl( b a ) index( b PK_SUB_SETTLMNT ) */
       b.mid AS id,
       b.settlmnt_dt AS settlmnt_dt,
       b.app_cnt AS app_cnt,
       b.app_amt AS app_amt,
       b.cc_cnt AS cc_cnt,
       b.cc_amt AS cc_amt,
       b.fee AS fee,
       b.vat AS vat,
       b.deposit_amt AS deposit_amt,
       nvl(b.coupon_amt, 0) coupon_amt
  FROM payment.tb_merchant a, payment.tb_sub_settlmnt b
 WHERE a.mid = b.mid
   AND b.settlmnt_dt BETWEEN substr(:settlmnt_dt, 1, 6) || '01' AND :settlmnt_dt
   AND NOT EXISTS (SELECT /*+ no_unnest push_subq */ 1                              
                     FROM payment.tb_id_group g
                    WHERE a.gid = g.gid
                      AND g.settlmnt_limit_use_cl = '1') ;


SQL_ID  ajjdkmp8dbzfv, child number 0
-------------------------------------
SELECT /*+ leading( b a ) use_nl( b a ) index( b PK_SUB_SETTLMNT ) */   
     b.mid AS id,        b.settlmnt_dt AS settlmnt_dt,        b.app_cnt 
AS app_cnt,        b.app_amt AS app_amt,        b.cc_cnt AS cc_cnt,     
   b.cc_amt AS cc_amt,        b.fee AS fee,        b.vat AS vat,        
b.deposit_amt AS deposit_amt,        nvl(b.coupon_amt, 0) coupon_amt   
FROM payment.tb_merchant a, payment.tb_sub_settlmnt b  WHERE a.mid = 
b.mid    AND b.settlmnt_dt BETWEEN substr(:settlmnt_dt, 1, 6) || '01' 
AND :settlmnt_dt    AND NOT EXISTS (SELECT /*+ no_unnest push_subq */ 1 
                                                   FROM 
payment.tb_id_group g                     WHERE a.gid = g.gid           
            AND g.settlmnt_limit_use_cl = '1')
 
Plan hash value: 1486693091
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |        |       | 98000 (100)|          |
|*  1 |  FILTER                        |                 |        |       |            |          |
|   2 |   NESTED LOOPS                 |                 |  13448 |   971K| 97997   (1)| 00:00:04 |
|   3 |    NESTED LOOPS                |                 |  27645 |   971K| 97997   (1)| 00:00:04 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TB_SUB_SETTLMNT |  27645 |  1403K| 42694   (1)| 00:00:02 |
|*  5 |      INDEX RANGE SCAN          | PK_SUB_SETTLMNT |  49760 |       |   372   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | PK_MERCHANT     |      1 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | TB_MERCHANT     |      1 |    22 |     2   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS BY INDEX ROWID| TB_ID_GROUP     |      1 |    13 |     3   (0)| 00:00:01 |
|*  9 |      INDEX UNIQUE SCAN         | PK_ID_GROUP     |      1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   4 - SEL$1 / B@SEL$1
   5 - SEL$1 / B@SEL$1
   6 - SEL$1 / A@SEL$1
   7 - SEL$1 / A@SEL$1
   8 - SEL$2 / G@SEL$2
   9 - SEL$2 / G@SEL$2
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_optimizer_unnest_scalar_sq' 'false')
      OPT_PARAM('_optimizer_gather_stats_on_load' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_batch_table_access_by_rowid' 'false')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      OPT_PARAM('_optimizer_gather_stats_on_load_index' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("TB_SUB_SETTLMNT"."SETTLMNT_DT" "TB_SUB_SETTLMNT"."MID" 
              "TB_SUB_SETTLMNT"."SVC_CD" "TB_SUB_SETTLMNT"."SVC_PRDT_CD" "TB_SUB_SETTLMNT"."TR_DT" 
              "TB_SUB_SETTLMNT"."TRANS_TYPE" "TB_SUB_SETTLMNT"."SETTLMNT_CYCLE" 
              "TB_SUB_SETTLMNT"."SETTLMNT_SVC"))
      INDEX(@"SEL$1" "A"@"SEL$1" ("TB_MERCHANT"."MID"))
      LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
      USE_NL(@"SEL$1" "A"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "A"@"SEL$1")
      PUSH_SUBQ(@"SEL$2")
      INDEX_RS_ASC(@"SEL$2" "G"@"SEL$2" ("TB_ID_GROUP"."GID"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(:SETTLMNT_DT>=SUBSTR(:SETTLMNT_DT,1,6)||'01')
   5 - access("B"."SETTLMNT_DT">=SUBSTR(:SETTLMNT_DT,1,6)||'01' AND 
              "B"."SETTLMNT_DT"<=:SETTLMNT_DT)
   6 - access("A"."MID"="B"."MID")
   7 - filter( IS NULL)
   8 - filter("G"."SETTLMNT_LIMIT_USE_CL"='1')
   9 - access("G"."GID"=:B1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "B"."MID"[VARCHAR2,10], "B"."SETTLMNT_DT"[VARCHAR2,8], "B"."APP_CNT"[NUMBER,22], 
       "B"."APP_AMT"[NUMBER,22], "B"."CC_CNT"[NUMBER,22], "B"."CC_AMT"[NUMBER,22], 
       "B"."FEE"[NUMBER,22], "B"."VAT"[NUMBER,22], "B"."DEPOSIT_AMT"[NUMBER,22], 
       "B"."COUPON_AMT"[NUMBER,22]
   2 - "B"."MID"[VARCHAR2,10], "B"."SETTLMNT_DT"[VARCHAR2,8], "B"."APP_CNT"[NUMBER,22], 
       "B"."APP_AMT"[NUMBER,22], "B"."CC_CNT"[NUMBER,22], "B"."CC_AMT"[NUMBER,22], 
       "B"."FEE"[NUMBER,22], "B"."VAT"[NUMBER,22], "B"."DEPOSIT_AMT"[NUMBER,22], 
       "B"."COUPON_AMT"[NUMBER,22]
   3 - "B"."MID"[VARCHAR2,10], "B"."SETTLMNT_DT"[VARCHAR2,8], "B"."APP_CNT"[NUMBER,22], 
       "B"."APP_AMT"[NUMBER,22], "B"."CC_CNT"[NUMBER,22], "B"."CC_AMT"[NUMBER,22], 
       "B"."FEE"[NUMBER,22], "B"."VAT"[NUMBER,22], "B"."DEPOSIT_AMT"[NUMBER,22],

       -

-- ## 3. unnest + qb_name 으로 조인 순서 제어 ##

SELECT /*+ leading(g@idg b a ) use_nl( b a ) index( b PK_SUB_SETTLMNT ) */
       b.mid AS id,
       b.settlmnt_dt AS settlmnt_dt,
       b.app_cnt AS app_cnt,
       b.app_amt AS app_amt,
       b.cc_cnt AS cc_cnt,
       b.cc_amt AS cc_amt,
       b.fee AS fee,
       b.vat AS vat,
       b.deposit_amt AS deposit_amt,
       nvl(b.coupon_amt, 0) coupon_amt
  FROM payment.tb_merchant a, payment.tb_sub_settlmnt b
 WHERE a.mid = b.mid
   AND b.settlmnt_dt BETWEEN substr(:settlmnt_dt, 1, 6) || '01' AND :settlmnt_dt
   AND NOT EXISTS (SELECT /*+ qb_name(idg) unnest  */ 1                              
                     FROM payment.tb_id_group g
                    WHERE a.gid = g.gid
                      AND g.settlmnt_limit_use_cl = '1') ;


SQL_ID  43htmfmy1vj3k, child number 0
-------------------------------------
SELECT /*+ leading(g@idg b a ) use_nl( b a ) index( b PK_SUB_SETTLMNT ) 
*/        b.mid AS id,        b.settlmnt_dt AS settlmnt_dt,        
b.app_cnt AS app_cnt,        b.app_amt AS app_amt,        b.cc_cnt AS 
cc_cnt,        b.cc_amt AS cc_amt,        b.fee AS fee,        b.vat AS 
vat,        b.deposit_amt AS deposit_amt,        nvl(b.coupon_amt, 0) 
coupon_amt   FROM payment.tb_merchant a, payment.tb_sub_settlmnt b  
WHERE a.mid = b.mid    AND b.settlmnt_dt BETWEEN substr(:settlmnt_dt, 
1, 6) || '01' AND :settlmnt_dt    AND NOT EXISTS (SELECT /*+ 
qb_name(idg) unnest  */ 1                                               
     FROM payment.tb_id_group g                     WHERE a.gid = g.gid 
                      AND g.settlmnt_limit_use_cl = '1')
 
Plan hash value: 4164379371
 
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |        |       | 99996 (100)|          |       |       |          |
|*  1 |  FILTER                         |                 |        |       |            |          |       |       |          |
|*  2 |   HASH JOIN RIGHT ANTI          |                 |  27645 |  2348K| 99996   (1)| 00:00:04 |  3843K|  3843K| 5320K (0)|
|*  3 |    TABLE ACCESS STORAGE FULL    | TB_ID_GROUP     |      1 |    13 |  2000   (1)| 00:00:01 |  1025K|  1025K|          |
|   4 |    NESTED LOOPS                 |                 |  27645 |  1997K| 97997   (1)| 00:00:04 |       |       |          |
|   5 |     NESTED LOOPS                |                 |  27645 |  1997K| 97997   (1)| 00:00:04 |       |       |          |
|   6 |      TABLE ACCESS BY INDEX ROWID| TB_SUB_SETTLMNT |  27645 |  1403K| 42694   (1)| 00:00:02 |       |       |          |
|*  7 |       INDEX RANGE SCAN          | PK_SUB_SETTLMNT |  49760 |       |   372   (0)| 00:00:01 |  1025K|  1025K|          |
|*  8 |      INDEX UNIQUE SCAN          | PK_MERCHANT     |      1 |       |     1   (0)| 00:00:01 |  1025K|  1025K|          |
|   9 |     TABLE ACCESS BY INDEX ROWID | TB_MERCHANT     |      1 |    22 |     2   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$FB4C6525
   3 - SEL$FB4C6525 / G@IDG
   6 - SEL$FB4C6525 / B@SEL$1
   7 - SEL$FB4C6525 / B@SEL$1
   8 - SEL$FB4C6525 / A@SEL$1
   9 - SEL$FB4C6525 / A@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_optimizer_unnest_scalar_sq' 'false')
      OPT_PARAM('_optimizer_gather_stats_on_load' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_batch_table_access_by_rowid' 'false')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      OPT_PARAM('_optimizer_gather_stats_on_load_index' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$FB4C6525")
      UNNEST(@"IDG")
      OUTLINE(@"SEL$1")
      OUTLINE(@"IDG")
      INDEX_RS_ASC(@"SEL$FB4C6525" "B"@"SEL$1" ("TB_SUB_SETTLMNT"."SETTLMNT_DT" "TB_SUB_SETTLMNT"."MID" 
              "TB_SUB_SETTLMNT"."SVC_CD" "TB_SUB_SETTLMNT"."SVC_PRDT_CD" "TB_SUB_SETTLMNT"."TR_DT" "TB_SUB_SETTLMNT"."TRANS_TYPE" 
              "TB_SUB_SETTLMNT"."SETTLMNT_CYCLE" "TB_SUB_SETTLMNT"."SETTLMNT_SVC"))
      INDEX(@"SEL$FB4C6525" "A"@"SEL$1" ("TB_MERCHANT"."MID"))
      FULL(@"SEL$FB4C6525" "G"@"IDG")
      LEADING(@"SEL$FB4C6525" "B"@"SEL$1" "A"@"SEL$1" "G"@"IDG")
      USE_NL(@"SEL$FB4C6525" "A"@"SEL$1")
      NLJ_BATCHING(@"SEL$FB4C6525" "A"@"SEL$1")
      USE_HASH(@"SEL$FB4C6525" "G"@"IDG")
      SWAP_JOIN_INPUTS(@"SEL$FB4C6525" "G"@"IDG")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(:SETTLMNT_DT>=SUBSTR(:SETTLMNT_DT,1,6)||'01')
   2 - access("A"."GID"="G"."GID")
   3 - storage("G"."SETTLMNT_LIMIT_USE_CL"='1')
       filter("G"."SETTLMNT_LIMIT_USE_CL"='1')
   7 - access("B"."SETTLMNT_DT">=SUBSTR(:SETTLMNT_DT,1,6)||'01' AND "B"."SETTLMNT_DT"<=:SETTLMNT_DT)
   8 - access("A"."MID"="B"."MID")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "B"."MID"[VARCHAR2,10], "B"."SETTLMNT_DT"[VARCHAR2,8], "B"."APP_CNT"[NUMBER,22], "B"."APP_AMT"[NUMBER,22], 
       "B"."CC_CNT"[NUMBER,22], "B"."CC_AMT"[NUMBER,22], "B"."FEE"[NUMBER,22], "B"."VAT"[NUMBER,22], 
       "B"."DEPOSIT_AMT"[NUMBER,22], "B"."COUPON_AMT"[NUMBER,22]
   2 - (#keys=1) "B"."MID"[VARCHAR2,10], "B"."SETTLMNT_DT"[VARCHAR2,8], "B"."APP_CNT"[NUMBER,22], 
       "B"."APP_AMT"[NUMBER,22], "B"."CC_CNT"[NUMBER,22], "B"."CC_AMT"[NUMBER,22], "B"."FEE"[NUMBER,22], 
       "B"."VAT"[NUMBER,22], "B"."DEPOSIT_AMT"[NUMBER,22], "B"."COUPON_AMT"[NUMBER,22]
   3 - (rowset=256) "G"."GID"[VARCHAR2,10]
   4 - "B"."MID"[VARCHAR2,10], "B"."SETTLMNT_DT"[VARCHAR2,8], "B"."APP_CNT"[NUMBER,22], "B"."APP_AMT"[NUMBER,22], 
       "B"."CC_CNT"[NUMBER,22], "B"."CC_AMT"[NUMBER,22], "B"."FEE"[NUMBER,22], "B"."VAT"[NUMBER,22], 
       "B"."DEPOSIT_AMT"[NUMBER,22], "B"."COUPON_AMT"[NUMBER,22], "A"."GID"[VARCHAR2,10]
   5 - "B"."MID"[VARCHAR2,10], "B"."SETTLMNT_DT"[VARCHAR2,8], "B"."APP_CNT"[NUMBER,22], "B"."APP_AMT"[NUMBER,22],

 

 

push_pred : 조인 조건 pushdown기능 메인 쿼리를 실행 하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능. 실행계획에 나타난 'VIEW PUSHED PREDCATE' 오퍼레이션을 통해 이 기능의 작동 여부를

알 수 있다.

​

select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래 from 고객 c . ( select /*+ no_merge push_pred */ 고객번호, avg(거래금액) 평균거래 , min(거래금액) 최소거래, max(거래금액) 최대거래 from 거래 where 거래일시 >= trunc(sysdate, 'mm') group by 고객번호 ) t where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm') and t.고객번호 = c.고객번호