메뉴 건너뛰기

Korea Oracle User Group

Tuning

Column 를 이용한 hint 사용하기

우뽕 2020.02.28 17:34 조회 수 : 1700

오래된 이야기 이지만, 사용할 일이 그리 있을까 했으나

역시 사용을 할 수 있는 기회가 되어서 잊을듯 하여 올려 봅니다.

 

 Index Column 힌트라는 말을 사용 하기도 하고  ( 검색을 하면 나오네요. ) 

 메뉴얼을 찾아 보았으나 못 찾음점 양해 부탁 드리며.

  -> 찾으신분은 링크 부탁 드립니다.

 

왜 이것을 사용하게 되었나가 중요한 포인트 입니다.

물론 가상 인덱스를 사용해도 됩니다. ( 이것 또한 저도 별로 사용 안해 봤어요 )

 

그러나 그것보다 좀더 간단한 방법을 이용하는 방법을 찾고자 

테스트를 해 보았습니다.

 

전제조건은 

 : 테이블에 물리적으로 인덱스가 있어야 하고 반드시 조인조건에 인덱스 컬럼이 한개라도 걸려 있어야 하네요.

  

사용방법

  index(테이블명, 물리적인인덱스명(컬럼명, 컬럼명)) 

  

 

  

 테스트 수행 

   기본적인 테이블은 존재 하고 인덱스는 아래와 같습니다.

 

 테스트 버전 

   12.1 버전 EE 입니다.

 

 

CREATE UNIQUE INDEX OOADM.PK_T2

ON T2 (EMP_NO) 

;

 

CREATE UNIQUE INDEX IX_T1

ON T1 (LOGIN_ID) 

;

 

CREATE UNIQUE INDEX PK_T1

ON T1 (USER_ID) ;

 

SELECT 
    *                
FROM  T1 A 
    LEFT OUTER JOIN T2 B 
     ON A.EMP_NO  = B.EMP_NO
    WHERE  A.USE_YN  = '1'

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1K Card=14K Bytes=6M)
   1    0   HASH JOIN (OUTER) (Cost=1K Card=14K Bytes=6M)
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=239 Card=14K Bytes=3M)
   3    1     TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=473 Card=51K Bytes=11M)
-----------------------------------------------------------


Predicate information (identified by operation id):
-----------------------------------------------------------
   1 - access("A"."EMP_NO"="B"."EMP_NO"(+))
   2 - filter("A"."USE_YN"='1')
-----------------------------------------------------------

 

 

 

== dbms_xplan 에서도 아래와 같습니다.

SQL_ID  5uxf03cdh8cf4, child number 0
-------------------------------------
SELECT      *                 FROM  T1 A      LEFT OUTER JOIN T2 B      
 ON A.EMP_NO  = B.EMP_NO     WHERE  A.USE_YN  = '1'

Plan hash value: 1823443478

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |       |  1441 (100)|          |  18992 |00:00:00.11 |    2607 |    255 |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      1 |  13771 |  5930K|  3040K|  1441   (1)| 00:00:01 |  18992 |00:00:00.11 |    2607 |    255 |  6606K|  1995K|     1/0/0|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |  13771 |  2877K|       |   239   (1)| 00:00:01 |  18992 |00:00:00.02 |     870 |     98 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  51412 |    11M|       |   473   (1)| 00:00:01 |  51412 |00:00:00.05 |    1737 |    157 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

 

~~~~ 나머지 아래는 생략 합니다.

 

 

SELECT  /*+ ORDERED 
            USE_NL(B A)
            INDEX(A PK_T1( USER_ID  ,EMP_NO ,USE_YN ))
            INDEX(B  PK_T2(EMP_NO, ORG_CD))

         */
         *                
FROM  T1 A 
    LEFT OUTER JOIN T2 B 
     ON A.EMP_NO  = B.EMP_NO
    WHERE  A.USE_YN  = '1'

*************************[Explain Plan Time: 2020/02/28 17:16:51]*************************
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18K Card=14K Bytes=6M)
   1    0   NESTED LOOPS (OUTER) (Cost=18K Card=14K Bytes=6M)
   2    1     TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'T1' (TABLE) (Cost=5K Card=14K Bytes=3M)
   3    2       INDEX (FULL SCAN) OF 'PK_T1' (INDEX (UNIQUE)) (Cost=63 Card=28K)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1 Card=1 Bytes=227)
   5    4       INDEX (UNIQUE SCAN) OF 'OOADM.PK_T2' (INDEX (UNIQUE)) (Cost=0 Card=1)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter("A"."USE_YN"='1')
   5 - access("A"."EMP_NO"="B"."EMP_NO"(+))
