[NO_EXPAND]OR확장, Concatenation을 막는 힌트

[NO_EXPAND]OR확장, Concatenation을 막는 힌트

Hints For Access Paths(NO_EXPAND)

 CBO(COST BASED Optimizer) 모드에서 OR 조건이나 IN List등을 사용할 때 OR확장 (Concatenation등을)을 막는 것인데 실행 계획에 Concatenation등이 보이는 경우 이것이 일어나지 않도록 처리해 준다.

[형식]
/*+ NO_EXPAND */

실습을 위해 먼저 옵티마이저 모드를 RULE로 바꾸는데 그 이유는, EMP 테이블의 경우 데이터 양이 적으므로 OR를 사용하더라도 FULL SCAN하는 실행계획을 만들어 내므로 고의로 CONCATENATION을 만들어 내기 위해 RULE BASED Optimizer Mode로 변경하는 것이다.

alter session set optimizer_mode=rule;

SELECT ename, sal
FROM  EMP
WHERE  JOB  = 'CLERK'
OR    JOB  = 'SALESMAN'

-----------------------------------------------------------------
Operation Object Name Rows Bytes Cost
-----------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=RULE  
  CONCATENATION        
    TABLE ACCESS BY INDEX ROWID SCOTT.EMP   INDEX RANGE SCAN SCOTT.IDX_EMP_JOB  
    TABLE ACCESS BY INDEX ROWID SCOTT.EMP  
      INDEX RANGE SCAN SCOTT.IDX_EMP_JOB  

SELECT /*+ NO_EXPAND */
      ENAME, SAL
FROM  EMP
WHERE  JOB  = 'CLERK'
OR    JOB  = 'SALESMAN'

Operation Object Name Rows Bytes Cost
-------------------------------------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=RULE 6  2 
  INLIST ITERATOR        
    TABLE ACCESS BY INDEX ROWID SCOTT.EMP 6  96  2 
      INDEX RANGE SCAN SCOTT.IDX_EMP_JOB 6  1 

-- myemp1 테이블의 SAL, DEPTNO 칼럼은 각각 인덱스가 있다. CBO모드에서 실행하면… INLIST  ITERATOR로 풀린다.

 SQL> select count(*) from myemp1
  where sal > 5000000
  and  deptno in ('1','3');

  COUNT(*)
----------
  1500000

경  과: 00:00:00.21

Execution Plan
-----------------------------------------------------------------------
| Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)
-----------------------------------------------------------------------
|  0 | SELECT STATEMENT  |                      |    1 |    8 |  4404  (1)
|  1 |  SORT AGGREGATE    |                      |    1 |    8 |
|  2 |  INLIST ITERATOR  |                      |      |      |
|*  3 |    INDEX RANGE SCAN| IDX_MYEMP1_DEPTNO_SAL |  1644K|    12M| 


-- 이번에는 Concatenation으로 출기위해 RBO MODE로 실행하자.

SQL> alter session set optimizer_mode = rule;

SQL> select  count(*) from myemp1
  where sal > 5000000
  and  deptno in ('1','3');

  COUNT(*)
----------
  1500000

한참 걸린다.

Execution Plan
----------------------------------------------------------
Plan hash value: 3544225264

-----------------------------------------------------------
| Id  | Operation                    | Name              |
-----------------------------------------------------------
|  0 | SELECT STATEMENT              |                  |
|  1 |  SORT AGGREGATE              |                  |
|  2 |  CONCATENATION              |                  |
|*  3 |    TABLE ACCESS BY INDEX ROWID| MYEMP1            |
|*  4 |    INDEX RANGE SCAN          | IDX_MYEMP1_DEPTNO |
|*  5 |    TABLE ACCESS BY INDEX ROWID| MYEMP1            |
|*  6 |    INDEX RANGE SCAN          | IDX_MYEMP1_DEPTNO |
-----------------------------------------------------------

-- 이번에는 RBO 상태에서 NO_EXPAND 힌트를 사용해 보자.
SQL> alter session set optimizer_mode = rule;
세션이 변경되었습니다.

SQL>  select /*+ no_expand */  count(*) from myemp1
  where sal > 5000000
    and  deptno in ('1','3');

  COUNT(*)
----------
  1500000

경  과: 00:00:00.09

INLIST ITERATOR로 풀리면서 좋은 성능을 낸다. 

[출처] 오라클자바커뮤니티 - http://ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=218