새로쓴 대용량 데이터베이스솔루션 1 (2011년)
Hints for Access Paths
0
0
83,215
by 구루비스터디 Hint 힌트 FULL CLUSTER HASH INDEX INDEX_ASC INDEX_DESC INDEX_COMBINE [2023.10.13]
Hints for Access Paths
FULL
SELECT /*+ FULL(T1) */ * FROM T1 WHERE N3 = 50;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 10 | 140 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SELECT * FROM T1 WHERE N3 = 50;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 140 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1N3 | 100 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
CLUSTER
SELECT /*+ FULL(T5) */ * FROM T5 WHERE T5.N4 = 0;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T5 | 1 | 14 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
SELECT /*+ CLUSTER(T5) */ * FROM T5 WHERE T5.N4 = 0;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS CLUSTER| T5 | 1 | 14 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | H2N4 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
HASH
SELECT /*+ FULL(T4) */ * FROM T4 WHERE T4.N4 = 0;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 14 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
SELECT /*+ HASH(T4) */ * FROM T4 WHERE T4.N4 = 0;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS HASH| T4 | 1 | 14 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
INDEX
SELECT /*+ INDEX(T1) */ * FROM T1 WHERE N2 > 0;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 140 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1N2 | 10000 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
SELECT /*+ INDEX(T1 T1N2) */ * FROM T1 WHERE N2 > 0;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 140 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1N2 | 9990 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
SELECT /*+ INDEX(T1 T1N3) */ * FROM T1 WHERE N2 > 0;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 140 | 1 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | T1N3 | 10000 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
NO_INDEX
SELECT /*+ NO_INDEX(T1) */ * FROM T1 WHERE N2 > 0;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 10 | 140 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
SELECT /*+ NO_INDEX(T1 T1N2) */ * FROM T1 WHERE N2 > 0;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 10 | 140 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
SELECT /*+ NO_INDEX(T1 T1N3) */ * FROM T1 WHERE N2 > 0;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 140 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1N2 | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
INDEX_ASC(=INDEX)
SELECT /*+ INDEX_ASC(T1 T1N2) */ * FROM T1 WHERE N2 > 0;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 140 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1N2 | 9990 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
INDEX_DESC
SELECT /*+ INDEX_DESC(T1 T1N2) */ * FROM T1 WHERE N2 > 0;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 10 | 140 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| T1N2 | 9990 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
INDEX_COMBINE
SELECT /*+ INDEX_COMBINE(T1 T1N3 T1N4) */ * FROM T1 WHERE N3 = 50 AND N4 = 5;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 10 | 140 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | T1N3 | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | T1N4 | | | 1 (0)| 00:00:01 |
INDEX_JOIN
SELECT /*+ INDEX_JOIN(T1 T1N3 T1N4) */ * FROM T1 WHERE N3 = 50 AND N4 = 5;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 140 | 17 (12)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 10 | 140 | 17 (12)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | HASH JOIN | | | | | |
|* 4 | HASH JOIN | | | | | |
|* 5 | INDEX RANGE SCAN | T1N3 | 10 | 140 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1N4 | 10 | 140 | 1 (0)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN| T1N2 | 10 | 140 | 7 (0)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN | T1PK | 10 | 140 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
INDEX_FFS
SELECT /*+ INDEX_FFS(T1) */ COUNT(N3) FROM T1;
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T1N3 | 10000 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------
NO_INDEX_FFS
SELECT /*+ NO_INDEX_FFS(T1) */ COUNT(N3) FROM T1;
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| T1N3 | 10000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------
INDEX_SS
SELECT /*+ NO_INDEX(T1 T1N3) INDEX_SS(T1) */ COUNT(*) FROM T1 WHERE N3 = 50;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX SKIP SCAN| T1N4N3 | 100 | 300 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
NO_INDEX_SS
SELECT /*+ NO_INDEX(T1 T1N3) NO_INDEX_SS(T1) */ COUNT(*) FROM T1 WHERE N3 = 50;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| T1N4N3 | 100 | 300 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------
INDEX_SS_ASC
SELECT /*+ NO_INDEX(T1 T1N3) INDEX_SS_ASC(T1) */ COUNT(*) FROM T1 WHERE N3 = 50;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX SKIP SCAN| T1N4N3 | 100 | 300 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
INDEX_SS_DESC
SELECT /*+ NO_INDEX(T1 T1N3) INDEX_SS_DESC(T1) */ COUNT(*) FROM T1 WHERE N3 = 50;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX SKIP SCAN DESCENDING| T1N4N3 | 100 | 300 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
"구루비 데이터베이스 스터디모임" 에서 2011년에 "새로쓴 대용량 데이터베이스 솔루션1" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4445
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.