메뉴 건너뛰기

bysql.net

1.6_내가_사용한_Hint가_무시되는_이유

ms 2011.09.05 20:44 조회 수 : 2345

"Query Transformation을 모르면 튜닝을 할 수 없다."는 주장에 대해 많은 이들이 말도 안 된다고 생각 할 것이다 Logical Optimizer 의 그 결과물인 Query Transformation 을 잘 알지 못했지만 지금껏 튜닝을 성공적으로 생각하는 사람이 많기 때문이다. 하지만 과연 그럴까.? 아래의 SQL 을 통해 Query Transformation 과 Logical Optimizer 가 얼마나 중요한지 알아보자!

 

 

======================================================================================

 

**준비** 

테스트를 위한 인덱스 뷰 3개와 뷰 하나를 만들자.

 

CREATE INDEX loc_postal_idx ON location ( postal_code );
CREATE INDEX dept_name_idx ON department ( department_name );
CREATE INDEX coun_region_idx ON country ( region_id );

create or replace view v_dept as
select d.department_id,d.department_name,d.manager_id,l.location_id,l.postal_code,
l.city,c.country_id,c.country_name,c.region_id
from department d,location l, country c
where d.location_id = l.location_id
and l.country_id = c.country_id;

select /*+ GATHER_PLAN_STATISTICS */
    e.employee_id,e.first_name,e.last_name,e.job_id,v.department_name
from employee e, v_dept v
where e.department_id = v.department_id
and v.department_name = 'Shipping'
and v.postal_code = '99236'
and v.region_id = 2
and e.job_id = 'ST_CLERK';

select * from
table ( DBMS_XPLAN.DISPLAY_CURSOR( null,null,'allstats last -rows +alias +outline +predicate') );

 

------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |      1 |     20 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS                   |                   |      1 |     20 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS                  |                   |      1 |     45 |00:00:00.01 |       9 |
|   3 |    NESTED LOOPS                 |                   |      1 |      1 |00:00:00.01 |       7 |
|   4 |     NESTED LOOPS                |                   |      1 |      1 |00:00:00.01 |       4 |
|   5 |      TABLE ACCESS BY INDEX ROWID| LOCATION          |      1 |      1 |00:00:00.01 |       3 |
|*  6 |       INDEX RANGE SCAN          | LOC_POSTAL_IDX    |      1 |      1 |00:00:00.01 |       2 |
|*  7 |      INDEX UNIQUE SCAN          | COUNTRY_C_ID_PK   |      1 |      1 |00:00:00.01 |       1 |
|*  8 |     TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      1 |      1 |00:00:00.01 |       3 |
|*  9 |      INDEX RANGE SCAN           | DEPT_NAME_IDX     |      1 |      1 |00:00:00.01 |       2 |
|* 10 |    INDEX RANGE SCAN             | EMP_DEPARTMENT_IX |      1 |     45 |00:00:00.01 |       2 |
|* 11 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE          |     45 |     20 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------

 

 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("L"."POSTAL_CODE"='99236')
   7 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")
       filter("C"."REGION_ID"=2)
   8 - filter("D"."LOCATION_ID"="L"."LOCATION_ID")
   9 - access("D"."DEPARTMENT_NAME"='Shipping')
  10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  11 - filter("E"."JOB_ID"='ST_CLERK')

 

  

**조인순서를 바꿀 수 있겠는가.**

위의 실행계획에서 조인순서 V_dept -> Employee 이다. 이 상태에서 여러분이 조인의 순서를 Employee -> V_depth 로 바꿀 수 있겠는가.? 아래와 같은 힌트를 사용한다.

 

select /*+ GATHER_PLAN_STATISTICS LEADING(E,V)*/
    e.employee_id,e.first_name,e.last_name,e.job_id,v.department_name
from employee e, v_dept v
where e.department_id = v.department_id
and v.department_name = 'Shipping'
and v.postal_code = '99236'
and v.region_id = 2
and e.job_id = 'ST_CLERK';

