메뉴 건너뛰기

bysql.net

2.28_DSJ_(Driving_Semi_Join)

2011.10.11 06:17

ms 조회 수:3804

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 은 이러한 제약이 없다.

 

*** 이 기능은 오라클 사의 정식 힌트가 아니다.!  필자도 데이터 분석 용도로만 사용한다고 한다..

그러므로.. 사용할떄는 주의를 하세요! ***