오래된 이야기 이지만, 사용할 일이 그리 있을까 했으나
역시 사용을 할 수 있는 기회가 되어서 잊을듯 하여 올려 봅니다.
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')
-----------------------------------------------------------
*
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 * 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"(+))
-------------------------------------------------------
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 |
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------
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 |
-----------------------------------------------------------------------------------------------------------------------------------------
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 |
-----------------------------------------------------------------------------------------------------------------------------------------
혹시 수정 사항이 있으시면 이야기 해 주세요.
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 10 |
events 활용 (10384,10046,10053 ) MOS 기준
[2]  | 우뽕 | 2023.04.06 | 660 |
| 9 | 튜닝시 SQL 문장의 OutLine 정보를 추출하는 SQL 문장 | 명품관 | 2021.01.14 | 35586 |
| 8 | SQL Advisor 사용법을 이용한 튜닝 가이드 | 우뽕 | 2020.04.09 | 3519 |
| 7 | [12cR1 이상] Limiting SQL Rows 활용하여 페이징 처리 해 보기 [1] | 우뽕 | 2020.03.27 | 2068 |
| 6 | DB 유저가 DBMS_XPLAN 패키기 사용시 필요한 권한 | 명품관 | 2020.03.20 | 1329 |
| 5 | DBMS_SHARED_POOL 사용 | 우뽕 | 2020.03.11 | 1014 |
| » | Column 를 이용한 hint 사용하기 [1] | 우뽕 | 2020.02.28 | 1972 |
| 3 |
_smu_debug_mode 와_undo_autotune 관련 내용 정리
| 우뽕 | 2020.01.23 | 1241 |
| 2 | [12cR2 이상] _cursor_obsolete_threshold 기본값으로 변경 하기 | 우뽕 | 2020.01.23 | 1382 |
| 1 | V$SQL_HINT를 통해 HINT 종류 확인 | 명품관 | 2019.05.29 | 778 |
code highlight 써서 편집 좀 했습니다.