나그네소
goldilocks tuning manual 본문
<< 진행 >>
Execution Plan Node는 크게 <Statement Node>, <Access Node>
<< SQL Tuning >>
trace를 떠서 HASH 있는것을 찾아 튜닝하자.
<< SQL 처리 과정 >>
SUNDB에서 전체적인 SQL 처리 과정은 아래 그림과 같이 SQL Parser, Plan Cache Check, SQL Validation, Optimization, Plan Generation, Execution의
과정을 거치게 된다. Plan Cache Check의 경우 Plan Cache에 동일한 질의의 실행계획이 저장되어 있으면 해당 실행계획을 이용하여 Execution을
수행한다.
<< SQL Parser >>
SQL Parser는 SQL 처리 과정의 첫번째 단계로 사용자가 입력한 SQL 구문에 대하여 문법상 올바른지 판단한다.
만약 SQL 구문이 문법상 올바르지 않다면 이 단계에서 에러로 처리된다.
SQL Parser의 단계에서는 Parsing에 필요한 정보들을 수집 및 저장하며, 수행이 끝났을 때 Parsing된 SQL 구조를
결과로 갖는다. 이때 Parsing된 SQL 구조 및 기타 정보들은 각각의 세션의 영역에 저장되어 관리된다.
--> SQL Check를 하고 파싱된 SQL 결과 구조를 반환한다.
<< Plan Cache Check >>
SQL Parser 단계가 끝나면 해당 질의에 대하여 동일한 질의가 Plan Cache에 저장되어 있는지 확인하는데 이 단계가
Plan Cache Check이다. Plan Cache는 서로 다른 세션에 의해 발생한 Plan들이 모두 공유되며, 다른 세션에 의해 저장된
Plan도 참조가 가능하다. Plan Cache Check 단계는 Plan Cache에 저장된 Plan의 이용으로 SQL Validation 단계부터
Plan Generatioin 단계까지의 과정을 생략함으로 인한 성능 향상을 가져온다.
--> Plan cache 키냐 안키냐 에따라 성능이 많이 차이가 난다.
<< SQL Validation >>
SQL Validation의 단계는 질의에 대하여 구문상 올바른지를 판단하는 단계이다. 이 단계에서는 질의에 기술한
테이블 및 컬럼 등의 객체들이 존재하는지 및 컬럼 참조시 참조가능한 컬럼인지 등의 구문상 오류에 대하여 검사한다.
<< Optimization >>
Optimization 단계는 SQL 구문에 대하여 다양한 실행계획을 세우고 그 중 가장 좋은 Plan을 선택하는 과정이다.
<< Plan Generation >>
Plan Generation은 Optimization에 의해 최종 선택된 Plan에 대하여 Execution 단계에서 수행할 수 있는 형태의 실행계획을
생성하는 단계이다. 실행계획은 여러 단계의 노드들의 조합으로 구성되며, 각 단계의 노드들은 수행한 결과 셋을 상위의
노드로 반환한다. 최종 단계의 노드는 SQL 구문의 최종 결과를 사용자에게 보낸다. 실행계획은 Tree 구조의 노드들로 구성
되며, Tree의 구조에서는 다음의 정보들을 알 수 있다. ·
- 각 테이블들의 Access 방법
- 참조하는 테이블들의 순서
- 테이블들의 조인 연산에 대한 조인 방법
- 데이터의 filter에 대한 정보
- 데이터의 Grouping 및 Aggregation에 대한 정보
- 데이터의 정렬에 대한 정보
<< Execution >>
Plan Generation에 의하여 생성되거나 Plan Cache Check에서 선택된 실행계획에 대하여 실제로
수행하여 결과를 반환하는 단계이다.
[ Plan Generation의 예제를 이용하여 실제 실행하는 과정 ]
1. IDX 3에서 테이블 T2에 대하여 Table Access를 이용하여 결과를 가져온다. 이때 결과로 반환하는 Row에는 I1과 I2 컬럼이 포함된다.
2. IDX 5에서 테이블 T1에 대하여 Table Access를 이용하여 결과를 가져온다. 이때 "I2 = 1"이 Physical Filter로 처리된 결과를 반환하게
되며, 결과로 반환하는 Row에는 I1과 I2 컬럼이 포함된다.
3. IDX4에서 IDX 5로부터 반환된 결과에 대하여 Hash Join Instant Access를 생성하며, Hash Filter로 "I1 ={I1}"이 수행된다. 결과로 반환
하는 Row에는 I1과 I2 컬럼이 포함된다.
4. IDX 2에서 IDX 3의 수행 결과에 대하여 IDX 4에서 Hash Filter를 이용하여 Hash Join을 수행하며, 결과로 반환하는 Row에는 T1 테이블의
I1과 I2 컬럼 및 T2 테이블의 I1과 I2 컬럼이 포함된다.
5. IDX 1에서 IDX2의 수행 결과에 대하여 Sort Instant Access를 생성하며, T1 테이블의 I1 컬럼을 이용하여 정렬한다. 결과로는 T1 테이블의
I1과 I2 컬럼 및 T2 테이블의 I1과 I2 컬럼이 포함된다.
6. IDX 0에서 IDX1의 수행 결과를 최종적으로 사용자에게 반환한다.
<< Query Optimizer 소개 >>
Query Optimizer는 Parsing 및 Validation을 거친 SQL 구문에 대하여 Query Transformations, 통계 정보를
이용한 Cost 계산을 하여 Candidate Plan들을 만들고, Candidate Plan들 중에서 가장 효율적인 (가장 Cost가
적은) Plan을 선택하여 최종 실행 계획을 작성한다.
Cost 계산에서는 기본적으로 Selectivity와 Cardinality를 사용한다. Selectivity는 조건으로 인하여 결과셋으로
반환될 row의 비율을 말하고 Cardinality는 각 노드에서 결과로 반환하는 row의 개수를 말한다.
Query Optimizer에서의 Cost 계산은 Selectivity와 Cardinality를 기본으로 하여 Table Access, Index Access 등의
Access Paths에 대한 방법과 Nested Loops Join, Hash Join 등의 Join Method에 대한 방법, Join Ordering 등의
기법들을 사용한다.
<< Filter Push Down >>
Filter Push Down은 <where clause>에 존재하는 Filter들 중 FROM 절에 기술한 SubQuery(View)에 Push 가능한
Filter들을 Push하는 기능을 말한다. Filter Push Down으로 SubQuery(View)에 Push된 Filter는
SubQuery(View)에서 해당 Filter로 Index Access를 하거나 SubQuery(View) 수행 중 먼저 처리되는 Filter로
작용하여 질의 처리 성능을 향상시킨다.
--> Fileter Push Down은 From절의 조건에 해당하는 Filter가 Subquery 조건절에 들어가는 것을 얘기한다.
ex)
SELECT l_linenumber, l_quantity
FROM ( SELECT *
FROM lineitem
WHERE l_shipdate >= date '1996-01-01'
AND l_shipdate <= date '1996-12-31' )
WHERE l_shipmode = 'AIR';
위 질의에서 최상의 노드 WHERE 절에 존재하는 Filter인 l_shipmode = 'AIR'는 FROM 절에 기술된 SubQuery로
Push되어지며, 이는 아래 질의와 동일하다.
SELECT l_linenumber, l_quantity
FROM ( SELECT *
FROM lineitem
WHERE l_shipdate >= date '1996-01-01'
AND l_shipdate <= date '1996-12-31'
AND l_shipmode = 'AIR' );
위와 같이 변형된 질의는 lineitem 테이블에 l_shipmode에 대한 index가 존재하는 경우 Index Access를 이용이
가능하여, 성능 향상을 가져온다.
<< Single Table Min/Max Aggregation Conversion >>
Single Table Min/Max Aggregation Conversion은 단일 테이블에 대하여 MIN 또는 MAX Aggregation을
요구하는 질의에 대하여 해당 Aggregation의 Columnn에 대한 Index가 존재하는 경우 Index를 이용하여
결과를 반환하도록 하는 기능이다.
이 기능을 이용하기 위해서는 다음의 조건을 만족해야 한다.
● 단일 테이블에 대한 질의이어야 하며, Target에 MIN 또는 MAX 중 하나만 존재해야 한다.
● 질의에 OFFSET / LIMIT 구문이 없어야 한다.
● Aggregation의 인자는 반드시 테이블의 컬럼 하나만 존재해야 한다.
● Aggregation 대상 컬럼이 Index의 첫번째 Key인 Index가 존재해야 한다.
● 사용자가 Table Access 또는 Rowid Access 등의 힌트를 주지 않아야 한다.
--> Aggregation function을 사용하여도 index range를 타는 것을 얘기 하는 것이다.
[plan]
gSQL> \explain plan verbose
2 select * from t1 where c1 = ( select max(c1) from t1 );
C1 C2
-- --
2 2
1 row selected.
>>> start print plan
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS | Total Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0:00:00.00 |
| 1 | INDEX ACCESS ("T1, T1_IDX1") | 1 | 0:00:00.00 |
| 2 | SUB QUERY LIST | 1 | 0:00:00.00 |
| 3 | INDEX ACCESS ("T1, T1_IDX1") | 1 | 0:00:00.00 |
==================================================================================================
1 - READ INDEX COLUMNS : C1
READ TABLE COLUMNS : C2
MIN RANGE : C1 = C1
MAX RANGE : C1 = C1
2 - READ COLUMNS : C1
3 - READ INDEX COLUMNS : C1
MAX RANGE : C1 IS NOT NULL
<<< end print plan
-> 위 실행 계획 출력에서 보면 조건절의 SubQuery에 대하여 index access를 이용하여 Max Range의 값을 읽어오는
것을 볼 수 있다.
[Rewrite Target On Exists]
Rewrite Target On Exists는 Exists 또는 Not Exists 연산에 존재하는 SubQuery에서 Target절을
Constant Value로 변형하는 기능이다. Exists 또는 Not Exists 연산은 SubQuery의 결과 row가 존
재하는지 여부를 판단하는 연산자로 Target의 개수나 Target의 Expression 처리 결과가 연산자의
결과에 영향을 미치지 않기 때문에 Target절을 Constant Value로 변형하여도 결과는 동일하다.
Rewrite Target On Exists는 불필요한 Target절의 Expression 처리를 줄여 질의 처리 성능을 향상시킨다.
[NO_QUERY_TRANSFORMATION]
query에 대하여 어떠한 변형도 하지 않도록 optimizer에 지시한다. 이 hint를 기술하면
heuristic optimizer 및 cost based optimizer 등에서 최적의 성능을 위해 optimizer가
query를 변형하는 과정들을 모두 수행하지 않는다.
\explain plan verbose
select /*+ NO_QUERY_TRANSFORMATION */
c1 from t1 a where exists ( select c2 from t2 b where a.c1 != b.c2)
--> exists 사용 하는 쿼리가 있을 경우 NO_QUERY_TRANSFORMATION hint 절을 주어 target
절을 상수화 시키면 좀더 빨리 처리 할 수 있다는 것을 얘기 한다. 하지만 잘모르겠다.
<< Access Paths >>
[Table Access]
Table Access는 테이블에 대한 검색시 Index나 Rowid를 이용하지 않고 저장된 테이블 그대로를
Scan하는 방식을 말한다. 일반적으로 Table Access는 다른 Access 방식보다 비용이 크기 때문에
Query Optimizer는 다른 Access 방식을 사용할 수 없는 경우나 사용자가 힌트로 Table Access를
요구한 경우에만 선택한다.
Table Access를 사용하는 경우는 아래와 같다.
index가 존재하지 않는 경우
● index에 존재하는 컬럼에 대한 Filter에서 컬럼이 존재하는 쪽에 Function이 존재하는 경우
(Ex: i1 + 1 = 10)
● index의 첫번째 컬럼에 대한 조건이 없어 테이블 접근의 비용이 적은 경우
(Ex: index key가 i1, i2인 index에 i2= 3 조건이 주어진 경우)
● 테이블이 작아 Index Access보다 Table Access의 비용이 적은 경우
● 사용자가 테이블 접근 힌트를 준 경우 (Ex: FULL(t1) 힌트)
● index에 대한 조건이 존재하지만 테이블 접근이 필요하여 Cost 계산에 의해 Table Access의 비용이
적은 경우
[ Index Access ]
Index Access는 테이블에 대한 검색시 Index를 이용하여 Scan하는 방식을 말한다. 일반적으로 Filter 중
Index를 이용할 수 있는 Filter가 존재하면 다른 Access보다 Index Access가 더 효율적이다.
Query Optimizer는 Index Access가 사용 가능한 경우 Cost를 계산하여 가장 적은 비용을 갖는 Index Access를
선택하며, 사용자가 힌트로 Index Access를 명시하는 경우 사용자가 명시한 Index들 중에서 Cost를 계산하여
가장 적은 비용을 갖는 Index Access를 선택한다. 다만 아래의 경우에는 Index Access가 선택되지 않는다.
[ Rowid Access ]
Rowid Access는 테이블에 대한 검색시 Rowid를 이용하여 해당 페이지에 직접 접근하는 방식을 말한다.
Rowid Access를 사용하기 위해서는 반드시 Rowid에 대한 Filter가 존재해야 한다. Query Optimizer는
Rowid에 대한 조건이 존재하는 경우 일반적으로 다른 Access보다 우선적으로 Rowid Access를 선택한다
gSQL> \EXPLAIN PLAN
2 SELECT p_name, p_brand, p_type
3 FROM part
4 WHERE ROWID = 'AAAAAAAAADiAACAAACCjAAA';
P_NAME P_BRAND P_TYPE
------ ---------- ------
Part#1 Brand#1 COPPER
1 row selected.
>>> start print plan
< Execution Plan >
=====================================================================================
| IDX | NODE DESCRIPTION | ROWS |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | USER ROWID ACCESS ("PART") | 1 |
=====================================================================================
1 - READ COLUMNS : P_NAME, P_BRAND, P_TYPE
ROWID ACCESS EXPR : ROWID = 'AAAAAAAAADiAACAAACCjAAA'
<<< end print plan
<< Index Concat >>
--> or 조건으로 두개의 plan이 생성 된 경우 하나로 통합하여 사용을 하려고 할 때 사용을 한다.
/*+ INDEX_COMBINE(part, part_size) */
Index Concat은 Filter에 Or 구문이 존재하고 Or에 의해 나누어진 Filter들이 각각 Index Access가
가능한 경우 각각의 Index Access의 결과를 통합하여 하나의 결과로 만드는 방식을 말한다. 즉,
Index Concat은 하위 노드로 다수의 Index Access를 갖는 Concat 노드이다. Query Optimizer에서는
Filter에서 Or 구문이 존재하는 경우 이에 대한 Index Concat에 대한 Cost를 계산하고 다른 Access보다
적은 비용일 경우 Index Concat을 선택한다.
- Index Concat의 Cost 계산은 다음의 단계로 수행된다.
1. Filter에 대해서 Or를 기준으로 재조정한 Filter를 생성한다.
2. Or를 기준으로 분류된 각각의 Filter들에 대하여 적용 가능한 Index 중 가장 좋은 Index를 선택한다.
3. 각각의 Filter에 대해 선택한 Index들에 대하여 결과로 취합할때 중복을 제거하기 위한 Concat의 Cost를
계산한다.
4. 앞서 선택한 Index들의 Cost와 중복을 제거하기 위한 Concat의 Cost를 합산하여 Index Concat의 최종
Cost를 결정한다.
- Index Concat Node에 대한 Execution은 다음의 단계로 수행된다.
1. Index Concat Node의 하위 노드들 중 첫번째 노드를 수행한다.
2. 수행 결과 중 중복제거를 위한 데이터를 Concat 노드에 저장하고, 결과를 상위 노드로 보낸다.
3. 두번째 노드부터는 Concat 노드에서 중복여부를 판단하여 중복되지 않는 row들에 대하여 중복제거를 위한
데이터를 Concat 노드에 저장하고, 결과를 상위 노드로 보낸다.
[example]
gSQL> \explain plan verbose
2 select /*+ index_combine(t1,t1_idx2) */ * from t1 where c2 = 1 or c2 = 3;
C1 C2 C3
-- -- --
1 1 1
1 row selected.
>>> start print plan
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS | Total Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0:00:00.00 |
| 1 | CONCAT | 1 | 0:00:00.00 |
| 2 | INDEX ACCESS ("T1, T1_IDX2") | 1 | 0:00:00.00 |
| 3 | INDEX ACCESS ("T1, T1_IDX2") | 0 | 0:00:00.00 |
==================================================================================================
2 - READ INDEX COLUMNS : C2
READ TABLE COLUMNS : C1, C3
MIN RANGE : C2 = 1
MAX RANGE : C2 = 1
3 - READ INDEX COLUMNS : C2
READ TABLE COLUMNS : C1, C3
MIN RANGE : C2 = 3
MAX RANGE : C2 = 3
<<< end print plan
[참고]
<< index concat(conbine) >>
create table t2 (c1 int, c2 int);
create index t2_idx1 on t2 (c1);
1. or 조건 시 index 타지 못함.
select /*+ index(t2,t2_idx1) */ * from t2 where c1 = 1 or c1 = 2;
>>> start print plan
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS | Total Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0:00:00.00 |
| 1 | INDEX ACCESS ("T2, T2_IDX1") | 1 | 0:00:00.00 |
==================================================================================================
1 - READ INDEX COLUMNS : C1
READ TABLE COLUMNS : C2
LOGICAL KEY FILTER : C1 = 1 OR C1 = 2
<<< end print plan
or 조건 시 range 타지 못하고 key filter로 빠지게 된다. index를 타게 하려면 cancat(conbind)로
묶어서 처리 하면된다.
2. or 조건 시 index 타도록 함.
select /*+ index_combine(t2,t2_idx1) */ * from t2 where c1 = 1 or c1 = 2;
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS | Total Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0:00:00.00 |
| 1 | CONCAT | 1 | 0:00:00.00 |
| 2 | INDEX ACCESS ("T2, T2_IDX1") | 1 | 0:00:00.00 |
| 3 | INDEX ACCESS ("T2, T2_IDX1") | 0 | 0:00:00.00 |
==================================================================================================
2 - READ INDEX COLUMNS : C1
READ TABLE COLUMNS : C2
MIN RANGE : C1 = 1
MAX RANGE : C1 = 1
3 - READ INDEX COLUMNS : C1
READ TABLE COLUMNS : C2
MIN RANGE : C1 = 2
MAX RANGE : C1 = 2
<<< end print plan
각각의 index 타게 만들어 하나로 결과를 묶어서 처리 하도록 하는 것이다.
3. concat으로 풀리면 좋을 때
concat으로 풀리면 좋은 경우는 or로 index 타지 못하지만 각각 풀어 range로 풀리게 되면
access 건수가 적어 지기 때문에 좋다.
<< Joins >>
Join은 두 테이블(혹은 view)의 결과 row들을 하나의 결과 row들로 결합하는 과정이다. Join을 하는 과정에서 두
테이블(혹은 view)의 row간의 결합을 위한 조건이 존재할 수 있는데 이를 Join 조건이라한다. Join에서 Join 조건이
존재하지 않는 경우 한 쪽 테이블의 각 row마다 다른쪽 테이블의 모든 row들이 결합한 결과 row들을 반환한다.
Join은 처리과정을 일반적으로 Tree 형태로 표현한다. Join의 Tree에서 Join의 왼쪽에 놓여진 테이블을 Outer
Node라 하고 오른쪽에 놓여진 테이블을 Inner Node라 하며, 일반적인 Join의 수행시 Outer Node의 row 하나를
읽어 Inner Node에서 Join 조건에 일치하는 row들을 읽어와 결합하는 형태로 수행된다.
--> 조인 조건이 없을 경우 한 쪽 테이블의 각 row마다 다른쪽 테이블의 모든 row들과 결합한 결과 row을 반환한다.
<< Join 종류 >>
[Cross Join]
Cross Join은 Join 조건이 없는 Join이다. Join 조건이 없기 때문에 Outer Node의 각 row에 대하여 Inner Node의
모든 row가 결합하여 Join의 결과 row로 반환된다.
gSQL> \explain plan verbose
2 select c1, c2 from t1, t2 ;
C1 C2
-- --
1 2
1 2
1 2
2 2
4 rows selected.
>>> start print plan
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS | Total Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0:00:00.00 |
| 1 | NESTED LOOP JOIN (INNER JOIN) | 4 | 0:00:00.00 |
| 2 | TABLE ACCESS ("T1") | 4 | 0:00:00.00 |
| 3 | TABLE ACCESS ("T2") | 1 | 0:00:00.00 |
==================================================================================================
1 - JOINED COLUMNS : T1.C1, T2.C2
2 - READ COLUMNS : C1
3 - READ COLUMNS : C2
<<< end print plan
[Inner Join]
Inner Join은 Join 조건이 있는 Join이다. Inner Join은 Outer Node의 각 row에 대하여 Inner Node에 모든 row들
중 Join 조건을 만족하는 row들에 대해서만 결합하여 Join 결과로 반환한다.
[Outer Join]
Outer Join은 Join 조건이 있는 Join으로 Outer Node의 각 row에 대하여 Inner Node에 Join 조건을 만족하는
row가 존재하면 해당 row와 결합한 row을 결과로 반환하고 Join 조건을 만족하는 row가 없으면 NULL 데이터만을
갖는 row와 결합한 row를 Join 결과로 반환한다.
Left Outer Join의 경우 구문의 좌측에 존재하는 테이블이 Outer Node가 되며, Right Outer Join의 경우 구문의 우측에
존재하는 테이블이 Outer Node가 된다. Full Outer Join의 경우에는 Left Outer Join 결과와 Right Outer Join 결과들의
합집합의 개념으로 Join 조건을 만족하는 row들을 결합한 row와 Join 조건을 만족하지 않는 Left Node의 row들 및
Right Node의 row들을 NULL 데이터만을 갖는 row와 결합한 row들을 Join 결과로 반환한다.
[Semi Join]
Semi Join은 Join 조건을 만족하는 Outer Node의 row만을 결과로 반환하는 Join으로 Join 조건이 반드시 존재해야
한다. Semi Join은 Outer Node의 각 row들에 대하여 Inner Node에 Join 조건을 만족하는 row가 존재하면 Outer
Node의 row만을 Join 결과로 반환한다.
gSQL> \EXPLAIN PLAN
SELECT p_name, p_brand
FROM part
WHERE p_partkey IN ( SELECT ps_partkey
FROM partsupp
WHERE ps_availqty > 5000 );
[Anti-Semi Join]
Anti-Semi Join은 Join 조건을 만족하지 않는 Outer Node의 row만을 결과로 반환하는 Join으로 Join 조건이
반드시 존재해야 한다. Anti-Semi Join은 Outer Node의 각 row들에 대하여 Inner Node에 Join 조건을 만족하는
row가 하나도 존재하지 않으면 Outer Node의 row만을 Join 결과로 반환한다.
gSQL> \EXPLAIN PLAN
SELECT p_name, p_brand
FROM part
WHERE p_partkey NOT IN ( SELECT ps_partkey
FROM partsupp
WHERE ps_availqty > 5000 );
<< Join Methods >>
Join Methods는 두 테이블(혹은 view)의 Join 연산 방법으로 Nested Loops Join과 Sort Merge Join, Hash Join이
있다. Query Optimizer는 Join 연산에 있어 이 3가지 Join Methods에 대한 Cost를 계산하여 가장 적은 비용의
Join 연산을 선택한다.
<< SQL 실행 계획 읽기 >>
[Execution Plan Node Table]
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS ("T1") | 3 |
==========================================================================
위 예에서 보면 Execution Plan Node Table은 IDX와 NODE DESCRIPTION, ROWS의 컬럼들로 구성되어 있으며, 각 노드는 0부터 고유의
식별번호를 갖는다. 또한 각 노드들의 Tree 형태의 구성을 노드 이름 앞의 공백으로 구분할 수 있도록 되어 있다. 즉, 위 예에서
SELECT STATEMENT는 TABLE ACCESS를 child node로 갖는다.
Execution Plan Node Table의 각 컬럼에 대한 정보는 아래와 같다.
● IDX
○ 각 plan node에 부여된 식별자
● NODE DESCRIPTION
○ plan node 이름
○ 괄호 안의 내용은 plan node를 구분하는 부가적인 정보
○ 들여쓰기로 표시된 plan node는 하위 plan node를 의미
■ 하위 plan node부터 수행하여 상위 노드에 그 결과를 전달
● ROWS
○ plan node 수행에 따른 결과 레코드의 수 위의 Execution Plan Node Table을 분석하면 다음과 같다.
"T1" 테이블에 대한 "TABLE ACCESS"를 수행하여 3개의 결과 record를 얻었고, 이는 "SELECT STATEMENT"의
입력으로 전달된다.
[Node Information]
1 - READ COLUMNS : ID, NAME
Node Information은 각 노드에서 출력할 정보가 있으면 출력하게 되며, 노드의 구분을 위하여 앞에 Execution
Plan Node Table의 IDX를 출력한다.
위의 Node Information을 분석하면 다음과 같다.
"T1" 테이블에 대한 "TABLE ACCESS"를 수행할 때 ID 와 NAME을 read column 대상으로 한다.
example]
gSQL> \explain plan verbose
2 select c1, c2 from t1, t2 ;
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS | Total Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0:00:00.00 |
| 1 | NESTED LOOP JOIN (INNER JOIN) | 4 | 0:00:00.00 |
| 2 | TABLE ACCESS ("T1") | 4 | 0:00:00.00 |
| 3 | TABLE ACCESS ("T2") | 1 | 0:00:00.00 |
==================================================================================================
1 - JOINED COLUMNS : T1.C1, T2.C2
2 - READ COLUMNS : C1
3 - READ COLUMNS : C2
[Execution Plan Node의 분류]
[DELETE STATEMENT]
DELETE 구문을 수행한다.
● DELETE 구문을 수행한다.
● DELETE (table_name)node를 하위 노드로 갖는다.
gSQL> \explain plan only DELETE FROM supplier;
<Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | DELETE STATEMENT | |
| 1 | DELETE ("SUPPLIER") | 0 |
| 2 | INDEX ACCESS ("SUPPLIER, SUPPLIER_PK_INDEX") | 0 |
==========================================================================
2 - READ INDEX COLUMNS : S_SUPPKEY
<<< end print plan
[INSERT STATEMENT]
● INSERT 구문을 수행한다.
● INSERT (table_name)node를 하위 노드로 갖는다.
gSQL> \explain plan only
INSERT INTO supplier VALUES ( 123, 'jhkim', 'KOREA', '0123456789' );
>>> start print plan
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | INSERT ("SUPPLIER") | 0 |
==========================================================================
<<< end print plan
[SELECT STATEMENT]
● SELECT 구문을 수행한다.
gSQL> \explain plan only SELECT s_suppkey FROM supplier;
>>> start print plan
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX ACCESS ("SUPPLIER, SUPPLIER_PK_INDEX") | 0 |
==========================================================================
1 - READ INDEX COLUMNS : S_SUPPKEY
<<< end print plan
[UPDATE STATEMENT]
● UPDATE 구문을 수행 한다.
● UPDATE (table_name) node를 하위 노드로 갖는다.
gSQL> \explain plan only UPDATE lineitem SET l_quantity = l_quantity + 1;
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE ("LINEITEM") | 0 |
| 2 | TABLE ACCESS ("LINEITEM") | 0 |
==========================================================================
2 - READ COLUMNS : L_QUANTITY
[INDEX ACCESS (table_name [ AS alias ], index_name)]
● index_name의 index를 이용하여 table_name의 레코드를 탐색한다.
● Node Information
○ READ INDEX COLUMNS
■ index key column인 참조 column list
○ READ TABLE COLUMNS
■ index key column을 제외한 참조 column list
○ AGGREGATIONS
■ node내에서 처리할 중첩되지 않은 aggregation list
○ MIN RANGE
■ index 의 min key range
○ MAX RANGE
■ index 의 max key range
○ PHYSICAL KEY FILTER
■ key column 만을 대상으로 하는 type casting이 필요 없는 비교 연산에 대한 AND-filter
○ LOGICAL KEY FILTER
■ 전체 KEY FILTER 중에 PHYSICAL KEY FILTER를 제외한 AND-filter
○ PHYSICAL TABLE FILTER
■ index key column이 아닌 column이 포함된 type casting이 필요 없는 비교 연산에 대한 AND-filter
○ LOGICAL TABLE FILTER
■ 전체 TABLE FILTER에서 PHYSICAL TABLE FILTER를 제외한 AND-filter
gSQL> \explain plan only
SELECT /*+ INDEX( lineitem ) */ count( l_orderkey )
FROM lineitem
WHERE l_orderkey > 100 and l_linenumber > 0
and mod( l_orderkey, 2 ) = 0 and l_quantity > 0
and l_discount * l_tax > 100;
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX ACCESS ("LINEITEM, LINEITEM_PK_INDEX") | 0 |
==========================================================================
1 - READ INDEX COLUMNS : L_ORDERKEY, L_LINENUMBER
READ TABLE COLUMNS : L_QUANTITY, L_DISCOUNT, L_TAX
AGGREGATIONS : COUNT( L_ORDERKEY )
MIN RANGE : L_ORDERKEY > 100
MAX RANGE : L_ORDERKEY IS NOT NULL
PHYSICAL KEY FILTER : L_LINENUMBER > 0
LOGICAL KEY FILTER : MOD(L_ORDERKEY,2) = 0
PHYSICAL TABLE FILTER : L_QUANTITY > 0
LOGICAL TABLE FILTER : ( L_DISCOUNT * L_TAX ) > 100
[TABLE ACCESS (table_name [ AS alias ])]
table_name의 레코드를 탐색한다.
● Node Information
○ READ COLUMNS
■ 참조 column list
○ AGGREGATIONS
■ node내에서 처리할 중첩되지 않은 aggregation list
○ PHYSICAL FILTER
■ type casting이 필요 없는 비교 연산에 대한 AND-filter
○ LOGICAL FILTER
■ 전체 TABLE FILTER에서 PHYSICAL FILTER를 제외한 AND-filter
gSQL> \explain plan only
SELECT /*+ FULL( lineitem ) */ count( l_orderkey )
FROM lineitem
WHERE l_orderkey > 0
and l_quantity * l_extendedprice > 100;
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS ("LINEITEM") | 0 |
==========================================================================
1 - READ COLUMNS : L_ORDERKEY, L_QUANTITY, L_EXTENDEDPRICE
AGGREGATIONS : COUNT( L_ORDERKEY )
PHYSICAL FILTER : L_ORDERKEY > 0
LOGICAL FILTER : ( L_QUANTITY * L_EXTENDEDPRICE ) > 100
[USER ROWID ACCESS (table_name [ AS alias ])]
● rowid와 일치하는 하나의 레코드를 탐색한다.
● Node Information
○ READ COLUMNS
■ 참조 column list
○ AGGREGATIONS
■ node내에서 처리할 중첩되지 않은 aggregation list
○ ROWID ACCESS EXPR
■ 찾고자 하는 rowid expression
○ PHYSICAL FILTER
■ type casting이 필요 없는 비교 연산에 대한 AND-filter
○ LOGICAL FILTER
■ 전체 TABLE FILTER에서 PHYSICAL FILTER를 제외한 AND-filter
gSQL> \explain plan only
SELECT /*+ ROWID( T1 ) */ COUNT( l_orderkey )
FROM lineitem
WHERE rowid = null and l_orderkey > 0
and l_quantity * l_extendedprice > 100;
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | USER ROWID ACCESS ("LINEITEM") | 0 |
==========================================================================
1 - READ COLUMNS : L_ORDERKEY, L_QUANTITY, L_EXTENDEDPRICE
AGGREGATIONS : COUNT( L_ORDERKEY )
ROWID ACCESS EXPR : ROWID = NULL
PHYSICAL FILTER : L_ORDERKEY > 0
LOGICAL FILTER : ( L_QUANTITY * L_EXTENDEDPRICE ) > 100
[HASH JOIN (join_method)]
● 하위 두 노드에 대한 hash join을 수행한다.
● 하위 노드 중 inner node는 hash instant로 구성되어 있다.
● outer node의 IDX가 inner node의 IDX보다 작다. (outer node가 상위에 표시됨)
● join_method
○ INNER JOIN
○ LEFT OUTER JOIN
○ FULL OUTER JOIN
○ LEFT SEMI JOIN
○ LEFT SEMI JOIN NA
○ INVERTED LEFT SEMI JOIN
○ LEFT ANTI SEMI JOIN
● Node Information
○ JOINED COLUMNS
■ join 결과로 나갈 column list
○ JOIN FILTER
■ join condition으로 적용될 filter
○ WHERE FILTER
■ join 수행 후 적용될 filter
gSQL> \explain plan only
SELECT l_quantity
FROM orders FULL OUTER JOIN lineitem
ON o_orderkey = l_orderkey and o_orderdate <> l_shipdate
WHERE o_orderkey > 1;
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN (FULL OUTER JOIN) | 0 |
| 2 | TABLE ACCESS ("ORDERS") | 0 |
| 3 | HASH JOIN INSTANT ACCESS | 0 |
| 4 | TABLE ACCESS ("LINEITEM") | 0 |
==========================================================================
1 - JOINED COLUMNS : ORDERS.O_ORDERKEY, LINEITEM.L_QUANTITY
JOIN FILTER : {O_ORDERDATE} <> {L_SHIPDATE}
WHERE FILTER : ORDERS.O_ORDERKEY > 1
2 - READ COLUMNS : O_ORDERKEY, O_ORDERDATE
3 - INDEX COLUMNS : L_ORDERKEY
TABLE COLUMNS : L_SHIPDATE, L_QUANTITY
READ COLUMNS : L_ORDERKEY, L_SHIPDATE, L_QUANTITY
HASH FILTER : {O_ORDERKEY} = L_ORDERKEY
4 - READ COLUMNS : L_ORDERKEY, L_QUANTITY, L_SHIPDATE
[NESTED LOOP JOIN (join_method)]
● 하위 두 노드에 대한 nested loop join을 수행한다.
● outer node의 IDX가 inner node의 IDX보다 작다. (outer node가 상위에 표시됨)
● join_method
○ CROSS JOIN
○ INNER JOIN
○ LEFT OUTER JOIN
○ FULL OUTER JOIN
○ LEFT SEMI JOIN
○ INVERTED LEFT SEMI JOIN
○ LEFT ANTI SEMI JOIN
● Node Information
○ JOINED COLUMNS
■ join 결과로 나갈 column list
○ JOIN FILTER
■ join condition으로 적용될 filter
○ WHERE FILTER
■ join 수행 후 적용될 filter
gSQL> \explain plan only
SELECT /*+ USE_NL( orders, lineitem )
ORDERING( orders, lineitem ) */ l_quantity
FROM orders FULL OUTER JOIN lineitem
ON o_orderkey = l_orderkey
WHERE l_quantity > 10;
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOP JOIN (FULL OUTER JOIN) | 0 |
| 2 | TABLE ACCESS ("ORDERS") | 0 |
| 3 | TABLE ACCESS ("LINEITEM") | 0 |
==========================================================================
1 - JOINED COLUMNS : LINEITEM.L_QUANTITY
JOIN FILTER : {O_ORDERKEY} = {L_ORDERKEY}
WHERE FILTER : LINEITEM.L_QUANTITY > 10
2 - READ COLUMNS : O_ORDERKEY, O_TOTALPRICE
3 - READ COLUMNS : L_ORDERKEY, L_SUPPKEY, L_QUANTITY
[GROUP HASH INSTANT ACCESS]
● grouping 연산을 수행한다.
● SELECT 구문에 GROUP BY 절이 오거나 DISTINCT 절이 오는 경우 생성된다.
gSQL> \explain plan only
SELECT /*+ FULL( lineitem ) */ max( sum( l_quantity ) )
FROM lineitem
GROUP BY l_orderkey
HAVING sum( l_quantity ) > 10 and mod( sum( l_quantity ), 10 ) = 1;
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | GROUP HASH INSTANT ACCESS | 0 |
| 2 | TABLE ACCESS ("LINEITEM") | 0 |
==========================================================================
1 - AGGREGATIONS : SUM( L_QUANTITY )
NESTED AGGREGATIONS : MAX( SUM( L_QUANTITY ) )
GROUPING COLUMNS : L_ORDERKEY
RECORD COLUMNS : SUM( L_QUANTITY )
READ COLUMNS : SUM( L_QUANTITY )
PHYSICAL FILTER : SUM( L_QUANTITY ) > 10
LOGICAL FILTER : MOD(SUM( L_QUANTITY ),10) = 1
2 - READ COLUMNS : L_ORDERKEY, L_QUANTITY
[HASH JOIN INSTANT ACCESS]
● hash join의 inner node이다.
● hash join 조건(equi-join)으로 사용되는 column expression을 기준으로 하여, 하위 노드에 대한 결과에
대한 hash instant를 구성한다.
example]
gSQL> \explain plan only
SELECT /*+ USE_HASH( orders, lineitem ) ORDERING( orders, lineitem ) */ l_quantity
FROM orders INNER JOIN lineitem ON o_orderkey = l_orderkey and l_suppkey > o_orderkey and l_quantity * 20 > o_totalprice;
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN (INNER JOIN) | 0 |
| 2 | TABLE ACCESS ("ORDERS") | 0 |
| 3 | HASH JOIN INSTANT ACCESS | 0 |
| 4 | TABLE ACCESS ("LINEITEM") | 0 |
==========================================================================
1 - JOINED COLUMNS : LINEITEM.L_QUANTITY
2 - READ COLUMNS : O_ORDERKEY, O_TOTALPRICE
3 - INDEX COLUMNS : L_ORDERKEY
TABLE COLUMNS : L_SUPPKEY, L_QUANTITY
READ COLUMNS : L_ORDERKEY, L_SUPPKEY, L_QUANTITY
HASH FILTER : {O_ORDERKEY} = L_ORDERKEY
PHYSICAL TABLE FILTER : L_SUPPKEY > {O_ORDERKEY}
LOGICAL TABLE FILTER : ( L_QUANTITY * 20 ) > {O_TOTALPRICE}
4 - READ COLUMNS : L_ORDERKEY, L_SUPPKEY, L_QUANTITY
<<< end print plan
[HASH JOIN INSTANT ACCESS (UNIQUE)]
● hash join 조건(equi-join)으로 사용되는 column expression을 기준으로 하여, 하위 노드에 대한 결과에
대한 hash instant를 구성한다.
○ 단, 전체 hash column list를 기준으로 중복을 제거한 hash instant를 구성한다.
● hash join method가 semi join 또는 anti semi join인 경우 생성된다.
- example
gSQL> \explain plan only
SELECT o_custkey FROM orders
WHERE o_orderkey in ( SELECT l_orderkey FROM lineitem );
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN (LEFT SEMI) | 0 |
| 2 | TABLE ACCESS ("ORDERS") | 0 |
| 3 | HASH JOIN INSTANT ACCESS (UNIQUE) | 0 |
| 4 | TABLE ACCESS ("LINEITEM") | 0 |
==========================================================================
1 - JOINED COLUMNS : ORDERS.O_CUSTKEY
2 - READ COLUMNS : O_ORDERKEY, O_CUSTKEY
3 - INDEX COLUMNS : L_ORDERKEY
HASH FILTER : {O_ORDERKEY} = L_ORDERKEY
4 - READ COLUMNS : L_ORDERKEY
<<< end print plan
[HASH AGGREGATION]
● 하위 노드의 결과에 대한 Aggregation을 수행한다.
● 하위 노드에서 Aggregation을 처리할 수 없는 경우 별도의 HASH AGGREGATION node가 생성된다.
● Node Information
○ AGGREGATIONS
■ node 내에서 처리할 중첩되지 않은 aggregation list
- example
gSQL> \explain plan only SELECT sum( l_quantity ) FROM orders, lineitem;
>>> start print plan
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH AGGREGATION | 0 |
| 2 | NESTED LOOP JOIN (INNER JOIN) | 0 |
| 3 | TABLE ACCESS ("LINEITEM") | 0 |
| 4 | INDEX ACCESS ("ORDERS, ORDERS_PK_INDEX") | 0 |
==========================================================================
1 - AGGREGATIONS : SUM( L_QUANTITY )
2 - JOINED COLUMNS : LINEITEM.L_QUANTITY
3 - READ COLUMNS : L_QUANTITY
4 - READ INDEX COLUMNS : NOTHING
<<< end print plan
[SUB QUERY FILTER]
● 하위 sub-query 결과에 대한 filter를 처리한다.
● Node Information
○ FILTER
■ sub-query AND-filter
gSQL> \explain plan only
SELECT l_orderkey
FROM lineitem
WHERE l_orderkey in
( SELECT /*+ NO_QUERY_TRANSFORMATION */ o_orderkey
FROM orders );
>>> start print plan
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SUB QUERY FILTER | 0 |
| 2 | TABLE ACCESS ("LINEITEM") | 0 |
| 3 | SUB QUERY LIST | 0 |
| 4 | SUB QUERY FUNCTION (MATERIALIZED) | 0 |
| 5 | INDEX ACCESS ("ORDERS, ORDERS_PK_INDEX") | 0 |
==========================================================================
1 - FILTER : ( LINEITEM.L_ORDERKEY ) IN ( ORDERS.O_ORDERKEY )
2 - READ COLUMNS : L_ORDERKEY
4 - FUNCTION : ( L_ORDERKEY ) IN ( O_ORDERKEY )
5 - READ INDEX COLUMNS : O_ORDERKEY
<<< end print plan
[SUB QUERY FUNCTION ]
● sub-query를 포함하는 function을 처리한다.
● Node Information
○ FUNCTION
■ function expression
gSQL> \explain plan only
SELECT exists ( SELECT /*+ FULL( lineitem ) */ l_orderkey
FROM lineitem
WHERE o_orderkey = l_orderkey ) FROM orders;
>>> start print plan
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX ACCESS ("ORDERS, ORDERS_PK_INDEX") | 0 |
| 2 | SUB QUERY LIST | 0 |
| 3 | SUB QUERY FUNCTION | 0 |
| 4 | TABLE ACCESS ("LINEITEM") | 0 |
==========================================================================
1 - READ INDEX COLUMNS : O_ORDERKEY
3 - FUNCTION : EXISTS( ( L_ORDERKEY ) )
4 - READ COLUMNS : L_ORDERKEY
PHYSICAL FILTER : {O_ORDERKEY} = L_ORDERKEY
[SUB QUERY FUNCTION (MATERIALIZED) ]
● sub-query 수행 결과를 실체화 한 후 function을 처리한다.
● Node Information
○ FUNCTION
■ function expression
gSQL> \explain plan only
SELECT o_orderkey in
( SELECT /*+ FULL( lineitem ) */ l_orderkey FROM lineitem )
FROM orders;
>>> start print plan
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX ACCESS ("ORDERS, ORDERS_PK_INDEX") | 0 |
| 2 | SUB QUERY LIST | 0 |
| 3 | SUB QUERY FUNCTION (MATERIALIZED) | 0 |
| 4 | TABLE ACCESS ("LINEITEM") | 0 |
==========================================================================
1 - READ INDEX COLUMNS : O_ORDERKEY
3 - FUNCTION : ( O_ORDERKEY ) IN ( L_ORDERKEY )
4 - READ COLUMNS : L_ORDERKEY
<<< end print plan
[SUB QUERY LIST ]
● sub-query들을 수행하여 상위 노드로 결과를 전달한다.
● outer column이 존재하는 경우 상수화하여 상위 노드로 전달한다.
○ 상위 노드에서 참조하는 하위 노드의 column을 outer column이라 한다.
● Node Information
○ READ COLUMNS
■ outer column으로 구성할 column list
gSQL> \explain plan only
SELECT ( SELECT count(*) FROM lineitem ) = count(*) FROM orders;
>>> start print plan
< Execution Plan >
==========================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX ACCESS ("ORDERS, ORDERS_PK_INDEX") | 0 |
| 2 | SUB QUERY LIST | 0 |
| 3 | INDEX ACCESS ("LINEITEM, LINEITEM_PK_INDEX") | 0 |
==========================================================================
1 - READ INDEX COLUMNS : NOTHING
AGGREGATIONS : COUNT(*)
2 - READ COLUMNS : COUNT(*)
3 - READ INDEX COLUMNS : NOTHING
AGGREGATIONS : COUNT(*)
<<< end print plan
<< Node Information의 분류 >>
Node Information은 크게 <Column Information>, <Filter Information>, <Aggregation Information>으로 분류되며, 각각의 Node Information은
아래 표와 같다.
<< subquery >>
[subquery]
부질의(Subquery)는 여러 단계로 구성된 검색 요청을 수행할 수 있는 기능을 제공한다. 여러 단계로 구성된 검색 요 청이란 현재 Query의
결과가 하위의 Query 결과에 의하여 결정되어지는 것을 말한다. 예를 들면 특정그룹에 속한 사 람들의 평균나이보다 나이가 많은 사람들을
검색하는 질의는 먼저 특정그룹에 속한 사람들의 평균나이를 구하기 위 한 Query를 수행하고, 이를 이용하여 평균나이보다 많은 사람들을
검색하는 Query를 수행하는 것이다.
SELECT e_name
FROM emp
WHERE e_age > ( SELECT AVG(e_age)
FROM emp WHERE e_dept = 'RND' );
부질의(Subquery)는 <from clause>와 <where clause>에 사용될 수 있으며, <from clause>에 사용되는 부질의를
"inline view"라 하고, <where clause>에 사용되는 부질의를 "nested subquery"라 한다.
nested subquery를 사용하는 경우 nested subquery에 존재하는 table 또는 view의 column 이름과 nested subq uery를 포함하고 있는 Query의 table
또는 view의 column 이름이 동일할 수 있다. 이때 nested subquery의 <sele ct list> 등에 column 이름만 기술한 경우 nested subquery에 존재하는
table 또는 view의 column의 참조를 의미 한다. 만약 nested subquery의 <select list> 등에 nested subquery의 table 또는 view에 존재하지 않는 column
이름이 사용된 경우 nested subquery를 포함하고 있는 Query의 table 또는 view에 해당 column 이름이 존재하면 이를 참조한다.
[스칼라 서브쿼리]
SELECT 절에서 사용하는 Subquery 이다.
한 개의 로우만 반환 한다. 메인 쿼리에서 추출되는 데이터 건 수 만큼 수행되기 때문에 조인으로 수행 될 때보다 수행회수가
적을 수 있다. 일치하는 값이 없는 경우 NULL을 반환 한다.
코드성 테이블에서 코드값을 조회 할 때, 불필요한 조인을 하지 않기 위해 많이 사용한다.
[scalar subquery]
scalar subquery는 결과로 1개의 column을 갖는 1개의 row를 반환하는 subquery이다. scalar subquery의
target은 하나만 존재해야 하며, 결과의 data type은 target의 data type에 따른다. scalar subquery는 select
list의 target에 단독으로 쓰일 수 있으며, 단일 column만을 갖는 연산자에 쓰일 수 있다.
- example
gSQL> SELECT (SELECT c_name FROM dual) FROM customer;
(SELECT C_NAME FROM DUAL)
-------------------------
Customer#1
Customer#2
gSQL> SELECT c_name, c_nation FROM customer WHERE c_nation = (SELECT 'CANADA' FROM dual);
C_NAME C_NATION
---------- --------
Customer#2 CANADA
[row subquery]
row subquery는 결과로 2개 이상의 column을 갖는 1개의 row를 반환하는 subquery이다. row subquery의
target은 2개 이상 존재해야 하며, 결과의 data type은 target들 각각의 data type에 따른다.
row subquery는 select list의 target에 단독으로 쓰일 수 없으며, 둘 이상의 column을 갖는 row 연산자에만
쓰일 수 있다.
- example
gSQL> SELECT p_name, p_brand, p_type FROM part WHERE (p_brand, p_type) = (SELECT 'Brand#1',
'NICKEL' FROM dual);
P_NAME P_BRAND P_TYPE
------ ---------- ------
Part#2 Brand#1 NICKEL
[table subquery]
table subquery는 결과로 1개 이상의 column을 갖는 1개 이상의 row를 반환하는 subquery이다. table
subquery의 target은 1개 이상 존재해야 하며, 결과의 data type은 target들 각각의 data type에 따른다.
table subquery는 select list의 target에 단독으로 쓰일 수 없으며, IN, NOT IN, EXISTS, NOT EXISTS,
Quantify Operator 등의 연산자에 쓰일 수 있다.
- example
gSQL> SELECT s_name, s_nation FROM supplier WHERE s_nation IN (SELECT c_nation FROM customer);
S_NAME S_NATION
------------------------- -------------
Supplier#2 KOREA
Supplier#3 GERMANY
Supplier#4 UNITED STATES
Supplier#5 CANADA
4 rows selected.
<< hint clause >>
[FULL]
기술한 table에 대하여 table full scan을 수행하도록 optimizer에 지시한다. 이 hint가 기술되면 optimizer는 해당
table에 대하여 index scan을 이용한 최적화나 rowid scan을 이용한, index combine을 이용한 최적화 최적화 등을
고려하지 않는다.
FULL hint를 기술시 반드시 table name을 기술해야 하며, 하나의 table name만 기술이 가능하다. 또한 기술된
table name은 반드시 <from clause>에 존재해야 한다. 아래는 T1 table에 대하여 table full scan을 하도록 hint를
적용한 예이다.
# 유형 1: <from clause>에 table name을 기술한 경우
SELECT /*+ FULL(T1) */ I1
FROM T1;
# 유형 2: <from clause>에 alias name을 기술한 경우
SELECT /*+ FULL(T1_ALIAS) */ I1
FROM T1 T1_ALIAS;
[INDEX]
기술한 table에 대하여 index scan을 수행하도록 optimizer에 지시한다. 이 hint가 기술되면 optimizer는 해당 table에
대하여 table scan을 이용한 최적화나 다른 index에 의한 index scan을 이용한 최적화, rowid scan을 이용한 최적화,
index combine을 이용한 최적화 등을 고려하지 않는다. INDEX hint 기술시 table name은 <from clause>에 존재해야
하며, index name은 해당 table에 존재하는 index이어야 한다. index name은 하나 이상 기술하거나 생략 가능하다.
index name을 생략한 경우에는 해당 table에 속한 모든 index들을 대상으로 한다. 만약 index name을 둘 이상 나열하거나
index가 둘 이상인 table에 대하여 index name을 생략한 경우 optimizer는 해당 index들의 index scan cost를 계산하여
가장 좋은 index scan을 선택한다. 아래는 T1 table에 대하여 index scan을 하도록 hint를 적용한 예이다.
# 유형 1: index name을 하나만 기술한 경우
SELECT /*+ INDEX(T1, T1_PK_INDEX) */ I1
FROM T1;
# 유형 2: index name을 둘 이상 기술한 경우
SELECT /*+ INDEX(T1, T1_PK_INDEX T1_UNIQUE_INDEX) */ I1
FROM T1;
# 유형 3: index name을 생략한 경우
SELECT /*+ INDEX(T1) */ I1
FROM T1;
[NO_INDEX]
기술한 table에서 index name에 해당하는 index들의 index scan을 수행하지 않도록 optimizer에 지시한다. 이hint가
기술되면 optimizer는 해당 table에 대하여 기술된 index들에 대한 index scan을 이용한 최적화를 고려하지 않는다.
NO_INDEX hint 기술시 table name은 <from clause>에 존재해야 하며, index name은 해당 table에 존재하는 index
이어야 한다. index name은 하나 이상 기술하거나 생략 가능하며, index name을 생략한 경우 해당 table에 속한 모든
index들을 대상으로 한다. 만약 index name을 생략한다면 optimizer는 해당 table에 대한 index scan을 고려하지 않는다.
NO_INDEX hint에 기술되지 않은 index들이 존재한다면 optimizer는 해당 index들에 대한 index scan cost를 계산하고,
table scan cost 및 rowid scan cost들을 포함하여 가장 좋은 scan 방법을 선택한다. 아래는 T1 table에 대하여 NO_INDEX
hint를 적용한 예이다.
# 유형 1: index name을 하나만 기술한 경우
SELECT /*+ NO_INDEX(T1, T1_PK_INDEX) */ I1
FROM T1;
# 유형 2: index name을 둘 이상 기술한 경우
SELECT /*+ NO_INDEX(T1, T1_PK_INDEX T1_UNIQUE_INDEX) */ I1
FROM T1;
# 유형 3: index name을 생략한 경우
SELECT /*+ NO_INDEX(T1) */ I1
FROM T1;
[INDEX_ASC]
기술한 table에서 ascending index scan을 수행하도록 optimizer에 지시한다. 이 hint가 기술되면 optimizer는
해당 table에 대하여 table scan을 이용한 최적화나 다른 index에 의한 index scan을 이용한 최적화, rowid scan을
이용한 최적화, index combine을 이용한 최적화 등을 고려하지 않는다. 만약 선택된 index scan의 index가 ascending
order로 구성되어 있다면 ascending order로, index가 descending order로 구성되어 있다면 descending order로 index를
scan한다. INDEX_ASC hint에 대한 구문 규칙은 INDEX hint와 동일하다.
[INDEX_DESC]
기술한 table에서 descending index scan을 수행하도록 optimizer에 지시한다. 이 hint가 기술되면 optimizer는 해당 table에
대하여 table scan을 이용한 최적화나 다른 index에 의한 index scan을 이용한 최적화, rowid scan을 이용한 최적화, index combine을
이용한 최적화 등을 고려하지 않는다. 만약 선택된 index scan의 index가 ascending order로 구성되어 있다면 descending order로,
index가 descending order로 구성되어 있다면 ascending order로 index를 scan한다. INDEX_DESC hint에 대한 구문 규칙은 INDEX
hint와 동일하다.
[INDEX_COMBINE]
기술한 table에 대하여 or 구문을 분리하여 각각 index scan을 수행하고 결과들을 합치도록 optimizer에 지시한다. 이 hint가 기술되면
index combine을 이용한 최적화를 우선 고려하며, 만약 index combine이 가능하지 않다면, table scan 또는 index scan, rowid scan
등에 대해 cost를 계산하고 가장 좋은 scan 방법을 선택한다. INDEX_COMBINE hint 기술시 table name은 <from clause>에 존재해야
하며, index name은 해당 table에 존재하는 index이어야 한다. index name은 하나 이상 기술하거나 생략 가능하다. index name을 생략한
경우에는 해당 table에 속한 모든 index들을 대상으로 한다. INDEX_COMBINE hint가 수행되기 위해서는 해당 table을 scan하기 위한
조건에 or 구문이 반드시 존재해야 한다. 만약 or 구문이 존재하지 않으면 optimizer는 해당 hint를 무시하며, 이 경우 table scan 및
index scan, rowid scan에 대한 cost를 계산하여 가장 좋은 scan 방법을 선택한다. index name을 둘 이상 나열하거나 index가 둘 이상인
table에 대하여 index name을 생략한 경우 optimizer는 각 or 구문에 대하여 해당 index들의 index scan cost를 계산하여 가장 좋은
index scan을 선택한다. 따라서 or 구문으로 분리된 조건들에 의해 각각 다른 index를 이용한 index scan이 선택될 수 있다.
아래는 T1 table에 대하여 index combine을 하도록 hint를 적용한 예이다.
# 유형 1: index name을 하나만 기술한 경우
SELECT /*+ INDEX_COMBINE(T1, T1_PK_INDEX) */ I1
FROM T1
WHERE I1 = 1
OR I1 = 2;
# 유형 2: index name을 둘 이상 기술한 경우
SELECT /*+ INDEX_COMBINE(T1, T1_PK_INDEX T1_UNIQUE_INDEX) */ I1
FROM T1
WHERE I1 = 1
OR I2 = 2;
# 유형 3: index name을 생략한 경우
SELECT /*+ INDEX_COMBINE(T1) */ I1
FROM T1
WHERE I1 = 1
OR I2 = 2;
# 유형 4: INDEX_COMBINE hint 적용이 불가능한 경우 (or 구문이 없는 경우)
SELECT /*+ INDEX_COMBINE(T1, T1_PK_INDEX) */ I1
FROM T1
WHERE I1 = 1;
<< 진행 >>
[ROWID]
기술한 table에 대하여 rowid scan을 수행하도록 optimizer에 지시한다. 이 hint가 기술되면 rowid scan을 이용한
최적화를 우선 고려하며, 만약 rowid scan이 가능하지 않다면, table scan 또는 index scan, index combine 등에
대해 cost를 계산하고 가장 좋은 scan 방법을 선택한다.
ROWID hint를 기술시 반드시 table name을 기술해야 하며, 하나의 table name만 기술이 가능하다. 또한 기술된
table name은 반드시 <from clause>에 존재해야 한다.
ROWID hint가 수행되기 위해서는 해당 table을 scan하기 위한 조건에 ROWID를 이용한 Equal 조건이 반드시
존재해야 한다. 만약 존재하지 않으면 optimizer는 해당 hint를 무시하며, 이 경우 table scan 및 index scan, rowid
scan, index combine에 대한 cost를 계산하여 가장 좋은 scan 방법을 선택한다. 아래는 T1 table에 대하여 rowid
scan을 하도록 hint를 적용한 예이다.
# 유형 1: <from clause>에 table name을 기술한 경우
SELECT /*+ ROWID(T1) */ I1
FROM T1
WHERE ROWID = 'AAAAAAAAADXAACAAAGAlAAA';
# 유형 2: <from clause>에 alias name을 기술한 경우
SELECT /*+ ROWID(T1_ALIAS) */ I1
FROM T1 T1_ALIAS
WHERE ROWID = 'AAAAAAAAADXAACAAAGAlAAA';
# 유형 3: ROWID hint 적용이 불가능한 경우 (rowid 조건이 없는 경우)
SELECT /*+ ROWID(T1) */ I1
FROM T1
WHERE I1 = 1;
[ORDERED]
table들에 대하여 join을 수행할때 <from clause>에 기술한 순서대로 join을 수행하도록 optimizer에 지시한다. 이
hint는 <from clause>에 ','로 구분된 table들을 기술되거나 inner join으로 table들이 기술되는 경우에만 적용 가능
하다. 아래는 T1, T2 table에 대한 join에 대하여 ORDERED hint를 적용한 예이다.
# 유형 1: <from clause>에 ','로 구분된 table들을 기술한 경우
SELECT /*+ ORDERED */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
# 유형 2: <from clause>에 inner join으로 table들을 기술한 경우
SELECT /*+ ORDERED */ *
FROM T1 INNER JOIN T2 ON T1.I1 = T2.I1;
# 유형 3: ORDERED hint 적용이 불가능한 경우 (outer join인 경우)
SELECT /*+ ORDERED */ *
FROM T1 LEFT OUTER JOIN T2 ON T1.I1 = T2.I1;
[ORDERING]
join을 수행할때 이 hint에 기술한 table들 순서대로 join을 수행하도록 optimizer에 지시한다. 이 hint는 <from
clause>에 ','로 구분된 table들을 기술되거나 inner join으로 table들이 기술되는 경우에만 적용 가능하다.
ORDERING hint의 경우 각 table에 대하여 위치지정 옵션을 기술할 수 있는데 1번째와 2번째 table에 대해서는
기술할 수 없고 3번째 table부터 기술 가능하다. 1번째와 2번째 table의 위치지정은 ORDERING hint에 기술한
순서에 의해 결정이 가능하다. 위치지정 옵션은 'LEFT'와 'RIGHT'가 존재하며, 'LEFT'는 해당 table을 join의 left
node(outer node)로 위치하도록 하는 것을 의미하며, 'RIGHT'는 해당 table을 join의 right node(inner node)로
위치하도록 하는 것을 의미한다.
만약 table에 위치지정 옵션을 기술한 경우 해당 table은 join시 위치지정 옵션에 기술한 위치에 고정하여 join을
수행하게 되며, table에 위치지정 옵션을 기술하지 않은 경우 optimizer는 해당 table을 left node(outer node)로
위치하는 경우와 right node(inner node)로 위치하는 경우에 대해 각각 cost를 계산하고 가장 좋은 join 순서를
선택한다.
# 유형 1: <from clause>에 ','로 구분된 table들을 기술한 경우
SELECT /*+ ORDERING(T2, T3, T1) */ *
FROM T1, T2, T3
WHERE T1.I1 = T2.I1
AND T2.I2 = T3.I2;
\explain plan verbose
select /*+ ordering(t2,t3,t1) use_nl(t3,t1) */ *
from t1,t2,t3
where t1.c1 = t2.c1 and t2.c1 = t3.c1;
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS | Total Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0:00:00.00 |
| 1 | NESTED LOOP JOIN (INNER JOIN) | 2 | 0:00:00.00 |
| 2 | NESTED LOOP JOIN (INNER JOIN) | 2 | 0:00:00.00 |
| 3 | TABLE ACCESS ("T2") | 2 | 0:00:00.00 |
| 4 | INDEX ACCESS ("T3, T3_PRIMARY_KEY_INDEX") | 2 | 0:00:00.00 |
| 5 | INDEX ACCESS ("T1, T1_PRIMARY_KEY_INDEX") | 2 | 0:00:00.00 |
==================================================================================================
1 - JOINED COLUMNS : T1.C1, T1.C2, T2.C1, T2.C2, T3.C1, T3.C2
2 - JOINED COLUMNS : T2.C1, T2.C2, T3.C1, T3.C2
3 - READ COLUMNS : C1, C2
4 - READ INDEX COLUMNS : C1
READ TABLE COLUMNS : C2
MIN RANGE : C1 = {C1}
MAX RANGE : C1 = {C1}
5 - READ INDEX COLUMNS : C1
READ TABLE COLUMNS : C2
MIN RANGE : C1 = {C1}
MAX RANGE : C1 = {C1}
<<< end print plan
# 유형 2: <from clause>에 inner join으로 table들을 기술한 경우
SELECT /*+ ORDERING(T2, T3, T1) */ *
FROM (T1 INNER JOIN T2 ON T1.I1 = T2.I1) INNER JOIN T3 ON T2.I2 = T3.I2;
\explain plan verbose
SELECT /*+ ORDERING(T2, T3, T1) use_nl(t3,t1) */ *
FROM (T1 INNER JOIN T2 ON T1.c1 = T2.c1) INNER JOIN T3 ON T2.c1 = T3.c1;
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS | Total Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0:00:00.00 |
| 1 | NESTED LOOP JOIN (INNER JOIN) | 2 | 0:00:00.00 |
| 2 | NESTED LOOP JOIN (INNER JOIN) | 2 | 0:00:00.00 |
| 3 | TABLE ACCESS ("T2") | 2 | 0:00:00.00 |
| 4 | INDEX ACCESS ("T3, T3_PRIMARY_KEY_INDEX") | 2 | 0:00:00.00 |
| 5 | INDEX ACCESS ("T1, T1_PRIMARY_KEY_INDEX") | 2 | 0:00:00.00 |
==================================================================================================
1 - JOINED COLUMNS : T1.C1, T1.C2, T2.C1, T2.C2, T3.C1, T3.C2
2 - JOINED COLUMNS : T2.C1, T2.C2, T3.C1, T3.C2
3 - READ COLUMNS : C1, C2
4 - READ INDEX COLUMNS : C1
READ TABLE COLUMNS : C2
MIN RANGE : C1 = {C1}
MAX RANGE : C1 = {C1}
5 - READ INDEX COLUMNS : C1
READ TABLE COLUMNS : C2
MIN RANGE : C1 = {C1}
MAX RANGE : C1 = {C1}
<<< end print plan
# 유형 3: ORDERING hint에 위치지정 옵션을 기술한 경우
SELECT /*+ ORDERING(T2, T3, T1 LEFT) */ *
FROM T1, T2, T3
WHERE T1.I1 = T2.I1
AND T2.I2 = T3.I2;
# 유형 4: ORDERING hint 적용이 불가능한 경우 (outer join인 경우)
SELECT /*+ ORDERING(T1, T2) */ *
FROM T1 LEFT OUTER JOIN T2 ON T1.I1 = T2.I1;
# 유형 5: ORDERING hint에서 위치지정 옵션을 잘못 사용한 경우 (1번째 table에 사용한 경우)
SELECT /*+ ORDERING(T2 RIGHT, T3, T1) */ *
FROM T1, T2, T3
WHERE T1.I1 = T2.I1
AND T2.I2 = T3.I2;
--> right, left option을 3번째 부터 지정이 가능하다는 것이다.
/*+ ordering(t2, t3 ,t1 right) */
[LEADING]
join을 수행할때 이 hint에 기술한 table들 순서대로 join을 수행하도록 optimizer에 지시한다. 이 hint는 <from
clause>에 ','로 구분된 table들을 기술되거나 inner join으로 table들이 기술되는 경우에만 적용 가능하다.
LEADING hint의 경우 ORDERING hint와 달리 위치지정이 불가능하기 때문에 join에 참여하는 table의 순서만
지정이 가능하다. 따라서 1번째와 2번째 table은 순서에 의해 각각 left node(outer node)와 right node(inner
node)로 위치가 결정되며, 3번째 table부터는 optimizer가 해당 table이 left node(outer node)에 위치하는
경우와 right node(inner node)에 위치하는 경우에 대하여 cost를 계산하고 둘 중 더 좋은 위치를 선택한다.
아래는 T1, T2, T3 table들에 대한 join에 대하여 LEADING hint를 적용한 예이다.
# 유형 1: <from clause>에 ','로 구분된 table들을 기술한 경우
SELECT /*+ LEADING(T2, T3, T1) */ *
FROM T1, T2, T3
WHERE T1.I1 = T2.I1
AND T2.I2 = T3.I2;
# 유형 2: <from clause>에 inner join으로 table들을 기술한 경우
SELECT /*+ LEADING(T2, T3, T1) */ *
FROM (T1 INNER JOIN T2 ON T1.I1 = T2.I1) INNER JOIN T3 ON T2.I2 = T3.I2;
# 유형 3: LEADING hint 적용이 불가능한 경우 (outer join인 경우)
SELECT /*+ LEADING(T1, T2) */ *
FROM T1 LEFT OUTER JOIN T2 ON T1.I1 = T2.I1;
[USE_HASH]
join을 수행할때 이 hint에 기술한 table이 포함되면 hash join 기법을 사용하여 join을 수행하도록 optimizer에
지시한다. 이 hint는 모든 join type에 대하여 적용 가능하다. USE_HASH hint는 하나 이상의 table을 기술해야
하며, 동일한 table을 둘 이상 기술할 수 없다. 이 hint에 기술된 table이 USE_MERGE, USE_NL, USE_INL 등의
hint에서 기술되면 안되며, 기술하는 경우 "hint_error" property가 on인 경우 validation error로 처리되며, off인
경우 먼저 기술된 hint가 적용된다. 또한 join에 참여하는 두 table에 대하여 각각 다른 join operation hint가
기술되어 있다면 left node(outer node)에 기술된 hint가 우선 적용된다. USE_HASH hint에 기술된 table이
join에 참여하는 경우 join 조건에 hash join이 가능한 조건(equi-join이어야 하고 비교 가능한 column이어야 함)이
존재해야 하며, 만약 hash join이 가능한 조건이 존재하지 않는다면 optimizer는 해당 hint를 무시하고 cost 계산에
의해 가장 좋은 join operation을 선택한다. 아래는 T1, T2 table들에 대한 join에 대하여 USE_HASH hint를 적용한 예이다.
# 유형 1: 한 table에 대해서만 join operation hint를 기술한 경우
SELECT /*+ USE_HASH(T1) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
# 유형 2: 두 table에 대하여 각각 다른 join operation hint를 기술한 경우 (ORDERED hint에 의해
순서가 지정되어 left node(outer node)에 위치한 T1 table에 대한 join operation hint(USE_HASH)가
적용됨)
SELECT /*+ ORDERED USE_HASH(T1) USE_MERGE(T2) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
# 유형 3: USE_HASH hint 적용이 불가능한 경우 (hash join 조건이 없는 경우)
SELECT /*+ USE_HASH(T1, T2) */ *
FROM T1, T2
WHERE T1.I1 < T2.I1;
[NO_USE_HASH]
join을 수행할때 이 hint에 기술한 table이 포함되면 hash join 기법을 제외한 나머지 기법 중 하나를 선택하여
join을 수행하도록 optimizer에 지시한다. 이 hint는 모든 join type에 대하여 적용 가능하다.
NO_USE_HASH hint는 하나 이상의 table을 기술해야 하며, 동일한 table을 둘 이상 기술할 수 없다. 이 hint에
기술된 table은 USE_HASH hint를 제외한 다른 join operation hint에서 기술할 수 있다. 이 hint에 기술된 table이
USE_HASH hint에 기술된 경우 "hint_error" property가 on인 경우 validation error로 처리되며, off인 경우 먼저
기술된 hint가 적용된다. 또한 join에 참여하는 두 table에 대하여 각각 다른 join operation hint가 기술되어 있다면
left node(outer node)에 기술된 hint가 우선 적용된다.
NO_USE_HASH hint에 기술된 table이 join에 참여하는 경우 optimizer는 hash join을 제외한 나머지 join
operation 기법들에 대하여 cost를 계산하고 그 중 가장 좋은 join operation을 선택한다.
아래는 T1, T2 table들에 대한 join에 대하여 NO_USE_HASH hint를 적용한 예이다.
# 유형 1: 한 table에 대해서만 join operation hint를 기술한 경우
SELECT /*+ NO_USE_HASH(T1) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
# 유형 2: 두 table에 대하여 각각 다른 join operation hint를 기술한 경우 (T2 table에 기술한
hint가 적용되어 merge join이 적용됨)
SELECT /*+ NO_USE_HASH(T1) USE_MERGE(T2) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
[USE_NL]
join을 수행할때 이 hint에 기술한 table이 포함되면 nested loops join 기법을 사용하여 join을 수행하도록
optimizer에 지시한다. 이 hint는 모든 join type에 대하여 적용 가능하다.
USE_NL hint는 하나 이상의 table을 기술해야 하며, 동일한 table을 둘 이상 기술할 수 없다. 이 hint에 기술된
table이 USE_HASH, USE_MERGE, USE_INL 등의 hint에서 기술되면 안되며, 기술하는 경우 "hint_error"
property가 on인 경우 validation error로 처리되며, off인 경우 먼저 기술된 hint가 적용된다. 또한 join에 참여하는
두 table에 대하여 각각 다른 join operation hint가 기술되어 있다면 left node(outer node)에 기술된 hint가 우선
적용된다.
nested loops join은 hash join 및 merge join과 다르게 어떠한 제약없이 해당 기법으로 join 수행이 가능하다.
아래는 T1, T2 table들에 대한 join에 대하여 USE_NL hint를 적용한 예이다.
# 유형 1: 한 table에 대해서만 join operation hint를 기술한 경우
SELECT /*+ USE_NL(T1) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
# 유형 2: 두 table에 대하여 각각 다른 join operation hint를 기술한 경우 (ORDERED hint에 의해
순서가 지정되어 left node(outer node)에 위치한 T1 table에 대한 join operation hint(USE_NL)가
적용됨)
SELECT /*+ ORDERED USE_NL(T1) USE_HASH(T2) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
[NO_USE_NL]
join을 수행할때 이 hint에 기술한 table이 포함되면 nested loops join 기법을 제외한 나머지 기법 중 하나를
선택하여 join을 수행하도록 optimizer에 지시한다. 이 hint는 모든 join type에 대하여 적용 가능하다.
NO_USE_NL hint는 하나 이상의 table을 기술해야 하며, 동일한 table을 둘 이상 기술할 수 없다. 이 hint에 기술된
table은 USE_NL hint를 제외한 다른 join operation hint에서 기술할 수 있다. 이 hint에 기술된 table이 USE_NL
hint에 기술된 경우 "hint_error" property가 on인 경우 validation error로 처리되며, off인 경우 먼저 기술된
hint가 적용된다. 또한 join에 참여하는 두 table에 대하여 각각 다른 join operation hint가 기술되어 있다면 left
node(outer node)에 기술된 hint가 우선 적용된다.
NO_USE_NL hint에 기술된 table이 join에 참여하는 경우 optimizer는 nested loops join을 제외한 나머지 join
operation 기법들에 대하여 cost를 계산하고 그 중 가장 좋은 join operation을 선택한다. 단, 제약에 의해 다른 join
operation 기법들이 사용될 수 없는 경우 optimizer는 이 hint를 무시하고 nested loops join을 이용한 기법을
선택한다. 아래는 T1, T2 table들에 대한 join에 대하여 NO_USE_NL hint를 적용한 예이다.
# 유형 1: 한 table에 대해서만 join operation hint를 기술한 경우
SELECT /*+ NO_USE_NL(T1) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
# 유형 2: 두 table에 대하여 각각 다른 join operation hint를 기술한 경우 (T2 table에 기술한
hint가 적용되어 hash join이 적용됨)
SELECT /*+ NO_USE_NL(T1) USE_HASH(T2) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
# 유형 3: NO_USE_NL hint를 기술하였지만 제약조건에 의해 hash join 등의 다른 join operation이
적용될 수 없는 경우(nested loops join이 적용됨)
SELECT /*+ NO_USE_NL(T1) */ *
FROM T1, T2
WHERE T1.I1 < T2.I1;
[USE_INL]
join을 수행할때 이 hint에 기술한 table이 포함되면 sort instant를 이용한 nested loops join 기법을 사용하여
join을 수행하도록 optimizer에 지시한다. 이 hint는 <from clause>에 ','로 구분된 table들을 기술되거나 inner
join으로 table들이 기술되는 경우에만 적용 가능하다.
USE_INL hint는 하나 이상의 table을 기술해야 하며, 동일한 table을 둘 이상 기술할 수 없다. 이 hint에 기술된
table이 USE_HASH, USE_MERGE, USE_NL 등의 hint에서 기술되면 안되며, 기술하는 경우 "hint_error"
property가 on인 경우 validation error로 처리되며, off인 경우 먼저 기술된 hint가 적용된다. 또한 join에 참여하는
두 table에 대하여 각각 다른 join operation hint가 기술되어 있다면 left node(outer node)에 기술된 hint가 우선
적용된다.
sort instant를 이용한 nested loops join은 right node(inner node)의 table에 대하여 조인 조건에 해당하는
expression들을 sort key로 하는 sort instant를 생성하고 이를 이용하여 nested loops join을 수행하는 방법이다.
이때 sort key는 key compare가 가능한 type이어야 한다. 조인 조건에 해당하는 expression들이 모두 key
compare가 불가능한 type인 경우 이 기법의 적용이 불가능하며, 이 경우 다른 join operation 기법들에 대하여
cost를 계산하고 그 중 가장 좋은 기법을 적용한다.
아래는 T1, T2 table들에 대한 join에 대하여 USE_INL hint를 적용한 예이다.
# 유형 1: 한 table에 대해서만 join operation hint를 기술한 경우
SELECT /*+ USE_INL(T1) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
# 유형 2: 두 table에 대하여 각각 다른 join operation hint를 기술한 경우 (ORDERED hint에 의해
순서가 지정되어 left node(outer node)에 위치한 T1 table에 대한 join operation hint(USE_INL)가
적용됨)
SELECT /*+ ORDERED USE_INL(T1) USE_HASH(T2) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
gSQL> \explain plan verbose
2 select /*+ use_inl(t1) */ * from t1,t2 where t1.c1 = t2.c1;
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS | Total Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0:00:00.00 |
| 1 | NESTED LOOP JOIN (INNER JOIN) | 3 | 0:00:00.00 |
| 2 | TABLE ACCESS ("T1") | 3 | 0:00:00.00 |
| 3 | SORT INSTANT ACCESS | 3 | 0:00:00.00 |
| 4 | TABLE ACCESS ("T2") | 3 | 0:00:00.00 |
==================================================================================================
[NO_USE_INL]
join을 수행할때 이 hint에 기술한 table이 포함되면 sort instant를 이용한 nested loops join 기법을 제외한 나머지
기법 중 하나를 선택하여 join을 수행하도록 optimizer에 지시한다. 이 hint는 모든 join type에 대하여 적용
가능하다.
NO_USE_INL hint는 하나 이상의 table을 기술해야 하며, 동일한 table을 둘 이상 기술할 수 없다. 이 hint에 기술된
table은 USE_INL hint를 제외한 다른 join operation hint에서 기술할 수 있다. 이 hint에 기술된 table이 USE_INL
hint에 기술된 경우 "hint_error" property가 on인 경우 validation error로 처리되며, off인 경우 먼저 기술된
hint가 적용된다. 또한 join에 참여하는 두 table에 대하여 각각 다른 join operation hint가 기술되어 있다면 left
node(outer node)에 기술된 hint가 우선 적용된다.
NO_USE_INL hint에 기술된 table이 join에 참여하는 경우 optimizer는 sort instant를 이용한 nested loops
join을 제외한 나머지 join operation 기법들에 대하여 cost를 계산하고 그 중 가장 좋은 join operation을 선택한다.
아래는 T1, T2 table들에 대한 join에 대하여 NO_USE_INL hint를 적용한 예이다.
# 유형 1: 한 table에 대해서만 join operation hint를 기술한 경우
SELECT /*+ NO_USE_INL(T1) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
# 유형 2: 두 table에 대하여 각각 다른 join operation hint를 기술한 경우 (T2 table에 기술한
hint가 적용되어 hash join이 적용됨)
SELECT /*+ NO_USE_INL(T1) USE_HASH(T2) */ *
FROM T1, T2
WHERE T1.I1 = T2.I1;
[UNNEST]
subquery에 대하여 동일한 결과를 보장하는 join 구문으로 변경하도록 optimizer에 지시한다. 이는 subquery를 별
도로 수행하지 않고 상위 레벨의 query와의 join 처리 방식을 이용하여 처리하도록 하여 반복적인 subquery를 수행
하지 않도록 한다.
UNNEST hint는 subquery의 <hint clause>에만 기술가능하며, 해당 subquery에만 적용되고 하위 subquery들에
는 적용되지 않는다. 만약 다수의 subquery가 존재하고 이를 모두 unnest하고 싶은 경우 해당 subquery 모두에
UN NEST hint를 기술하여야 하며, subquery 내부에 존재하는 subquery에 대하여 unnest하고 싶은 경우 해당 subque ry에
UNNEST hint를 기술하여야 한다.
UNNEST hint가 NO_QUERY_TRANSFORMATION hint와 같이 기술된 경우 NO_QUERY_TRANSFORMATION
hint에 의하여 UNNEST hint가 무시된다.
UNNEST hint는 NO_UNNEST hint와 동시에 사용할 수 없다. 동시에 사용하는 경우 "hint_error" property를 on으
로 설정한 경우 validation error로 처리되며, off로 설정한 경우 먼저 기술한 hint가 적용된다.
아래는 UNNEST hint를 이용하여 subquery의 unnesting을 적용한 예이다.
# 유형 1: IN subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 IN ( SELECT /*+ UNNEST */ I1
FROM T2 );
# 유형 2: UNNEST hint와 NO_QUERY_TRANSFORMATION hint를 기술한 경우 (NO_QUERY_TRANSFORMATION
hint에 의해 subquery를 unnesting하지 않음)
SELECT *
FROM T1
WHERE I1 IN ( SELECT /*+ UNNEST NO_QUERY_TRANSFORMATION */ I1
FROM T2 );
[NL_SJ]
subquery에 대하여 nested loops semi join으로 처리하도록 optimizer에 지시한다. 이는 subquery에 대하여 동일
한 결과를 갖는 semi join 형태로 풀어내도록 한다.
NL_SJ의 경우 EXISTS, IN, ANY quantify 연산자에 사용 가능하며, NOT EXISTS, NOT IN, ALL quantify 연산자에
는 사용이 불가능하다.
NL_SJ hint는 해당 subquery를 semi join 형태로 풀어내기 때문에 anti-semi join 형태로만 풀릴 수 있는 subquery
에 기술한 경우 optimizer는 이 hint를 무시한다.
아래는 NL_SJ hint를 이용하여 subquery를 join 형태로 풀어낸 예이다.
# 유형 1: IN subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 IN ( SELECT /*+ NL_SJ */ I1
FROM T2 );
# 유형 2: EXISTS subquery에 사용한 경우
SELECT *
FROM T1
WHERE EXISTS ( SELECT /*+ NL_SJ */ I1
FROM T2
WHERE T1.I1 = T2.I1 );
# 유형 3: quantify 연산자의 subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 < ANY ( SELECT /*+ NL_SJ */ I1
FROM T2 );
# 유형 4: NOT IN subquery에 사용한 경우(anti-semi join으로 풀 수 있는 연산자이므로 hint가
무시됨)
SELECT *
FROM T1
WHERE I1 NOT IN ( SELECT /*+ NL_SJ */ I1
FROM T2 );
[NL_ISJ]
subquery에 대하여 nested loops inverted semi join으로 처리하도록 optimizer에 지시한다. 이는 subquery에 대
하여 동일한 결과를 갖는 inverted semi join 형태로 풀어내도록 한다.
inverted semi join이란 right node(inner node)를 semi join을 위한 key를 unique sort key로 갖는 sort instant
를 생성하여 해당 key들을 이용하여 역으로 left node(outer node)에서 해당 key와 일치하는 레코드를 찾는 semi j
oin 방법이다.
NL_ISJ hint는 right node(inner node)가 semi join을 위한 key에 대하여 중복률이 높고, left node(outer node)
에서 해당 key로 index scan을 할 수 있는 경우 유리하다. optimizer는 semi join을 위한 key에 대하여 left node(o
uter node)에서 index scan을 할 수 없는 경우 hint를 무시한다. 또한, semi join을 위한 key가 key compare가 불
가능한 경우 이 hint를 무시한다.
NL_ISJ의 경우 EXISTS, IN, = ANY quantify 연산자에 사용 가능하며, NOT EXISTS, NOT IN, ALL quantify 연산자
및 = ANY를 제외한 ANY quantify 연산자에는 사용이 불가능하다.
NL_ISJ hint는 해당 subquery를 semi join 형태로 풀어내기 때문에 anti-semi join 형태로만 풀릴 수 있는 subquer
y에 기술한 경우 optimizer는 이 hint를 무시한다. 아래는 NL_ISJ hint를 이용하여 subquery를 join 형태로 풀어낸 예이다.
# 유형 1: IN subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 IN ( SELECT /*+ NL_ISJ */ I1 FROM T2 );
# 유형 2: EXISTS subquery에 사용한 경우
SELECT *
FROM T1
WHERE EXISTS ( SELECT /*+ NL_ISJ */ I1
FROM T2
WHERE T1.I1 = T2.I1 );
# 유형 3: quantify 연산자의 subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 < ANY ( SELECT /*+ NL_ISJ */ I1
FROM T2 );
# 유형 4: NOT IN subquery에 사용한 경우(anti-semi join으로 풀 수 있는 연산자이므로 hint가
무시됨)
SELECT *
FROM T1
WHERE I1 NOT IN ( SELECT /*+ NL_ISJ */ I1
FROM T2 );
[ NL_SJ 와 NL_ISJ]
1. NL_SJ 조인에서 아래와 같이 T2는 무조건 unique 하다는 것을 조건으로 조인을 한다.
2. NL_ISJ는 NS_SJ join에서 join 되는 Table를 바꾸어 하는 것을 의미한다.
ex) select c1,c2 from t1 a where exists ( select /*+ NL_ISJ */ * from t2 where c1 = a.c1 ) and a.c2 = 3;
세미조인으로 안에 먼저 풀리는데 원래는 t1이 먼저 풀리는데 만약 t1 보다 t2가 더 적다면 t2로 먼저
풀리고 싶을 때가 있다. 이 때에 거꾸로 풀리기 위해 NL_ISJ hint를 쓴다.
3. NL_SJ 와 NL_ISJ의 차이점은 세미조인으로 풀릴 때 NL_ISJ로 하였을 경우 조인 처리를 바꾸어서 하는것이 다른 점이다.
create table t1 (c1 int primary key, c2 int);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
create table t2 (c1 int primary key, c2 int);
insert into t2 values(2,2);
insert into t2 values(3,3);
select * from t1 where exists ( select * from t2 where t1.c1 = t2.c1 );
select c1,c2 from t1 a where exists ( select * from t2 where c1 = a.c1 );
select c1,c2 from t1 a where exists ( select /*+ NL_ISJ */ * from t2 where c1 = a.c1 ) and a.c2 = 3;
select c1,c2 from t1 a where exists ( select /*+ NL_SJ */ * from t2 where c1 = a.c1 ) and a.c2 = 3;
gSQL> \explain plan on
2 select c1,c2 from t1 a where exists ( select /*+ NL_ISJ */ * from t2 where c1 = a.c1 ) and a.c2 = 3;
C1 C2
-- --
3 3
1 row selected.
>>> start print plan
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOP JOIN (INVERTED LEFT SEMI) | 1 |
| 2 | TABLE ACCESS ("T2") | 2 |
| 3 | INDEX ACCESS ("T1" AS A, "T1_PRIMARY_KEY_INDEX") | ( 2) 1 |
==================================================================================================
1 - JOINED COLUMNS : A.C1, A.C2
2 - READ COLUMNS : C1
3 - READ INDEX COLUMNS : C1
READ TABLE COLUMNS : C2
MIN RANGE : C1 = {C1}
MAX RANGE : C1 = {C1}
PHYSICAL TABLE FILTER : C2 = 3
<<< end print plan
gSQL> \explain plan on
2 select c1,c2 from t1 a where exists ( select /*+ NL_SJ */ * from t2 where c1 = a.c1 ) and a.c2 = 3;
C1 C2
-- --
3 3
1 row selected.
>>> start print plan
< Execution Plan >
==================================================================================================
| IDX | NODE DESCRIPTION | ROWS |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOP JOIN (LEFT SEMI) | 1 |
| 2 | INDEX ACCESS ("T1" AS A, "T1_IDX2") | ( 1) 1 |
| 3 | INDEX ACCESS ("T2", "T2_PRIMARY_KEY_INDEX") | ( 1) 1 |
==================================================================================================
1 - JOINED COLUMNS : A.C1, A.C2
2 - READ INDEX COLUMNS : C2
READ TABLE COLUMNS : C1
MIN RANGE : C2 = 3
MAX RANGE : C2 = 3
3 - READ INDEX COLUMNS : C1
MIN RANGE : C1 = {C1}
MAX RANGE : C1 = {C1}
<<< end print plan
--> 여기서 NL_ISJ로 하였을 경우 TABLE_ACCESS로 풀리는 이유는 NL_ISJ로 풀리면서 조인 순서가 T2로 되었고
T2에 대한 조인 조건이 없기 때문에 TABLE_ACCESs로 풀리게 되는 것이다.
참고로 T2에 primary key가 아니라면 sort merge join이 먼저 풀린다.
[MERGE_SJ]
subquery에 대하여 merge semi join으로 처리하도록 optimizer에 지시한다. 이는 subquery에 대하여 동일한 결과
를 갖는 semi join 형태로 풀어내도록 한다.
MERGE_SJ의 경우 EXISTS, IN, = ANY quantify 연산자에 사용 가능하며, NOT EXISTS, NOT IN, ALL quantify 연
산자, = ANY를 제외한 ANY quantify 연산자에는 사용이 불가능하다.
MERGE_SJ hint는 해당 subquery를 semi join 형태로 풀어내기 때문에 anti-semi join 형태로만 풀릴 수 있는 sub
query에 기술한 경우 optimizer는 이 hint를 무시한다. 또한, merge semi join에 사용할 key가 key compare가 불
가능하다면 optimizer는 이 hint를 무시한다.
아래는 MERGE_SJ hint를 이용하여 subquery를 join 형태로 풀어낸 예이다.
# 유형 1: IN subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 IN ( SELECT /*+ MERGE_SJ */ I1
FROM T2 );
# 유형 2: EXISTS subquery에 사용한 경우
SELECT *
FROM T1
WHERE EXISTS ( SELECT /*+ MERGE_SJ */ I1
FROM T2
WHERE T1.I1 = T2.I1 );
# 유형 3: quantify 연산자의 subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 = ANY ( SELECT /*+ MERGE_SJ */ I1
FROM T2 );
# 유형 4: NOT IN subquery에 사용한 경우(anti-semi join으로 풀 수 있는 연산자이므로 hint가
무시됨)
SELECT *
FROM T1
WHERE I1 NOT IN ( SELECT /*+ MERGE_SJ */ I1
FROM T2 );
[HASH_SJ]
subquery에 대하여 hash semi join으로 처리하도록 optimizer에 지시한다. 이는 subquery에 대하여 동일한 결과
를 갖는 semi join 형태로 풀어내도록 한다.
HASH_SJ의 경우 EXISTS, IN, = ANY quantify 연산자에 사용 가능하며, NOT EXISTS, NOT IN, ALL quantify 연산
자, = ANY를 제외한 ANY quantify 연산자에는 사용이 불가능하다.
HASH_SJ hint는 해당 subquery를 semi join 형태로 풀어내기 때문에 anti-semi join 형태로만 풀릴 수 있는 subqu
ery에 기술한 경우 optimizer는 이 hint를 무시한다. 또한, hash semi join에 사용할 key가 key compare가 불가능
하다면 optimizer는 이 hint를 무시한다.
아래는 HASH_SJ hint를 이용하여 subquery를 join 형태로 풀어낸 예이다.
# 유형 1: IN subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 IN ( SELECT /*+ HASH_SJ */ I1
FROM T2 );
# 유형 2: EXISTS subquery에 사용한 경우
SELECT *
FROM T1
WHERE EXISTS ( SELECT /*+ HASH_SJ */ I1
FROM T2
WHERE T1.I1 = T2.I1 );
# 유형 3: quantify 연산자의 subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 = ANY ( SELECT /*+ HASH_SJ */ I1
FROM T2 );
# 유형 4: NOT IN subquery에 사용한 경우(anti-semi join으로 풀 수 있는 연산자이므로 hint가
무시됨)
SELECT *
FROM T1
WHERE I1 NOT IN ( SELECT /*+ HASH_SJ */ I1
FROM T2 );
[HASH_ISJ]
subquery에 대하여 hash inverted semi join으로 처리하도록 optimizer에 지시한다. 이는 subquery에 대하여 동
일한 결과를 갖는 inverted semi join 형태로 풀어내도록 한다.
HASH_ISJ hint는 left node(outer node)의 row 개수가 적고, right node(inner node)의 row 개수가 많은 경우
유리하다. optimizer는 semi join을 위한 key가 key compare가 불가능한 경우 이 hint를 무시한다.
HASH_ISJ의 경우 EXISTS, IN, = ANY quantify 연산자에 사용 가능하며, NOT EXISTS, NOT IN, ALL quantify 연
산자 및 = ANY를 제외한 ANY quantify 연산자에는 사용이 불가능하다.
HASH_ISJ hint는 해당 subquery를 semi join 형태로 풀어내기 때문에 anti-semi join 형태로만 풀릴 수 있는 subq
uery에 기술한 경우 optimizer는 이 hint를 무시한다. 또한, hash semi join에 사용할 key가 key compare가 불가능
하다면 optimizer는 이 hint를 무시한다.
아래는 HASH_ISJ hint를 이용하여 subquery를 join 형태로 풀어낸 예이다.
# 유형 1: IN subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 IN ( SELECT /*+ HASH_ISJ */ I1
FROM T2 );
# 유형 2: EXISTS subquery에 사용한 경우
SELECT *
FROM T1
WHERE EXISTS ( SELECT /*+ HASH_ISJ */ I1
FROM T2
WHERE T1.I1 = T2.I1 );
# 유형 3: quantify 연산자의 subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 = ANY ( SELECT /*+ HASH_ISJ */ I1
FROM T2 );
# 유형 4: NOT IN subquery에 사용한 경우(anti-semi join으로 풀 수 있는 연산자이므로 hint가
무시됨)
SELECT *
FROM T1
WHERE I1 NOT IN ( SELECT /*+ HASH_ISJ */ I1
FROM T2 );
[HASH_AJ]
subquery에 대하여 hash anti-semi join으로 처리하도록 optimizer에 지시한다. 이는 subquery에 대하여 동일한
결과를 갖는 anti-semi join 형태로 풀어내도록 한다.
HASH_AJ의 경우 NOT EXISTS, NOT IN, != ALL quantify 연산자에 사용 가능하며, EXISTS, IN, ANY quantify 연
산자, != ALL quantify 연산자를 제외한 ALL quantify 연산자에는 사용이 불가능하다.
HASH_AJ hint는 해당 subquery를 anti-semi join 형태로 풀어내기 때문에 semi join 형태로만 풀릴 수 있는 subq
uery에 기술한 경우 optimizer는 이 hint를 무시한다. 또한, hash semi join에 사용할 key가 key compare가 불가능
하다면 optimizer는 이 hint를 무시한다.
아래는 HASH_AJ hint를 이용하여 subquery를 join 형태로 풀어낸 예이다.
# 유형 1: NOT IN subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 NOT IN ( SELECT /*+ HASH_AJ */ I1
FROM T2 );
# 유형 2: NOT EXISTS subquery에 사용한 경우
SELECT *
FROM T1
WHERE NOT EXISTS ( SELECT /*+ HASH_AJ */ I1
FROM T2
WHERE T1.I1 = T2.I1 );
# 유형 3: quantify 연산자의 subquery에 사용한 경우
SELECT *
FROM T1
WHERE I1 != ALL ( SELECT /*+ HASH_AJ */ I1
FROM T2 );
# 유형 4: IN subquery에 사용한 경우(semi join으로 풀 수 있는 연산자이므로 hint가 무시됨)
SELECT *
FROM T1
WHERE I1 IN ( SELECT /*+ HASH_AJ */ I1
FROM T2 );
[NO_QUERY_TRANSFORMATION]
query에 대하여 어떠한 변형도 하지 않도록 optimizer에 지시한다. 이 hint를 기술하면 heuristic optimizer 및 cost
based optimizer 등에서 최적의 성능을 위해 optimizer가 query를 변형하는 과정들을 모두 수행하지 않는다.
NO_QUERY_TRANSFORMATION hint는 최상위 레벨의 query 및 subquery에 기술할 수 있으며, 기술한 query를
포함한 그 하위의 subquery 모두에 대하여 적용된다.
아래는 NO_QUERY_TRANSFORMATION을 이용하여 query 변형을 못하도록 한 예이다.
# 유형 1: subquery에 NO_QUERY_TRANSFORMATION hint를 사용한 경우 (subquery에 대해서만 query
변형을 하지 않음)
SELECT *
FROM T1
WHERE I1 IN ( SELECT /*+ NO_QUERY_TRANSFORMATION */ I1
FROM T2 );
# 유형 2: 최상위 query에 NO_QUERY_TRANSFORMATION hint를 사용한 경우 (최상위 query 및 subquery
모두 query 변형을 하지 않음)
SELECT /*+ NO_QUERY_TRANSFORMATION */ *
FROM T1
WHERE I1 IN ( SELECT I1
FROM T2 );
# 유형 3: 최상위 query에 NO_QUERY_TRANSFORMATION hint를 사용하고 subquery에 UNNEST hint를
사용한 경우 (subquery에 대해 UNNEST hint를 무시함)
SELECT /*+ NO_QUERY_TRANSFORMATION */ *
FROM T1
WHERE I1 IN ( SELECT /*+ UNNEST */ I1
FROM T2 );
[PUSH_PRED]
Single Table에 대하여 적용이 가능한 Filter들을 push하도록 optimizer에게 지시한다. 이 hint는 해당 Select 구문
의 Filter들에 대하여 적용이 가능한 Single Table에 Filter를 push하여 선행 처리하도록 한다. 해당 힌트는 Select로
시작이 되는 SubQuery들에도 각각 기술할 수 있으며, 각 질의 Block 내에서만 적용이 되고, 하위 SubQuery로 힌트
가 전파되지 않는다.
각 SubQuery들에는 독립적으로 이 힌트를 기술할 수 있으며, 기술하지 않으면 Default 값이 적용된다. Default 값은
PUSH_PRED이다.
다음은 PUSH_PRED hint를 이용하여 subquery를 push하는 예이다.
# 유형 1: T1.i1 = 1을 T1 table에 push 하는 경우
SELECT /*+ PUSH_PRED */ *
FROM T1, T2
WHERE T1.I1 = T2.I1
AND T1.I1 = 1;
[NO_PUSH_PRED]
Single Table에 대하여 적용이 가능한 Filter들을 push하지 못하도록 optimizer에게 지시한다. 이 hint는 해당 Select 구문의
Filter들에 대하여 적용이 가능한 Single Table에 Filter를 push하여 선행 처리되는 것을 막는다. 해당 힌트는 Select로 시작
이되는 SubQuery들에도 각각 기술할 수 있으며, 각 질의 Block 내에서만 적용이 되고, 하위 SubQuery로 힌트가 전파되지
않는다. 각 SubQuery들에는 독립적으로 이 힌트를 기술할 수 있으며, 기술하지 않으면 Default 값이 적용된다. Default 값은
PUSH_PRED이다.
이 hint를 기술한 경우 From절에 Join이 존재한다면 해당 Join 중 해당 필터를 처리할 수 있는 가장 하위 위치의 Join
에서 처리한다. 즉, 해당 Filter가 처리 가능한 Single Table에서의 선행 처리를 막는다. 또한, 이 hint가 기술된 경우 From
절에 view가 존재하면, 해당 view의 하위로 filter가 push되지 않는다. 다음은 NO_PUSH_PRED hint를 이용하여 filter를 push
하는 예이다.
# 유형 1: T1.i1 = 1을 T1과 T2의 Join처리가 된 이후 수행된다.
SELECT /*+ NO_PUSH_PRED */ *
FROM T1, T2
WHERE T1.I1 = T2.I1
AND T1.I1 = 1;
# 유형 2: A.i1 = 1을 view A 처리가 된 이후 수행된다.
SELECT /*+ NO_PUSH_PRED */ *
FROM (SELECT I1 FROM T1) AS A
WHERE A.I1 = 1;
[PUSH_SUBQ]
subquery에 대하여 적용이 가능한 최하위 노드까지 push하도록 optimizer에게 지시한다. 이 hint는 subquery의 상위
레벨의 query가 join으로 구성되어 있을때 해당 join node 구조의 최상위 node부터 최하위 노드까지 탐색하며 해당
subquery가 처리 가능한 node를 찾아 push가 가능한 가장 하위 node에서 subquery를 처리하도록 한다. PUSH_SUBQ
hint는 subquery에만 기술할 수 있으며, 다수의 subquery가 존재하는 경우 각 subquery에 대하여 각가 PUSH_SUBQ
hint를 기술해야 한다. PUSH_SUBQ hint를 기술하지 않은 경우 optimizer에 의하여 cost를 계산하여 가장 좋은 cost를
갖는 node에 subquery를 push한다.
# 유형 1: subquery를 T1 table에 push 하는 경우
SELECT *
FROM T1, T2
WHERE T1.I1 = T2.I1
AND T1.I1 IN ( SELECT /*+ PUSH_SUBQ */ I1 FROM T3 );
# 유형 2: subquery가 최상위 node에서만 처리가 가능한 경우 (subquery를 하위 노드로 push하지
못함)
SELECT *
FROM T1, T2
WHERE T1.I1 = T2.I1
AND T1.I1 IN ( SELECT /*+ PUSH_SUBQ */ I1
FROM T3
WHERE T3.I2 = T1.I2
AND T3.I3 = T2.I3 );
[NO_PUSH_SUBQ]
subquery에 대하여 하위 노드로 push하지 못하도록 optimizer에게 지시한다. 이 hint는 subquery의 상위 레벨의
query가 join으로 구성되어 있을때 해당 join node 구조의 최상위 node에서 subquery를 처리하도록 한다.
NO_PUSH_SUBQ hint는 subquery에만 기술할 수 있으며, 다수의 subquery가 존재하는 경우 각 subquery에 대하
여 각각 NO_PUSH_SUBQ hint를 기술해야 한다.
NO_PUSH_SUBQ hint를 기술하지 않은 경우 optimizer에 의하여 cost를 계산하여 가장 좋은 cost를 갖는 node에
subquery를 push한다.
다음은 NO_PUSH_SUBQ hint를 이용하여 subquery를 push하는 예이다.
# 유형 1: T1 table에서 처리 가능한 subquery에 NO_PUSH_SUBQ hint를 사용한 경우 (T1 table과 T2
table의 join이 이루어진 이후 subquery를 수행함)
SELECT *
FROM T1, T2
WHERE T1.I1 = T2.I1
AND T1.I1 IN ( SELECT /*+ NO_PUSH_SUBQ */ I1
FROM T3 );
[USE_GROUP_HASH]
Group By절이 있는 경우 Grouping을 하기위한 방법으로 Hash Instant를 사용하도록 Optimizer에게 지시한다.
USE_GROUP_HASH hint는 Group By절이 있는 구문에만 기술할 수 있으며, subquery가 존재하는 경우 각각의
subquery마다 독립적으로 기술해야 한다.
USE_GROUP_HASH hint를 사용하지 않는 경우 Grouping을 위한 Group Key들이 하위 노드로부터 정렬된 상태
일 경우 Group Node를 이용하는 방법을 고려한다. 다음은 USE_GROUP_HASH hint를 이용하는 예이다.
# 유형 1: T1 table에서 Group Key들로 구성된 Index가 존재할때 USE_GROUP_HASH를 사용한 경우
SELECT /*+ USE_GROUP_HASH */ *
FROM T1
GROUP BY I1;
[USE_DISTINCT_HASH]
Distinct절이 있는 경우 Distinct를 수행하기 위한 방법으로 Hash Instant를 사용하도록 Optimizer에게 지시한다.
USE_DISTINCT_HASH hint는 Group By절이 있는 구문에만 기술할 수 있으며, subquery가 존재하는 경우 각각의
subquery마다 독립적으로 기술해야 한다.
USE_DISTINCT_HASH hint를 사용하지 않는 경우 Distinct를 위한 Distinct Key들이 하위 노드로부터 정렬된 상태
일 경우 Group Node를 이용하는 방법을 고려한다. 다음은 USE_DISTINCT_HASH hint를 이용하는 예이다.
# 유형 1: T1 table에서 Distinct Key들로 구성된 Index가 존재할때 USE_DISTINCT_HASH를 사용한 경우
SELECT /*+ USE_DISTINCT_HASH */ I1, I2
FROM T1;
<< join >>
join은 <from clause>에 기술한 둘 이상의 table 또는 view에 대하여 각 row들을 결합하는 질의다.
join되는 테이블에서 동일한 이름의 column명이 있을 경우 테이블 명을 함께 명시해야 한다.
join은 조건이 있는 join과 조건이 존재하는 join으로 나뉜다.
[조인조건]
- Equi Join은 조인을 생성하려는 두 개의 테이블의 한쪽 컬럼에서 값이 없다면 테이터를 반환하지 못한다.
- 동일 조건에서 조인 조건을 만족하는 값이 없는 행들을 조회하기 위해 Outer Join을 사용 한다.
- Outer Join 연산자는 "(+)" 이다.
- 조인시 값이 없는 조인측에 "(+)"를 위치 시킨다.
- Outer Join 연산자는 표현식의 한 편에만 올 수 있다
-- Outer Join 으로 부서 번호를 조회하는 예제
SELECT DISTINCT(e.deptno), d.deptno
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;
DEPTNO DEPTNO
------- --------
10 10
20 20
30 30
40
[join 조건이 없는 join]
CROSS JOIN
[join 조건이 있는 join]
INNER JOIN
OUTER JOIN ( LEFT, RIGHT, FULL )
NATURAL JOIN
SEMI JOIN
ANTI-SEMI JOIN
1. cross join
CROSS JOIN은 table 또는 view의 row들에 대하여 각각 다른 table 또는 view의 row들과 결합한 결과를 반환한다.
-- cross join example
SELECT * FROM T1 CROSS JOIN T2;
-- where clause 을 사용할 수도 있다.
SELECT * FROM T1 CROSS JOIN T2 WHERE T1.I1 = T2.I1;
2. inner join
INNER JOIN이란 둘 이상의 table 또는 view에 대하여 조인 조건을 만족하는 row들을 반환하는 조인 연산이다.
INNER JOIN을 명시하는 경우와 <from clause> 에서 table 또는 view 를 ‘,’로 구분하여 기술하는 경우 모두 해당된다.
INNER JOIN시에는 join condition과 함께 쓰이며, <from clause> 는 ‘,‘ 와 함께 쓰인다.
-- <where clause > , ‘,’
SELECT * FROM T1, T2 WHERE T1.I1 = T2.I2;
-- INNER JOIN , <join condition>
SELECT * FROM T1 INNER JOIN T2 ON T1.I1 = I2.I1;
SELECT * FROM T1 INNER JOIN T2 USING( I1 );
3. outer join
INNER JOIN 의 결과에서 추가적으로 OUTER JOIN의 방향에 따라 한쪽 또는 양쪽의 table 또는 view에 대하여 조인 조건을
만족하지 않는 row들을 반환하는 조인 연산이다..
join 조건을 만족하지 않는 row들의 반환은 OUTER키워드 앞에 오는 키워드에 종속적인 row 들을 반환 한다.
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.I1 = T2.I1;
SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.I1 = T2.I1;
SELECT * FROM T1 FULL OUTER JOIN T2 ON T1.I1 = T2.I1;
4. NATURAL JOIN
NATURAL JOIN이란 둘 이상의 table 또는 view에 대하여 동일한 이름의 column 들에 대하여 각각 동등비교(=) 조건을 만들어
이들을 조인 조건으로 하는 조인 연산을 말한다. 동일한 이름의 column 들에 대하여 조인조건을 내부적으로 만들어 사용한다는
것을 제외하고는 INNER JOIN과 동일하다.
SELECT * FROM T1 NATURAL JOIN T2;
SELECT * FROM T1 NATURAL INNER JOIN T2;
5. SEMI JOIN
SEMI JOIN이란 좌측의 row에 대하여 우측의 모든 row들을 대상으로 조인 조건을 만족하는 row가 존재하면 해당 좌측의 row를
결과로 반환하는 조인 연산이다.
SEMI JOIN은 좌측의 row와 우측의 row를 결합한 row를 결과로 반환하는 다른 조인 연산과 달리 좌측의 row만 결과로 반환된다.
SEMI JOIN은 SQL구문 상으로 지정할 수 없으며, 앞서 설명한 subquery의 nested subquery 형태가 optimizer에 의해 SEMI JOIN으로 변환된다.
-- 좌측 table t1의 i1 가 우측 table t2의 i2 와 join condition 으로 조인
SELECT COUNT(*) FROM T1 WHERE I1 IN ( SELECT I1 FROM T2 );
< Execution Plan >
=====================================================================================
| IDX | NODE DESCRIPTION | ROWS |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH AGGREGATION | 1 |
| 2 | SORT MERGE JOIN (LEFT SEMI) : EQUAL | 1 |
| 3 | INDEX ACCESS ("T1, T1_PRIMARY_KEY_INDEX") | 2 |
| 4 | INDEX ACCESS ("T2, T2_PRIMARY_KEY_INDEX") | 2 |
=====================================================================================
1 - AGGREGATIONS : COUNT(*)
2 - JOINED COLUMNS : NOTHING
MERGE FILTER : T1.I1 = T2.I1
3 - READ INDEX COLUMNS : I1
4 - READ INDEX COLUMNS : I1
6. ANTI-SEMI JOIN
ANTI-SEMI JOIN이란 좌측의 row에 대하여 우측의 모든 row들을 대상으로 조인 조건을 만족하는 row가 존재하지
않으면 해당 좌측의 row를 결과로 반환하는 조인 연산이다.
ANTI-SEMI JOIN은 SEMI JOIN과 마찬가지로 좌측의 row만 결과로 반환된다.
ANTI-SEMI JOIN 또한 SEMI JOIN과 마찬가지로 SQL 구문 상으로 지정할 수 없다.
-- 좌측 table t1의 i1 가 우측 table t2의 i2 와 join condition 으로 조인
SELECT COUNT(*) FROM T1 WHERE I1 NOT IN ( SELECT I1 FROM T2 );
< Execution Plan >
=====================================================================================
| IDX | NODE DESCRIPTION | ROWS |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH AGGREGATION | 1 |
| 2 | SORT MERGE JOIN (LEFT ANTI SEMI) : EQUAL | 1 |
| 3 | INDEX ACCESS ("T1, T1_PRIMARY_KEY_INDEX") | 2 |
| 4 | INDEX ACCESS ("T2, T2_PRIMARY_KEY_INDEX") | 2 |
=====================================================================================
1 - AGGREGATIONS : COUNT(*)
2 - JOINED COLUMNS : NOTHING
MERGE FILTER : T1.I1 = T2.I1
3 - READ INDEX COLUMNS : I1
4 - READ INDEX COLUMNS : I1
<< update join >>
- 질의 gSQL> update t1,t2 set t1.c2=9 where t1.c1=t2.c1; 위와 같은 조건 처리 시 오류가 나는데
ERR-42000(40000): syntax error:
update t1,t2 set t1.c2=9 where t1.c1=t2.c1
update에서는 join 처리가 되지 않는 것인가?
update/delete에서 semi join에서의 join은 지원은 하나 inner,outer node에 대한 update 조인은
지원 하지 않습니다. 위의 기능은 지원하지 않는 기능 입니다.
'sunjesoft > Goldilocks SQL' 카테고리의 다른 글
| Goldilocks view & table clear Command (0) | 2021.12.16 |
|---|---|
| R&D SQL 엔지니어 교육 (0) | 2020.05.12 |
| Goldilocks SQL Support (0) | 2020.02.25 |
| Goldilocks SQL TYPE (0) | 2019.10.16 |
'sunjesoft/Goldilocks SQL' Related Articles
more
