새로쓴 대용량 데이터베이스솔루션 1 (2011년)
Hints for Query Transformations
0
0
31,844
by 구루비스터디 Hint 힌트 USE_CONCAT REWRITE MERGE UNNEST STAR_TRANSFORMATION [2023.10.14]
Hints for Query Transformations
NO_QUERY_TRANSFORMATION
SELECT N1 FROM (SELECT * FROM T1 A) V WHERE V.N1 = 1;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| T1PK | 1 | 4 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
SELECT /*+ NO_QUERY_TRANSFORMATION */ N1 FROM (SELECT * FROM T1 A) V WHERE V.N1 = 1;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN| T1PK | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
USE_CONCAT
SELECT /*+ USE_CONCAT */ COUNT(*) FROM T1 WHERE N1 = 10 OR N2 = 10;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | CONCATENATION | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 8 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | T1PK | 1 | | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 80 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1N2 | 10 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
-- OR-Expansion 유도
NO_EXPAND
SELECT /*+ NO_EXPAND */ COUNT(*) FROM T1 WHERE N1 = 10 OR N2 = 10;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | BITMAP CONVERSION COUNT | | 11 | 88 | 2 (0)| 00:00:01 |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | T1N2 | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | T1PK | | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
-- OR-Expansion 방지
REWRITE
SELECT /*+ REWRITE */ N4, COUNT(*) AS CNT FROM T1 GROUP BY N4;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 260 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| M1 | 10 | 260 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
NO_REWRITE
SELECT /*+ NO_REWRITE */ N4, COUNT(*) AS CNT FROM T1 GROUP BY N4;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 5 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 10 | 30 | 5 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | T1N4 | 10000 | 30000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------
MERGE
SELECT /*+ MERGE(X) */ COUNT(*) FROM T1, (SELECT N4 FROM T2 WHERE N2 = 10) X WHERE T1.N4 = X.N4;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | NESTED LOOPS | | 10000 | 97K| 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 70 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2N2 | 10 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1N4 | 1000 | 3000 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
NO_MERGE
SELECT /*+ NO_MERGE(X) */ COUNT(*) FROM T1, (SELECT N4 FROM T2 WHERE N2 = 10) X WHERE T1.N4 = X.N4;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | NESTED LOOPS | | 5000 | 30000 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 10 | 30 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 70 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2N2 | 10 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1N4 | 500 | 1500 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
UNNEST
SELECT /*+ UNNEST(@QB) */ COUNT(*) FROM T1 WHERE T1.N1 IN (SELECT /*+ QB_NAME(QB) */ N4 FROM T2 WHERE N1 = 10);
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | NESTED LOOPS | | 1 | 11 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | T2PK | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | T1PK | 10000 | 40000 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
-- 서브쿼리를 조인형태로 변형
NO_UNNEST
SELECT /*+ NO_UNNEST(@QB) */COUNT(*) FROM T1 WHERE T1.N1 IN (SELECT /*+ QB_NAME(QB) */ N4 FROM T2 WHERE N1 = 10);
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5007 (1)| 00:01:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FULL SCAN | T1PK | 500 | 2000 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | T2PK | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
STAR_TRANSFORMATION
SELECT /*+ STAR_TRANSFORMATION FACT(X) */ X.N4, COUNT(*)
FROM T1 A, T1 B, T1 C, T2 X
WHERE X.N1 = A.N1
AND X.N2 = B.N2
AND X.N3 = C.N3
GROUP BY X.N4;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 250 | 1821 (67)| 00:00:22 |
| 1 | HASH GROUP BY | | 10 | 250 | 1821 (67)| 00:00:22 |
|* 2 | HASH JOIN | | 10M| 238M| 758 (22)| 00:00:10 |
| 3 | INDEX FULL SCAN | T1N3 | 10000 | 30000 | 5 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 100K| 2148K| 651 (10)| 00:00:08 |
| 5 | INDEX FULL SCAN | T1N2 | 10000 | 40000 | 6 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 10000 | 175K| 644 (10)| 00:00:08 |
| 7 | INDEX FULL SCAN | T1PK | 10000 | 40000 | 5 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | T2 | 10000 | 136K| 638 (10)| 00:00:08 |
| 9 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 10 | BITMAP AND | | | | | |
| 11 | BITMAP MERGE | | | | | |
| 12 | BITMAP KEY ITERATION | | | | | |
| 13 | INDEX FULL SCAN | T1N2 | 10000 | 40000 | 6 (0)| 00:00:01 |
| 14 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 15 | INDEX RANGE SCAN | T2N2 | | | 1 (0)| 00:00:01 |
| 16 | BITMAP MERGE | | | | | |
| 17 | BITMAP KEY ITERATION | | | | | |
| 18 | INDEX FULL SCAN | T1N3 | 10000 | 30000 | 5 (0)| 00:00:01 |
| 19 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 20 | INDEX RANGE SCAN | T2N3 | | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
-- FACT TABLE : T2 매출
-- DIMENSION TABLE : T1(A) 고객, T1(B) 상품, T1(C) 부서
-- BITMAP 연산을 통해 FACT TABLE(T2) 범위를 줄인 후 각 DIMENSION TABLE(T1(A), T1(B), T1(C))과 JOIN
NO_STAR_TRANSFORMATION
SELECT /*+ NO_STAR_TRANSFORMATION */ X.N4, COUNT(*)
FROM T1 A, T1 B, T1 C, T2 X
WHERE X.N1 = A.N1
AND X.N2 = B.N2
AND X.N3 = C.N3
GROUP BY X.N4;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 250 | 68 (78)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 10 | 250 | 68 (78)| 00:00:01 |
| 2 | NESTED LOOPS | | 10M| 238M| 68 (78)| 00:00:01 |
| 3 | NESTED LOOPS | | 100K| 2148K| 15 (7)| 00:00:01 |
| 4 | NESTED LOOPS | | 10000 | 175K| 13 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10000 | 136K| 12 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | T2N4 | 10000 | | 5 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | T1PK | 1 | 4 | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | T1N2 | 10 | 40 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | T1N3 | 100 | 300 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
"구루비 데이터베이스 스터디모임" 에서 2011년에 "새로쓴 대용량 데이터베이스 솔루션1" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4446
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.