본문 바로가기
DBMS/SQL튜닝

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

by 드바 2023. 7. 4.
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

관련글

댓글

티스토리툴바