2.28_DSJ_(Driving_Semi_Join)
2011.10.10 21:17
2.28 DSJ ( Driving Semi Join ) :
Semi Join을 Bitmap 인덱스를 이용하여 Driving 테이블로 바꾸어라.
DSJ 란 ?
Semi Join을 Driving 집합으로 조인순서를 바꾸는 것이다.
이것은 상식에 어긋난다.
Oracle 10g 에서 새로 나온 기능인 hash Join Right Semi를 제외하면 Semi Join 이 Drving 집합이 될 수 없다. 하지만 DSJ 예외에 속한다. DSJ 를 수행하려면 메인 쿼리의 컬럼 중에서 서브쿼리와 조인되는 컬럼에 Bitmap Index 가 생성 되어 있거나 B-Tree 인덱스라면 _b_tree_bitmap_plans 파라미터가 True로 되어있어야 DSJ(Driving Semi Join) 가 가능하다.
먼저 테이블을 단순한 구조로 만들이 위해서 테이블을 2개 생성한다.
CREATE TABLE sales_t AS SELECT * FROM sales;
CREATE TABLE pRODUCTS_T as SELECT * FROM products;
ALTER TABLE products_t ADD CONSTRAINT pk_products_t PRIMARY KEY (prod_id) USING INDEX; --> pk 및 인덱스 생성
CREATE BITMAP INDEX sales_t_prod_bix ON sales_t ( prod_id ); --> BIT MAP 인덱스 생성
exec dbms_stats.gather_table_stats(user,'SALES_T',cascade=>true);
exec dbms_stats.gather_table_stats(user,'PRODUCTS_T',cascade=>true);
SQL 실행 준비 끝.
아래의 상품의 cATEGORY 가 SOFRWARE/OTHER인 제품의 상품코드별, 채널별 판매량을 나타낸 SQL이다. 이러한 SQL 은 주로 DW에서 나타난다.
SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN)*/
s.prod_id,s.channel_id,SUM(quantity_sold) AS qs,SUM (amount_sold ) AS amt
FROM sales_t s
WHERE s.prod_id IN ( SELECT /*+QB_NAME(SUB)*/
p.prod_id
FROM products_t p
WHERE p.prod_category_desc = 'Software/Other')
GROUP BY s.prod_id,s.channel_id;
----------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 82 | 4440 |
| 1 | HASH GROUP BY | | 1 | 82 | 4440 |
|* 2 | HASH JOIN | | 1 | 405K| 4440 |
|* 3 | TABLE ACCESS FULL| PRODUCTS_T | 1 | 26 | 4 |
| 4 | TABLE ACCESS FULL| SALES_T | 1 | 918K| 4436 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROD_ID"="P"."PROD_ID")
3 - filter("P"."PROD_CATEGORY_DESC"='Software/Other')
너무 많은 블럭을 Read 하였다.
Subquery Unnesting이 발생 하여 작은 쪽 테이블인 Products가 Driving 테이블로 선정 되었고 Sales_t 테이블과 Hash Join을 하게 된다. Scan 한 블럭 수는 무료 4444 블럭이다.
이것이 최적으로 튜닝된 SQL인가.? 이것을 알아보기 위해 Hash Join대신 Nested Loop 조인으로
바꿔보자.
SELECT /*+ GATHER_PLAN_STATISTICS*/
prod_id,channel_id,SUM(qs) as qs,SUM(amt) as amt
FROM ( SELECT /*+ NO_MERGE USE_NL(PR S)*/
s.prod_id,s.channel_id,
quantity_sold AS qs,amount_sold AS amt
FROM sales_t s,products_t pr
WHERE s.prod_id = pr.prod_id
AND pr.prod_category_desc ='Software/Other' )
GROUP BY prod_id,channel_id;
원본 SQL 의 경우 웬만해서는 Nested Loop Join으로 바뀌지 않으므로강제로 서브쿼리를 From 절로 끌어 올려서 Nested Loop Join으로 유도하였다.
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 82 | 4416 |
| 1 | HASH GROUP BY | | 1 | 82 | 4416 |
| 2 | VIEW | | 1 | 405K| 4416 |
| 3 | NESTED LOOPS | | 1 | 405K| 4416 |
| 4 | NESTED LOOPS | | 1 | 405K| 43 |
|* 5 | TABLE ACCESS FULL | PRODUCTS_T | 1 | 26 | 4 |
| 6 | BITMAP CONVERSION TO ROWIDS| | 26 | 405K| 39 |
|* 7 | BITMAP INDEX SINGLE VALUE | SALES_T_PROD_BIX | 26 | 31 | 39 |
| 8 | TABLE ACCESS BY INDEX ROWID | SALES_T | 405K| 405K| 4373 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("PR"."PROD_CATEGORY_DESC"='Software/Other')
7 - access("S"."PROD_ID"="PR"."PROD_ID")
HASH JOIN 보다 더 느려졌다.
Nested Loop Join으로 유도하자 인덱스는 제대로 사용하였지만 비효율이 극심하게 드러났다.
먼저 Nested Loop Join 이 40 만번 이상 수행된다 ( Starts 항목 참조 ) 그 결과로 View 를 생성하는 것이 느려졌다.
Buffers 도 5209로 늘어났다. 조인 시도 횟수가 증가하여 시간이 많이 걸리므로 이것은 최악의 결과이다. 그럼 Hash Join에 만족해야 하나.?
지금까지의 상식으로는 그런다 . 하지만 Driving Semi Join 기법을 사용한다면 생각이 달라질 것이다.
Driving Semi Join이란.?
Semi Join의 변형된 형태
=> Hash Join의 장점 ( 조인건수의 최소화) + Nested Loop Join의 장점 ( 선행 테이블이 상수화 되어
후행 테이블에서 인덱스를 효율적으로 사용) + Semi Join 되는 서브쿼리의 집합이 Driving 집합이 되는 것.
SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) semijoin_driver(@sub)*/
s.prod_id,s.channel_id,SUM(quantity_sold) AS qs,SUM (amount_sold ) AS amt
FROM sales_t s
WHERE s.prod_id IN ( SELECT /*+QB_NAME(SUB)*/
p.prod_id
FROM products_t p
WHERE p.prod_category_desc = 'Software/Other')
GROUP BY s.prod_id,s.channel_id;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 82 | 2754 |
| 1 | HASH GROUP BY | | 1 | 82 | 2754 |
| 2 | TABLE ACCESS BY INDEX ROWID | SALES_T | 1 | 405K| 2754 |
| 3 | BITMAP CONVERSION TO ROWIDS| | 1 | 405K| 67 |
| 4 | BITMAP MERGE | | 1 | 4 | 67 |
| 5 | BITMAP KEY ITERATION | | 1 | 31 | 67 |
|* 6 | TABLE ACCESS FULL | PRODUCTS_T | 1 | 26 | 4 |
|* 7 | BITMAP INDEX RANGE SCAN | SALES_T_PROD_BIX | 26 | 31 | 63 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("P"."PROD_CATEGORY_DESC"='Software/Other')
7 - access("S"."PROD_ID"="P"."PROD_ID")
최적의 plan
Driving Semi Join은 조인 횟수도 26번에 불과하며 I/O Block 수도 3051로 Hash Join의 보다 25% 이상 줄어들었다 그 이유는 Join 대신에 Bitmap Key Iteration 이 수행된 까닭이다 Bitmap Key Iteration 의 원리는 Inlist Iterator Operation과 같다고 볼 수 있다. 즉 조인이 아니라 WHERE COLUMN IN( 상수 1,상수2, 상수 3..) 과 같은 Inlist 라는 것이다.
Druvgub Semi Join 은 B-Tree 인덱스에서도 사용이 가능해
이 기능은 Bitmap 인덱스 뿐만 아니라 B-Tree 인덱스에서도 사용가능하다.
하지만 이 경우에는 _b_tree_bitmap_plans 파라미터가 True 로 되어 이써야 한다.
아래의 SQL을 보자
DROP INDEX sales_t_prod_bix; --> Bitmap 인덱스 Drop
CREATE INDEX sales_t_prod_ix ON SALES_T (prod_id); --> B-Tree 인덱스 생성
ALTER SESSION SET "_b_tree_bitmap_plans" = TRUE;
SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) SEMIJOIN_DRIVER(@sub)*/
s.prod_id,s.channel_id,SUM(quantity_sold) AS qs,SUM (amount_sold ) AS amt
FROM sales_t s
WHERE s.prod_id IN ( SELECT /*+QB_NAME(SUB)*/
p.prod_id
FROM products_t p
WHERE p.prod_category_desc = 'Software/Other')
GROUP BY s.prod_id,s.channel_id;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 82 | 2754 |
| 1 | HASH GROUP BY | | 1 | 82 | 2754 |
| 2 | TABLE ACCESS BY INDEX ROWID | SALES_T | 1 | 405K| 2754 |
| 3 | BITMAP CONVERSION TO ROWIDS| | 1 | 405K| 67 |
| 4 | BITMAP MERGE | | 1 | 4 | 67 |
| 5 | BITMAP KEY ITERATION | | 1 | 31 | 67 |
|* 6 | TABLE ACCESS FULL | PRODUCTS_T | 1 | 26 | 4 |
| 7 | BITMAP CONVERSION TO ROWIDS | | 26 | 31 | 63 |
|* 8 | INDEX RANGE SCAN | SALES_T_PROD_BIX | 26 | 31 | 63 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("P"."PROD_CATEGORY_DESC"='Software/Other')
7 - access("S"."PROD_ID"="P"."PROD_ID")
Bitmap 인덱스에 비하여 약간의 비효율이 있다. 그것은 Bitmap Conversion From Rowids Operation 이 추가되었기 때문이다. 하지만 기존의 Hash Join 이나 Nested Loop Join 보다는 수행시간 , I/O Block 수 , 조인 건수 등이 우월함을 알 수있다.
당신은 이미 Driving Semi Join 을 사용했다.
SQL 이 Star Transformation 을 수행하는 경우는 싫든 좋든 Driving Semi Join 기능을 이용한 것이다.
DSJ 기능은 두가지로 요약 가능 하다.
1.Join 의 형태를 Inlist 로 변경시킨다.
2.Star Transformation 기능을 사용 할떄 내부적으로 사용한다.
아래의 Star Transformatoin 기능을 사용할 때 내부적으로 사용한다.
ALTER SESSION SET "star_transformation_enabled" = TRUE;
SELECT /*+gather_plan_statistics*/
p.prod_id,c.channel_id,SUM(quantity_sold) AS qa,SUM(amount_sold ) AS amt
FROM sales s,channels c,products p
WHERE s.channel_id = c.channel_id
AND c.channel_desc = 'Internet'
AND s.prod_id = p.prod_id
AND p.prod_category = 'Photo'
GROUP BY p.prod_id,c.channel_id;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 656 |
| 1 | HASH GROUP BY | | 1 | 10 | 656 |
|* 2 | HASH JOIN | | 1 | 13223 | 656 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 10 | 10 |
|* 4 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 7 |
| 5 | BUFFER SORT | | 1 | 10 | 3 |
| 6 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 1 | 10 | 3 |
|* 7 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 1 | 10 | 1 |
| 8 | PARTITION RANGE ALL | | 1 | 13223 | 646 |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 28 | 13223 | 646 |
| 10 | BITMAP CONVERSION TO ROWIDS | | 28 | 13223 | 222 |
| 11 | BITMAP AND | | 28 | 16 | 222 |
| 12 | BITMAP MERGE | | 28 | 16 | 50 |
| 13 | BITMAP KEY ITERATION | | 28 | 20 | 50 |
| 14 | BUFFER SORT | | 28 | 28 | 7 |
|* 15 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 7 |
|* 16 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | 28 | 20 | 43 |
| 17 | BITMAP MERGE | | 28 | 16 | 172 |
| 18 | BITMAP KEY ITERATION | | 28 | 132 | 172 |
| 19 | BUFFER SORT | | 28 | 160 | 5 |
|* 20 | VIEW | index$_join$_078 | 5 | 10 | 5 |
|* 21 | HASH JOIN | | 5 | 10 | 5 |
|* 22 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 5 | 10 | 1 |
| 23 | INDEX FAST FULL SCAN | PRODUCTS_PK | 1 | 72 | 4 |
|* 24 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | 160 | 132 | 167 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CHANNEL_ID"="C"."CHANNEL_ID" AND "S"."PROD_ID"="P"."PROD_ID")
4 - filter("C"."CHANNEL_DESC"='Internet')
7 - access("P"."PROD_CATEGORY"='Photo')
15 - filter("C"."CHANNEL_DESC"='Internet')
16 - access("S"."CHANNEL_ID"="C"."CHANNEL_ID")
20 - filter("P"."PROD_CATEGORY"='Photo')
21 - access(ROWID=ROWID)
22 - access("P"."PROD_CATEGORY"='Photo')
24 - access("S"."PROD_ID"="P"."PROD_ID")
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('star_transformation_enabled' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$D750A531")
OUTLINE_LEAF(@"SEL$374C338B")
OUTLINE_LEAF(@"SEL$1898F719")
OUTLINE_LEAF(@"SEL$5208623C")
STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("C"@"SEL$1") ("P"@"SEL$1")))
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$1898F719")
FULL(@"SEL$5208623C" "C"@"SEL$1")
INDEX_RS_ASC(@"SEL$5208623C" "P"@"SEL$1" ("PRODUCTS"."PROD_CATEGORY"))
BITMAP_TREE(@"SEL$5208623C" "S"@"SEL$1" AND(("SALES"."CHANNEL_ID") ("SALES"."PROD_ID")))
LEADING(@"SEL$5208623C" "C"@"SEL$1" "P"@"SEL$1" "S"@"SEL$1")
USE_MERGE_CARTESIAN(@"SEL$5208623C" "P"@"SEL$1")
USE_HASH(@"SEL$5208623C" "S"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$5208623C")
INDEX_JOIN(@"SEL$1898F719" "P"@"SEL$1898F719" ("PRODUCTS"."PROD_CATEGORY") ("PRODUCTS"."PROD_ID"))
SEMIJOIN_DRIVER(@"SEL$1898F719")
FULL(@"SEL$D750A531" "C"@"SEL$D750A531")
SEMIJOIN_DRIVER(@"SEL$D750A531")
END_OUTLINE_DATA
*/
위의 Outline Data를 보면 Star Transformation이 발생 할 때 DSJ 기능을 사용하였음을 알 수 있다.
Star Transformation 은 3.17 / 3.18 에 자세히 소개 되었다.
Driving Semi Join 의 장점
Driving Semi Join은 Nested Loop Join 시의 조인 횟수에 의한 부하나 Hash 조인 시의 Full Table Scan + Hashing 작업의 부하를 획기적으로 감소시킨다.
더 좋은 점은 Star Transformation 의 경우는 바인드 변수를 사용할 수 없지만,
Dring Semi join 은 이러한 제약이 없다.
*** 이 기능은 오라클 사의 정식 힌트가 아니다.! 필자도 데이터 분석 용도로만 사용한다고 한다..
그러므로.. 사용할떄는 주의를 하세요! ***