select * from
table ( DBMS_XPLAN.DISPLAY_CURSOR( null,null,'allstats last -rows +alias +outline +predicate') );

 

------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |      1 |     20 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS                   |                   |      1 |     20 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS                  |                   |      1 |     45 |00:00:00.01 |       9 |
|   3 |    NESTED LOOPS                 |                   |      1 |      1 |00:00:00.01 |       7 |
|   4 |     NESTED LOOPS                |                   |      1 |      1 |00:00:00.01 |       4 |
|   5 |      TABLE ACCESS BY INDEX ROWID| LOCATION          |      1 |      1 |00:00:00.01 |       3 |
|*  6 |       INDEX RANGE SCAN          | LOC_POSTAL_IDX    |      1 |      1 |00:00:00.01 |       2 |
|*  7 |      INDEX UNIQUE SCAN          | COUNTRY_C_ID_PK   |      1 |      1 |00:00:00.01 |       1 |
|*  8 |     TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      1 |      1 |00:00:00.01 |       3 |
|*  9 |      INDEX RANGE SCAN           | DEPT_NAME_IDX     |      1 |      1 |00:00:00.01 |       2 |
|* 10 |    INDEX RANGE SCAN             | EMP_DEPARTMENT_IX |      1 |     45 |00:00:00.01 |       2 |
|* 11 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE          |     45 |     20 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------

 

 

*힌트가 무시 되었다 원인은?**

조인의 순서가 전혀 변하지 않았다.

이상하지 않은가? 간단하게 생각되는 SQL의 조인순서도 변경할 수 없다.

