oracle hint 위치별 적용여부 테스트

ㆍ 힌트없음

ㆍ 일반적인 인덱스 힌트(정상)

ㆍ 괄호안 공백 대신 콤마 사용(정상)

ㆍ 2개 테이블 힌트 사용(정상)

ㆍ 힌트 구문 2개 사용(앞에것만 적용됨)

ㆍ 한개의 index 힌트에 여러 테이블 사용(앞에것만 적용됨 tab1_ix01)

ㆍ 한개의 index 힌트에 여러 테이블 사용(앞에것만 적용됨 tab1_ix02)

ㆍ 순서를 바꾸니 모든 힌트 무효(E - Syntax error 메세지)

ㆍ 서브쿼리 힌트 없음

ㆍ 서브쿼리 힌트 사용(정상)

DBMS : oracle 19.3

힌트없음

 SQL> SELECT
* 
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_id = b.object_id
AND a.owner = 'SK'
;  
 
---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |  2705 |   697K|   790   (1)| 00:00:01 |
|*  1 |  HASH JOIN	   |	  |  2705 |   697K|   790   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TAB1 |  2705 |   348K|   395   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TAB2 | 73045 |  9415K|   396   (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OWNER"='SK')

일반적인 인덱스 힌트(정상)

 SQL> SELECT /*+ index(a tab1_ix01) */
* 
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_id = b.object_id
AND a.owner = 'SK'
;
 
--------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |		 |  2705 |   697K| 37452   (1)| 00:00:02 |
|*  1 |  HASH JOIN			     |		 |  2705 |   697K| 37452   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1	 |  2705 |   348K| 37056   (1)| 00:00:02 |
|   3 |    INDEX FULL SCAN		     | TAB1_IX01 | 73044 |	 |   514   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL		     | TAB2	 | 73045 |  9415K|   396   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OWNER"='SK')

괄호안 공백 대신 콤마 사용(정상)

 SQL> SELECT /*+ index(a,tab1_ix01) */
* 
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_id = b.object_id
AND a.owner = 'SK'
; 
 
--------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |		 |  2705 |   697K| 37452   (1)| 00:00:02 |
|*  1 |  HASH JOIN			     |		 |  2705 |   697K| 37452   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1	 |  2705 |   348K| 37056   (1)| 00:00:02 |
|   3 |    INDEX FULL SCAN		     | TAB1_IX01 | 73044 |	 |   514   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL		     | TAB2	 | 73045 |  9415K|   396   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OWNER"='SK')

2개 테이블 힌트 사용(정상)

 SQL> SELECT /*+ index(a tab1_ix01) index(b tab2_ix01) */
* 
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_id = b.object_id
AND a.owner = 'SK'
;
 
--------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |		 |  2705 |   697K| 74113   (1)| 00:00:03 |
|*  1 |  HASH JOIN			     |		 |  2705 |   697K| 74113   (1)| 00:00:03 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1	 |  2705 |   348K| 37056   (1)| 00:00:02 |
|   3 |    INDEX FULL SCAN		     | TAB1_IX01 | 73044 |	 |   514   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB2	 | 73045 |  9415K| 37057   (1)| 00:00:02 |
|   5 |    INDEX FULL SCAN		     | TAB2_IX01 | 73045 |	 |   514   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OWNER"='SK')

힌트 구문 2개 사용(앞에것만 적용됨)

 SQL> SELECT /*+ index(a tab1_ix01) *//*+ index(b tab2_ix01) */
* 
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_id = b.object_id
AND a.owner = 'SK'
;
 
--------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |		 |  2705 |   697K| 37452   (1)| 00:00:02 |
|*  1 |  HASH JOIN			     |		 |  2705 |   697K| 37452   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1	 |  2705 |   348K| 37056   (1)| 00:00:02 |
|   3 |    INDEX FULL SCAN		     | TAB1_IX01 | 73044 |	 |   514   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL		     | TAB2	 | 73045 |  9415K|   396   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OWNER"='SK')

한개의 index 힌트에 여러 테이블 사용(앞에것만 적용됨 tab1_ix01)

 SQL> SELECT /*+ index(a tab1_ix01 b tab2_ix01) */
* 
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_id = b.object_id
AND a.owner = 'SK'
;
 
--------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |		 |  2705 |   697K| 37452   (1)| 00:00:02 |
|*  1 |  HASH JOIN			     |		 |  2705 |   697K| 37452   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1	 |  2705 |   348K| 37056   (1)| 00:00:02 |
|   3 |    INDEX FULL SCAN		     | TAB1_IX01 | 73044 |	 |   514   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL		     | TAB2	 | 73045 |  9415K|   396   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OWNER"='SK')
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
 
   2 -	SEL$1 / A@SEL$1
	 U -  index(a tab1_ix01 b tab2_ix01)
	 U -  index(a tab1_ix01 b tab2_ix01)

한개의 index 힌트에 여러 테이블 사용(앞에것만 적용됨 tab1_ix02)

 SQL> SELECT /*+ index(a tab1_ix02 b tab2_ix01) */
* 
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_id = b.object_id
AND a.owner = 'SK'
;
 
--------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |		 |  2705 |   697K|  2552   (1)| 00:00:01 |
|*  1 |  HASH JOIN			     |		 |  2705 |   697K|  2552   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1	 |  2705 |   348K|  2156   (1)| 00:00:01 |
|   3 |    INDEX FULL SCAN		     | TAB1_IX02 | 73044 |	 |   513   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL		     | TAB2	 | 73045 |  9415K|   396   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OWNER"='SK')
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
 
   2 -	SEL$1 / A@SEL$1
	 U -  index(a tab1_ix02 b tab2_ix01)
	 U -  index(a tab1_ix02 b tab2_ix01)

순서를 바꾸니 모든 힌트 무효(E - Syntax error 메세지)

 SQL> SELECT /*+ index(b tab2_ix01 a tab1_ix01 ) */
* 
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_id = b.object_id
AND a.owner = 'SK'
;
 
---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |  2705 |   697K|   790   (1)| 00:00:01 |
|*  1 |  HASH JOIN	   |	  |  2705 |   697K|   790   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TAB1 |  2705 |   348K|   395   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TAB2 | 73045 |  9415K|   396   (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OWNER"='SK')
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
 
   1 -	SEL$1
	 E -  index(b

서브쿼리 힌트 없음

 SQL> SELECT 
* 
FROM sk.tab1 a
WHERE object_id in (SELECT object_id FROM sk.tab2 b)
;
 
------------------------------------------------------------------------------------
| Id  | Operation	       | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	   | 73040 |  9771K|   761   (1)| 00:00:01 |
|*  1 |  HASH JOIN	       |	   | 73040 |  9771K|   761   (1)| 00:00:01 |
|   2 |   SORT UNIQUE	       |	   | 73045 |   356K|   140   (0)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| TAB2_IX01 | 73045 |   356K|   140   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL    | TAB1	   | 73044 |  9415K|   396   (1)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("OBJECT_ID"="OBJECT_ID")

서브쿼리 힌트 사용(정상)

 SQL> SELECT /*+ index(a tab1_ix01) */
* 
FROM sk.tab1 a
WHERE object_id in (SELECT /*+ index(b tab2_ix02) */ object_id FROM sk.tab2 b)
;
 
--------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |		 | 73040 |  9771K| 37794   (1)| 00:00:02 |
|*  1 |  HASH JOIN			     |		 | 73040 |  9771K| 37794   (1)| 00:00:02 |
|   2 |   SORT UNIQUE			     |		 | 73045 |   356K|   513   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN		     | TAB2_IX02 | 73045 |   356K|   513   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1	 | 73044 |  9415K| 37056   (1)| 00:00:02 |
|   5 |    INDEX FULL SCAN		     | TAB1_IX01 | 73044 |	 |   514   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("OBJECT_ID"="OBJECT_ID")

'DBMS > SQL튜닝' 카테고리의 다른 글

oracle ABS 함수, 절대값을 구하는 함수 (0)	2023.07.18
oracle hint 적용 안되는 경우 확인 및 해결 법 (0)	2023.07.07
ORDER BY 컬럼명이 가리키는 컬럼은? (0)	2023.07.03
펑션 위치별 실행 횟 수 (0)	2023.07.02
INLIST ITERATOR 성능개선(where 절 in 조건) (0)	2023.06.29

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

oracle hint 위치별 적용여부 테스트

힌트없음

일반적인 인덱스 힌트(정상)

괄호안 공백 대신 콤마 사용(정상)

2개 테이블 힌트 사용(정상)

힌트 구문 2개 사용(앞에것만 적용됨)

한개의 index 힌트에 여러 테이블 사용(앞에것만 적용됨 tab1_ix01)

한개의 index 힌트에 여러 테이블 사용(앞에것만 적용됨 tab1_ix02)

순서를 바꾸니 모든 힌트 무효(E - Syntax error 메세지)

서브쿼리 힌트 없음

서브쿼리 힌트 사용(정상)

'DBMS > SQL튜닝' 카테고리의 다른 글

댓글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

	SQL> SELECT
	*
	FROM sk.tab1 a, sk.tab2 b
	WHERE a.object_id = b.object_id
	AND a.owner = 'SK'
	;

	---------------------------------------------------------------------------
	\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
	---------------------------------------------------------------------------
	\| 0 \| SELECT STATEMENT \| \| 2705 \| 697K\| 790 (1)\| 00:00:01 \|
	\|* 1 \| HASH JOIN \| \| 2705 \| 697K\| 790 (1)\| 00:00:01 \|
	\|* 2 \| TABLE ACCESS FULL\| TAB1 \| 2705 \| 348K\| 395 (1)\| 00:00:01 \|
	\| 3 \| TABLE ACCESS FULL\| TAB2 \| 73045 \| 9415K\| 396 (1)\| 00:00:01 \|
	---------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
	2 - filter("A"."OWNER"='SK')

	SQL> SELECT /+ index(a tab1_ix01) /
	*
	FROM sk.tab1 a, sk.tab2 b
	WHERE a.object_id = b.object_id
	AND a.owner = 'SK'
	;

	--------------------------------------------------------------------------------------------------
	\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
	--------------------------------------------------------------------------------------------------
	\| 0 \| SELECT STATEMENT \| \| 2705 \| 697K\| 37452 (1)\| 00:00:02 \|
	\|* 1 \| HASH JOIN \| \| 2705 \| 697K\| 37452 (1)\| 00:00:02 \|
	\|* 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| TAB1 \| 2705 \| 348K\| 37056 (1)\| 00:00:02 \|
	\| 3 \| INDEX FULL SCAN \| TAB1_IX01 \| 73044 \| \| 514 (0)\| 00:00:01 \|
	\| 4 \| TABLE ACCESS FULL \| TAB2 \| 73045 \| 9415K\| 396 (1)\| 00:00:01 \|
	--------------------------------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
	2 - filter("A"."OWNER"='SK')

	SQL> SELECT /+ index(a,tab1_ix01) /
	*
	FROM sk.tab1 a, sk.tab2 b
	WHERE a.object_id = b.object_id
	AND a.owner = 'SK'
	;

	--------------------------------------------------------------------------------------------------
	\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
	--------------------------------------------------------------------------------------------------
	\| 0 \| SELECT STATEMENT \| \| 2705 \| 697K\| 37452 (1)\| 00:00:02 \|
	\|* 1 \| HASH JOIN \| \| 2705 \| 697K\| 37452 (1)\| 00:00:02 \|
	\|* 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| TAB1 \| 2705 \| 348K\| 37056 (1)\| 00:00:02 \|
	\| 3 \| INDEX FULL SCAN \| TAB1_IX01 \| 73044 \| \| 514 (0)\| 00:00:01 \|
	\| 4 \| TABLE ACCESS FULL \| TAB2 \| 73045 \| 9415K\| 396 (1)\| 00:00:01 \|
	--------------------------------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
	2 - filter("A"."OWNER"='SK')

	SQL> SELECT /+ index(a tab1_ix01) index(b tab2_ix01) /
	*
	FROM sk.tab1 a, sk.tab2 b
	WHERE a.object_id = b.object_id
	AND a.owner = 'SK'
	;

	--------------------------------------------------------------------------------------------------
	\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
	--------------------------------------------------------------------------------------------------
	\| 0 \| SELECT STATEMENT \| \| 2705 \| 697K\| 74113 (1)\| 00:00:03 \|
	\|* 1 \| HASH JOIN \| \| 2705 \| 697K\| 74113 (1)\| 00:00:03 \|
	\|* 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| TAB1 \| 2705 \| 348K\| 37056 (1)\| 00:00:02 \|
	\| 3 \| INDEX FULL SCAN \| TAB1_IX01 \| 73044 \| \| 514 (0)\| 00:00:01 \|
	\| 4 \| TABLE ACCESS BY INDEX ROWID BATCHED\| TAB2 \| 73045 \| 9415K\| 37057 (1)\| 00:00:02 \|
	\| 5 \| INDEX FULL SCAN \| TAB2_IX01 \| 73045 \| \| 514 (0)\| 00:00:01 \|
	--------------------------------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
	2 - filter("A"."OWNER"='SK')

	SQL> SELECT /+ index(a tab1_ix01) //+ index(b tab2_ix01) /
	*
	FROM sk.tab1 a, sk.tab2 b
	WHERE a.object_id = b.object_id
	AND a.owner = 'SK'
	;

	--------------------------------------------------------------------------------------------------
	\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
	--------------------------------------------------------------------------------------------------
	\| 0 \| SELECT STATEMENT \| \| 2705 \| 697K\| 37452 (1)\| 00:00:02 \|
	\|* 1 \| HASH JOIN \| \| 2705 \| 697K\| 37452 (1)\| 00:00:02 \|
	\|* 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| TAB1 \| 2705 \| 348K\| 37056 (1)\| 00:00:02 \|
	\| 3 \| INDEX FULL SCAN \| TAB1_IX01 \| 73044 \| \| 514 (0)\| 00:00:01 \|
	\| 4 \| TABLE ACCESS FULL \| TAB2 \| 73045 \| 9415K\| 396 (1)\| 00:00:01 \|
	--------------------------------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
	2 - filter("A"."OWNER"='SK')

	SQL> SELECT /+ index(a tab1_ix01 b tab2_ix01) /
	*
	FROM sk.tab1 a, sk.tab2 b
	WHERE a.object_id = b.object_id
	AND a.owner = 'SK'
	;

	--------------------------------------------------------------------------------------------------
	\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
	--------------------------------------------------------------------------------------------------
	\| 0 \| SELECT STATEMENT \| \| 2705 \| 697K\| 37452 (1)\| 00:00:02 \|
	\|* 1 \| HASH JOIN \| \| 2705 \| 697K\| 37452 (1)\| 00:00:02 \|
	\|* 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| TAB1 \| 2705 \| 348K\| 37056 (1)\| 00:00:02 \|
	\| 3 \| INDEX FULL SCAN \| TAB1_IX01 \| 73044 \| \| 514 (0)\| 00:00:01 \|
	\| 4 \| TABLE ACCESS FULL \| TAB2 \| 73045 \| 9415K\| 396 (1)\| 00:00:01 \|
	--------------------------------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
	2 - filter("A"."OWNER"='SK')

	Hint Report (identified by operation id / Query Block Name / Object Alias):
	Total hints for statement: 2 (U - Unused (2))
	---------------------------------------------------------------------------

	2 - SEL$1 / A@SEL$1
	U - index(a tab1_ix01 b tab2_ix01)
	U - index(a tab1_ix01 b tab2_ix01)

	SQL> SELECT /+ index(a tab1_ix02 b tab2_ix01) /
	*
	FROM sk.tab1 a, sk.tab2 b
	WHERE a.object_id = b.object_id
	AND a.owner = 'SK'
	;

	--------------------------------------------------------------------------------------------------
	\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
	--------------------------------------------------------------------------------------------------
	\| 0 \| SELECT STATEMENT \| \| 2705 \| 697K\| 2552 (1)\| 00:00:01 \|
	\|* 1 \| HASH JOIN \| \| 2705 \| 697K\| 2552 (1)\| 00:00:01 \|
	\|* 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| TAB1 \| 2705 \| 348K\| 2156 (1)\| 00:00:01 \|
	\| 3 \| INDEX FULL SCAN \| TAB1_IX02 \| 73044 \| \| 513 (0)\| 00:00:01 \|
	\| 4 \| TABLE ACCESS FULL \| TAB2 \| 73045 \| 9415K\| 396 (1)\| 00:00:01 \|
	--------------------------------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
	2 - filter("A"."OWNER"='SK')

	Hint Report (identified by operation id / Query Block Name / Object Alias):
	Total hints for statement: 2 (U - Unused (2))
	---------------------------------------------------------------------------

	2 - SEL$1 / A@SEL$1
	U - index(a tab1_ix02 b tab2_ix01)
	U - index(a tab1_ix02 b tab2_ix01)

	SQL> SELECT /+ index(b tab2_ix01 a tab1_ix01 ) /
	*
	FROM sk.tab1 a, sk.tab2 b
	WHERE a.object_id = b.object_id
	AND a.owner = 'SK'
	;

	---------------------------------------------------------------------------
	\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
	---------------------------------------------------------------------------
	\| 0 \| SELECT STATEMENT \| \| 2705 \| 697K\| 790 (1)\| 00:00:01 \|
	\|* 1 \| HASH JOIN \| \| 2705 \| 697K\| 790 (1)\| 00:00:01 \|
	\|* 2 \| TABLE ACCESS FULL\| TAB1 \| 2705 \| 348K\| 395 (1)\| 00:00:01 \|
	\| 3 \| TABLE ACCESS FULL\| TAB2 \| 73045 \| 9415K\| 396 (1)\| 00:00:01 \|
	---------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
	2 - filter("A"."OWNER"='SK')

	Hint Report (identified by operation id / Query Block Name / Object Alias):
	Total hints for statement: 1 (E - Syntax error (1))
	---------------------------------------------------------------------------

	1 - SEL$1
	E - index(b

	SQL> SELECT
	*
	FROM sk.tab1 a
	WHERE object_id in (SELECT object_id FROM sk.tab2 b)
	;

	------------------------------------------------------------------------------------
	\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
	------------------------------------------------------------------------------------
	\| 0 \| SELECT STATEMENT \| \| 73040 \| 9771K\| 761 (1)\| 00:00:01 \|
	\|* 1 \| HASH JOIN \| \| 73040 \| 9771K\| 761 (1)\| 00:00:01 \|
	\| 2 \| SORT UNIQUE \| \| 73045 \| 356K\| 140 (0)\| 00:00:01 \|
	\| 3 \| INDEX FAST FULL SCAN\| TAB2_IX01 \| 73045 \| 356K\| 140 (0)\| 00:00:01 \|
	\| 4 \| TABLE ACCESS FULL \| TAB1 \| 73044 \| 9415K\| 396 (1)\| 00:00:01 \|
	------------------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	1 - access("OBJECT_ID"="OBJECT_ID")

	SQL> SELECT /+ index(a tab1_ix01) /
	*
	FROM sk.tab1 a
	WHERE object_id in (SELECT /+ index(b tab2_ix02) / object_id FROM sk.tab2 b)
	;

	--------------------------------------------------------------------------------------------------
	\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
	--------------------------------------------------------------------------------------------------
	\| 0 \| SELECT STATEMENT \| \| 73040 \| 9771K\| 37794 (1)\| 00:00:02 \|
	\|* 1 \| HASH JOIN \| \| 73040 \| 9771K\| 37794 (1)\| 00:00:02 \|
	\| 2 \| SORT UNIQUE \| \| 73045 \| 356K\| 513 (0)\| 00:00:01 \|
	\| 3 \| INDEX FULL SCAN \| TAB2_IX02 \| 73045 \| 356K\| 513 (0)\| 00:00:01 \|
	\| 4 \| TABLE ACCESS BY INDEX ROWID BATCHED\| TAB1 \| 73044 \| 9415K\| 37056 (1)\| 00:00:02 \|
	\| 5 \| INDEX FULL SCAN \| TAB1_IX01 \| 73044 \| \| 514 (0)\| 00:00:01 \|
	--------------------------------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	1 - access("OBJECT_ID"="OBJECT_ID")

oracle hint 위치별 적용여부 테스트

힌트없음

일반적인 인덱스 힌트(정상)

괄호안 공백 대신 콤마 사용(정상)

2개 테이블 힌트 사용(정상)

힌트 구문 2개 사용(앞에것만 적용됨)

한개의 index 힌트에 여러 테이블 사용(앞에것만 적용됨 tab1_ix01)

한개의 index 힌트에 여러 테이블 사용(앞에것만 적용됨 tab1_ix02)

순서를 바꾸니 모든 힌트 무효(E - Syntax error 메세지)

서브쿼리 힌트 없음

서브쿼리 힌트 사용(정상)

'DBMS > SQL튜닝' 카테고리의 다른 글

관련글

댓글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역