-------------------------------------------------------

 

 

 

SQL_ID  0gmgwyr6w3a0c, child number 0
-------------------------------------
SELECT  /*+ ORDERED                  USE_NL(B A)                      
INDEX(A PK_T1( USER_ID  ,EMP_NO ,USE_YN ))                       
INDEX(B  PK_T2(EMP_NO, ORG_CD))                 */                 *    
             FROM  T1 A      LEFT OUTER JOIN T2 B       ON A.EMP_NO  = 
B.EMP_NO     WHERE  A.USE_YN  = '1'

Plan hash value: 500316873

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |      1 |        |       | 18385 (100)|          |  18992 |00:00:00.10 |   35435 |    154 |
|   1 |  NESTED LOOPS OUTER                  |       |      1 |  13771 |  5930K| 18385   (1)| 00:00:01 |  18992 |00:00:00.10 |   35435 |    154 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |  13771 |  2877K|  5080   (1)| 00:00:01 |  18992 |00:00:00.04 |    5111 |     63 |
|   3 |    INDEX FULL SCAN                   | PK_T1 |      1 |  27543 |       |    63   (0)| 00:00:01 |  27543 |00:00:00.02 |      82 |     63 |
|   4 |   TABLE ACCESS BY INDEX ROWID        | T2    |  18992 |      1 |   227 |     1   (0)| 00:00:01 |  15873 |00:00:00.06 |   30324 |     91 |
|*  5 |    INDEX UNIQUE SCAN                 | PK_T2 |  18992 |      1 |       |     0   (0)|          |  15873 |00:00:00.04 |   14451 |     91 |
-------------------------------------------------------------------------------------------------------------------------------------------------

 

 

 ==> 인덱스 삭제 합니다. 

DROP INDEX PK_T1;

 

 

SELECT  /*+ ORDERED 

                USE_NL(B A)

                     INDEX(A  PK_T1( USER_ID  ,EMP_NO ,USE_YN ))

                      INDEX(B  PK_T2(EMP_NO, ORG_CD))

                */

                *                

FROM  T1 A 

    LEFT OUTER JOIN T2 B 

     ON A.EMP_NO  = B.EMP_NO

    WHERE  A.USE_YN  = '1'

 

 

*************************[Explain Plan Time: 2020/02/28 17:20:58]*************************
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14K Card=14K Bytes=6M)
   1    0   NESTED LOOPS (OUTER) (Cost=14K Card=14K Bytes=6M)
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=239 Card=14K Bytes=3M)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1 Card=1 Bytes=227)

   4    3       INDEX (UNIQUE SCAN) OF 'OOADM.PK_T2' (INDEX (UNIQUE)) (Cost=0 Card=1)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter("A"."USE_YN"='1')
   4 - access("A"."EMP_NO"="B"."EMP_NO"(+))
-----------------------------------------------------------


SQL_ID  bp6v5vu28ac3g, child number 0
-------------------------------------
SELECT  /*+ ORDERED                  USE_NL(B A)                      
INDEX(A  PK_T1( USER_ID  ,EMP_NO ,USE_YN ))                       
INDEX(B  PK_T2(EMP_NO, ORG_CD))                 */                 *    
             FROM  T1 A      LEFT OUTER JOIN T2 B       ON A.EMP_NO  = 
B.EMP_NO     WHERE  A.USE_YN  = '1'

Plan hash value: 3081498522

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |       | 13544 (100)|          |  18992 |00:00:00.27 |   31990 |    619 |
|   1 |  NESTED LOOPS OUTER          |       |      1 |  13771 |  5930K| 13544   (1)| 00:00:01 |  18992 |00:00:00.27 |   31990 |    619 |
|*  2 |   TABLE ACCESS FULL          | T1    |      1 |  13771 |  2877K|   239   (1)| 00:00:01 |  18992 |00:00:00.05 |     889 |    144 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2    |  18992 |      1 |   227 |     1   (0)| 00:00:01 |  15873 |00:00:00.21 |   31101 |    475 |
|*  4 |    INDEX UNIQUE SCAN         | PK_T2 |  18992 |      1 |       |     0   (0)|          |  15873 |00:00:00.05 |   15228 |    119 |
-----------------------------------------------------------------------------------------------------------------------------------------
 

 

 

 

혹시 수정 사항이 있으시면 이야기 해 주세요.

 

위로