원인은 Query Transformation 에 있다. Outline 정보를 보면 실마리를 찾을 수 있다.

 

 

Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1" "L"@"SEL$2" ("LOCATION"."POSTAL_CODE"))
      INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("COUNTRY"."COUNTRY_ID"))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("DEPARTMENT"."DEPARTMENT_NAME"))
      INDEX(@"SEL$F5BB74E1" "E"@"SEL$1" ("EMPLOYEE"."DEPARTMENT_ID"))
      LEADING(@"SEL$F5BB74E1" "L"@"SEL$2" "C"@"SEL$2" "D"@"SEL$2" "E"@"SEL$1")
      USE_NL(@"SEL$F5BB74E1" "C"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "E"@"SEL$1")
      NLJ_BATCHING(@"SEL$F5BB74E1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
 

 

**원인은 Logical Optimizer 에 의한 Query Transformation 이다.**

뷰 V_dept에 View Merging 이 발생한 것이다. View Merging은 Query Transformation의 한 종류이며 뷰를 해체하여 정상적인 조인으로 바꾸는 작업이다.

View Merging 과 같은 Query Transformation이 발생하면 많은 경우에 쿼리블럭명이 바뀌어 버린다. 실무에서 많은 DBA나 개발자들이 이 점을 놓치고 있어 힌트가 적용되지 않았다.

 

 

**해결방법**

Query Transformation에 의해 바뀐 쿼리블럭명을 지정하여 힌트를 사용하거나 Query Transformation 이 발생하지 않게 하면 힌트가 제대로 적용된다.

 

select /*+ GATHER_PLAN_STATISTICS NO_MERGET(V) LEADING(E,V)*/
    e.employee_id,e.first_name,e.last_name,e.job_id,v.department_name
from employee e, v_dept v
where e.department_id = v.department_id
and v.department_name = 'Shipping'
and v.postal_code = '99236'
and v.region_id = 2
and e.job_id = 'ST_CLERK';

select * from
table ( DBMS_XPLAN.DISPLAY_CURSOR( null,null,'allstats last -rows +alias +outline +predicate') );

 

 -----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Starts | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |      1 |     20 |00:00:00.01 |       9 |      1 |       |       |          |
|*  1 |  HASH JOIN                       |                 |      1 |     20 |00:00:00.01 |       9 |      1 |   786K|   786K|  343K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID    | EMPLOYEE        |      1 |     20 |00:00:00.01 |       2 |      1 |       |       |          |
|*  3 |    INDEX RANGE SCAN              | EMP_JOB_IX      |      1 |     20 |00:00:00.01 |       1 |      1 |       |       |          |
|   4 |   VIEW                           | V_DEPT          |      1 |      1 |00:00:00.01 |       7 |      0 |       |       |          |
|   5 |    NESTED LOOPS                  |                 |      1 |      1 |00:00:00.01 |       7 |      0 |       |       |          |
|   6 |     NESTED LOOPS                 |                 |      1 |      1 |00:00:00.01 |       6 |      0 |       |       |          |
|   7 |      NESTED LOOPS                |                 |      1 |      1 |00:00:00.01 |       4 |      0 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID| LOCATION        |      1 |      1 |00:00:00.01 |       3 |      0 |       |       |          |
|*  9 |        INDEX RANGE SCAN          | LOC_POSTAL_IDX  |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
|* 10 |       INDEX UNIQUE SCAN          | COUNTRY_C_ID_PK |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |
|* 11 |      INDEX RANGE SCAN            | DEPT_NAME_IDX   |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
|* 12 |     TABLE ACCESS BY INDEX ROWID  | DEPARTMENT      |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

 

**Query Transformation 이 발생하지 않으면 조인순서변경이 가능해**

No_merge 힌트를 사용하여 Query Transformation이 발생하지 않게 하였더니 조인 순서가 Employee -> V_dept로 바뀌었다. Query Transformation 과 Logical Optimizer를 모른다며 힌트 사용도 무용지물이 된다.이래서는 제대로 된 튜닝을 할 수 없다.

 

 

**뷰 내부의 테이블에 대한 조인순서의 변경은 가능한가.**

No_Merge 힌트를 사용한 상태에서 뷰 내부의 테이블들에 대해서 조인순서를 바꾸고 싶다. 즉 조인 순서를 Employee -> 뷰 (Country -> Location -> Department ) 로 바꾸어야 한다.

그럴 경우에는 Global Hint 를 사용하면 된다.

select /*+ GATHER_PLAN_STATISTICS NO_MERGE(V) LEADING(E,V) LEADING(V.C V.L V.D)*/
    e.employee_id,e.first_name,e.last_name,e.job_id,v.department_name
from employee e, v_dept v
where e.department_id = v.department_id
and v.department_name = 'Shipping'
and v.postal_code = '99236'
and v.region_id = 2
and e.job_id = 'ST_CLERK';

select * from
table ( DBMS_XPLAN.DISPLAY_CURSOR( null,null,'allstats last -rows +alias +outline +predicate') );

 

Leading 힌트를 두번 사용하였다. 그 이유는 위의 SQL이 쿼리블럭 2개로 구성되어있기 때문이다. 즉 전체 SQL에 대한 Leading 힌트가 필요하며 V_dept에 대한 Leading 힌트가 각각 필요하다. 힌트를 사용할 때 뷰 내부에 존재 하는 테이블들의 Alias를 사용해야 한다 Leading 힌트 내에 V.C V.L V.D로 사용했음을 유의 하라. 여기서 C,L,D는 해당 Object 의 Alias 이다. 이 방법은 Global Hint 사용방법 중에 Dot 표기법을 사용한 것이다.

 

 

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |      1 |     20 |00:00:00.01 |      11 |       |       |          |
|*  1 |  HASH JOIN                            |                 |      1 |     20 |00:00:00.01 |      11 |   786K|   786K|  343K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID         | EMPLOYEE        |      1 |     20 |00:00:00.01 |       2 |       |       |          |
|*  3 |    INDEX RANGE SCAN                   | EMP_JOB_IX      |      1 |     20 |00:00:00.01 |       1 |       |       |          |
|   4 |   VIEW                                | V_DEPT          |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|   5 |    NESTED LOOPS                       |                 |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|   6 |     NESTED LOOPS                      |                 |      1 |      1 |00:00:00.01 |       8 |       |       |          |
|   7 |      NESTED LOOPS                     |                 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|*  8 |       INDEX RANGE SCAN                | COUN_REGION_IDX |      1 |      5 |00:00:00.01 |       2 |       |       |          |
|   9 |       TABLE ACCESS BY INDEX ROWID     | LOCATION        |      5 |      1 |00:00:00.01 |       4 |       |       |          |
|  10 |        BITMAP CONVERSION TO ROWIDS    |                 |      5 |      1 |00:00:00.01 |       3 |       |       |          |
|  11 |         BITMAP AND                    |                 |      5 |      1 |00:00:00.01 |       3 |       |       |          |
|  12 |          BITMAP CONVERSION FROM ROWIDS|                 |      5 |      4 |00:00:00.01 |       2 |       |       |          |
|* 13 |           INDEX RANGE SCAN            | LOC_COUNTRY_IX  |      5 |      8 |00:00:00.01 |       2 |       |       |          |
|  14 |          BITMAP CONVERSION FROM ROWIDS|                 |      5 |      1 |00:00:00.01 |       1 |       |       |          |
|* 15 |           INDEX RANGE SCAN            | LOC_POSTAL_IDX  |      5 |      4 |00:00:00.01 |       1 |       |       |          |
|* 16 |      INDEX RANGE SCAN                 | DEPT_NAME_IDX   |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 17 |     TABLE ACCESS BY INDEX ROWID       | DEPARTMENT      |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

 

**Global Hint 는 매우 유용해**

조인순서가 Employee -> 뷰(Country -> Location -> Department ) 로 바뀌었다. Global Hint를 사용하자 힌트가 제대로 적용 되었다

이 방법은 특히 뷰나 인라인 뷰를 Control 할 때 유용하므로 반드시 익혀 두자

 

 

**Query Transformation이 발생한 경우 힌트를 어떻게 적용해야 하는가.**

이제 이번 장의 원래 목적인 Query Transformation 이 발생했을 경우에 조인순서를 바꾸는 방법에 대해 알아보자 위에서 배운 Global Hint를 여기에 적용할 것이다.

 

select /*+ GATHER_PLAN_STATISTICS LEADING(E V.C V.L V.D)*/
    e.employee_id,e.first_name,e.last_name,e.job_id,v.department_name
from employee e, v_dept v
where e.department_id = v.department_id
and v.department_name = 'Shipping'
and v.postal_code = '99236'
and v.region_id = 2
and e.job_id = 'ST_CLERK';

select * from
table ( DBMS_XPLAN.DISPLAY_CURSOR( null,null,'allstats last -rows +alias +outline +predicate') );

 

 위의 경우 Hint 가 적용 되었을까.?

 

select /*+ GATHER_PLAN_STATISTICS LEADING(E V.C V.L V.D)*/
    e.employee_id,e.first_name,e.last_name,e.job_id,v.department_name
from employee e, v_dept v
where e.department_id = v.department_id
and v.department_name = 'Shipping'
and v.postal_code = '99236'
and v.region_id = 2
and e.job_id = 'ST_CLERK';

select * from
table ( DBMS_XPLAN.DISPLAY_CURSOR( null,null,'allstats last -rows +alias +outline +predicate') );

 

 

**실패사례**

힌트가 적혀 적용 되지 않았다.

view Merging 이 발생하여 새로운 쿼리블럭이 생성되었기 때문이다.

 

"/" 기준으로 왼쪽이 쿼리블럭명이고 오른쪽이 각 테이블의 Alias 이다.

제일 왼쪽 숫자는 실행계획상의 id와 일치한다.

 

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   5 - SEL$F5BB74E1 / L@SEL$2
   6 - SEL$F5BB74E1 / L@SEL$2
   7 - SEL$F5BB74E1 / C@SEL$2
   8 - SEL$F5BB74E1 / D@SEL$2
   9 - SEL$F5BB74E1 / D@SEL$2
  10 - SEL$F5BB74E1 / E@SEL$1
  11 - SEL$F5BB74E1 / E@SEL$1

 

 

**힌트에 쿼리블럭명과 Object 의 Alias를 사용해야 가능함**

 쿼리 블럭명은 SEL$F5BB74E1이며 Object의 Alias들은 D@SEL$2, L@SEL$2, C@SEL$2, E@SEL$1 임을 알 수 있다.

 

select /*+ GATHER_PLAN_STATISTICS LEADING(@SEL$F5BB74E1 E@SEL$1 C@SEL$2
 L@SEL$2 D@SEL$2 )*/
    e.employee_id,e.first_name,e.last_name,e.job_id,v.department_name
from employee e, v_dept v
where e.department_id = v.department_id
and v.department_name = 'Shipping'
and v.postal_code = '99236'
and v.region_id = 2
and e.job_id = 'ST_CLERK';

 

쿼리 블럭명을 처음에 지정 /  그 뒤에는 조인될 순서대로 Object Alias 를 배치하기만 하면 된다. 

 
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |      1 |     20 |00:00:00.01 |      12 |       |       |          |
|   1 |  NESTED LOOPS                   |                  |      1 |     20 |00:00:00.01 |      12 |       |       |          |
|   2 |   NESTED LOOPS                  |                  |      1 |     20 |00:00:00.01 |      10 |       |       |          |
|*  3 |    HASH JOIN                    |                  |      1 |     20 |00:00:00.01 |       6 |   776K|   776K|  696K (0)|
|   4 |     MERGE JOIN CARTESIAN        |                  |      1 |    100 |00:00:00.01 |       3 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      1 |     20 |00:00:00.01 |       2 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | EMP_JOB_IX       |      1 |     20 |00:00:00.01 |       1 |       |       |          |
|   7 |      BUFFER SORT                |                  |     20 |    100 |00:00:00.01 |       1 |  2048 |  2048 | 2048  (0)|
|*  8 |       INDEX RANGE SCAN          | COUN_REGION_IDX  |      1 |      5 |00:00:00.01 |       1 |       |       |          |
|   9 |     TABLE ACCESS BY INDEX ROWID | LOCATION         |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 10 |      INDEX RANGE SCAN           | LOC_POSTAL_IDX   |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 11 |    INDEX RANGE SCAN             | DEPT_LOCATION_IX |     20 |     20 |00:00:00.01 |       4 |       |       |          |
|* 12 |   TABLE ACCESS BY INDEX ROWID   | DEPARTMENT       |     20 |     20 |00:00:00.01 |       2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

 

왜 책과 같은 결과 값이 나오지 않을까..?

 

조인 순서가 Employee -> country -> Location -> Department 로 바뀌었다.

이 방법은 Global hint 사용 방법 중에 쿼리블럭 표기법을 사용한 것이다.

이 방법은 특히 View merging 과 같은 Query Transformation 이 발생하여 새로 생성된 경우 적용하면 매우 유용하다.

 

 

**Global Hint는 위험한가.**

Global Hint 관련하여 SQL을 다시 실행할 경우에 쿼리블럭이 바꾸지 않을까 걱정하는 사람들이있다.

하지만 나중에 다시 실행 한다고 해서 쿼리 블럭이 바뀌지 않으며 바뀌는 경우는 SQL이 변경 되거나 통계정보 등이 변경 되어 Plan 이 변경되는 경우 이다.  하지만 이 경우는 일반적인 힌트를 사용한다고 해도 적용되지 않는다 왜냐면 SQL이 변경되거나 통계정보가 바뀌어서 Query Transformation 이 발생한다면 쿼리블럭명이 변경 되므로 일반적인 힌트도 적용되지 않는다.

따라서 쿼리 블럭 표기법이 특별히 더 위험한 것이 아니다.


쿼리브럭 표기법으로 모든 힌트를 사용할 수 있다.

아래의 Outline Data는 이런점을 잘 설명해 준다.

 

Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("COUNTRY"."REGION_ID"))
      BITMAP_TREE(@"SEL$F5BB74E1" "L"@"SEL$2" AND(("LOCATION"."COUNTRY_ID") ("LOCATION"."POSTAL_CODE")))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("DEPARTMENT"."DEPARTMENT_NAME"))
      INDEX(@"SEL$F5BB74E1" "E"@"SEL$1" ("EMPLOYEE"."DEPARTMENT_ID"))
      LEADING(@"SEL$F5BB74E1" "C"@"SEL$2" "L"@"SEL$2" "D"@"SEL$2" "E"@"SEL$1")
      USE_NL(@"SEL$F5BB74E1" "L"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "E"@"SEL$1")
      NLJ_BATCHING(@"SEL$F5BB74E1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */


위의 정보는 오라클이 내부적으로 사용한 힌트의 내용이다

역시 Global Hint로 쿼리블럭명과 Objet Alias 를 사용하고 있다.


뷰내부의 테이블에 대해서만 조인 순서를 바꾸고 싶다면 Query Transformation 이 발생한다고 해도 Dot 표기법을 사용할 수 있다.  아래의 SQL 이 이런 특징을 잘 나타내고 있다.


select /*+ GATHER_PLAN_STATISTICS LEADING(V.C V.L V.D)*/   
e.employee_id,e.first_name,e.last_name,e.job_id,v.department_name from
employee e, v_dept v where e.department_id = v.department_id and
v.department_name = 'Shipping' and v.postal_code = '99236' and
v.region_id = 2 and e.job_id = 'ST_CLERK'


--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |     20 |00:00:00.01 |      14 |      6 |
|   1 |  NESTED LOOPS                        |                   |      1 |     20 |00:00:00.01 |      14 |      6 |
|   2 |   NESTED LOOPS                       |                   |      1 |     45 |00:00:00.01 |      11 |      6 |
|   3 |    NESTED LOOPS                      |                   |      1 |      1 |00:00:00.01 |       9 |      6 |
|   4 |     NESTED LOOPS                     |                   |      1 |      1 |00:00:00.01 |       6 |      4 |
|*  5 |      INDEX RANGE SCAN                | COUN_REGION_IDX   |      1 |      5 |00:00:00.01 |       2 |      1 |
|   6 |      TABLE ACCESS BY INDEX ROWID     | LOCATION          |      5 |      1 |00:00:00.01 |       4 |      3 |
|   7 |       BITMAP CONVERSION TO ROWIDS    |                   |      5 |      1 |00:00:00.01 |       3 |      2 |
|   8 |        BITMAP AND                    |                   |      5 |      1 |00:00:00.01 |       3 |      2 |
|   9 |         BITMAP CONVERSION FROM ROWIDS|                   |      5 |      4 |00:00:00.01 |       2 |      1 |
|* 10 |          INDEX RANGE SCAN            | LOC_COUNTRY_IX    |      5 |      8 |00:00:00.01 |       2 |      1 |
|  11 |         BITMAP CONVERSION FROM ROWIDS|                   |      5 |      1 |00:00:00.01 |       1 |      1 |
|* 12 |          INDEX RANGE SCAN            | LOC_POSTAL_IDX    |      5 |      4 |00:00:00.01 |       1 |      1 |
|* 13 |     TABLE ACCESS BY INDEX ROWID      | DEPARTMENT        |      1 |      1 |00:00:00.01 |       3 |      2 |
|* 14 |      INDEX RANGE SCAN                | DEPT_NAME_IDX     |      1 |      1 |00:00:00.01 |       2 |      1 |
|* 15 |    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |      1 |     45 |00:00:00.01 |       2 |      0 |
|* 16 |   TABLE ACCESS BY INDEX ROWID        | EMPLOYEE          |     45 |     20 |00:00:00.01 |       3 |      0 |
--------------------------------------------------------------------------------------------------------------------

 

뷰 내부의 조인 순서가 바뀌었다.

이 방법 역시 하나의 쿼리블럭 내에서만 적용할 경우는 어디든 사요이 가능하낟.


Query Transformation 이 발생하면 힌트가 정상적으로 적용되지 않는다.

이것이 Logical Optimizer와 그 결과물인 Query Transformation 을 반드시 알아야 하는 이유중 하나이다.


이런 경우 힌트를 적용하려면 Query Transformation 발생 하지 않게 하거나 쿼리블럭명을 이용한 Global Hint를 사용하면 된다.