1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 상반기 - 오라클 트러블슈팅 스터디
  4. 3.7 CSU (Complex Subquery Unnesting) 복잡한 서브쿼리를 조인으로 바꾸어라

3.7 CSU (Complex Subquery Unnesting) 복잡한 서브쿼리를 조인으로 바꾸어라 (by openlsw) [2013.06.27]

3.7 CSU (Complex Subquery Unnesting) : 복잡한 서브쿼리를 조인으로 바꾸어라

CSU

  • SSU(Simple Subquery Unnesting) / CSU(Complex Subquery Unnesting)
  • Simple Subquery : 서브쿼리 내부의 From 절에 테이블이 하나만 존재
  • Complex Subquery : 서브쿼리 내부의 From 절에 테이블이 복수로 존재 하거나 Group By + 집계함수 등의 복잡한 Operation 존재
  • _unnesting_subquery := True , UNNEST/NO_UNNEST
CSU 예제
{code:sqlborderStyle=solid}
SELECT /*+ QB_NAME(MAIN) / count()
FROM sales s
WHERE quantity_sold IN (SELECT /*+ QB_NAME(SUB) */ MAX (c.quantity_sold)
FROM sales c
WHERE c.cust_id = s.cust_id)
AND s.cust_id between 100 and 1000;

--

IdOperationNameStartsA-RowsA-Time

--

0SELECT STATEMENT1100:00:00.01
1SORT AGGREGATE1100:00:00.01
2NESTED LOOPS1000:00:00.01
3PARTITION RANGE ALL1000:00:00.01
4TABLE ACCESS BY LOCAL INDEX ROWIDSALES28000:00:00.01
5BITMAP CONVERSION TO ROWIDS0000:00:00.01
  • 6
BITMAP INDEX RANGE SCANSALES_CUST_BIX0000:00:00.01
  • 7
VIEW PUSHED PREDICATEVW_SQ_10000:00:00.01
  • 8
FILTER0000:00:00.01
9SORT AGGREGATE0000:00:00.01
  • 10
FILTER0000:00:00.01
11PARTITION RANGE ALL0000:00:00.01
12TABLE ACCESS BY LOCAL INDEX ROWIDSALES0000:00:00.01
13BITMAP CONVERSION TO ROWIDS0000:00:00.01
  • 14
BITMAP INDEX SINGLE VALUESALES_CUST_BIX0000:00:00.01

--

Query Block Name / Object Alias (identified by operation id):

-

1 - SEL$43BFB197
4 - SEL$43BFB197 / S@MAIN
7 - SEL$A624FCD2 / VW_SQ_1@SEL$EDD5E67B
8 - SEL$A624FCD2
12 - SEL$A624FCD2 / C@SUB

Outline Data

-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$A624FCD2")
PUSH_PRED(@"SEL$43BFB197" "VW_SQ_1"@"SEL$EDD5E67B" 2)
OUTLINE_LEAF(@"SEL$43BFB197")
UNNEST(@"SUB")
OUTLINE(@"SEL$75CB63B7")
OUTLINE(@"SEL$43BFB197")
UNNEST(@"SUB")
OUTLINE(@"SEL$EDD5E67B")
OUTLINE(@"SUB")
OUTLINE(@"MAIN")
BITMAP_TREE(@"SEL$43BFB197" "S"@"MAIN" AND(("SALES"."CUST_ID")))
NO_ACCESS(@"SEL$43BFB197" "VW_SQ_1"@"SEL$EDD5E67B")
LEADING(@"SEL$43BFB197" "S"@"MAIN" "VW_SQ_1"@"SEL$EDD5E67B")
USE_NL(@"SEL$43BFB197" "VW_SQ_1"@"SEL$EDD5E67B")
BITMAP_TREE(@"SEL$A624FCD2" "C"@"SUB" AND(("SALES"."CUST_ID")))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):

---

6 - access("S"."CUST_ID">=100 AND "S"."CUST_ID"<=1000)
7 - filter("QUANTITY_SOLD"="MAX(C.QUANTITY_SOLD)")
8 - filter((COUNT(*)>0 AND 100<=1000))
10 - filter((1000>="S"."CUST_ID" AND 100<="S"."CUST_ID"))
14 - access("C"."CUST_ID"="S"."CUST_ID")
filter(("C"."CUST_ID">=100 AND "C"."CUST_ID"<=1000)) <<<<< TP(Transitive Predicate) 발생

|
||CSU 예제 - QT가 바꾼 쿼리||
|{code:sql|borderStyle=solid}
SELECT /*+ QB_NAME(MAIN) */ COUNT (*)
  FROM sales s,
       (SELECT /*+ QB_NAME(SUB) */
               c.cust_id AS item_1,
               MAX (c.quantity_sold) AS "MAX(QUANTITY_SOLD)"
          FROM sales c
         WHERE c.cust_id BETWEEN 100 AND 1000
         GROUP BY c.cust_id) vw_sq_1
 WHERE s.cust_id = vw_sq_1.item_1
   AND s.quantity_sold = vw_sq_1."MAX(QUANTITY_SOLD)"
   AND s.cust_id BETWEEN 100 AND 1000;

--------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                |      1 |      1 |00:00:00.01 |
|   1 |  SORT AGGREGATE                          |                |      1 |      1 |00:00:00.01 |
|   2 |   NESTED LOOPS                           |                |      1 |      0 |00:00:00.01 |
|   3 |    PARTITION RANGE ALL                   |                |      1 |      0 |00:00:00.01 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID    | SALES          |     28 |      0 |00:00:00.01 |
|   5 |      BITMAP CONVERSION TO ROWIDS         |                |      0 |      0 |00:00:00.01 |
|*  6 |       BITMAP INDEX RANGE SCAN            | SALES_CUST_BIX |      0 |      0 |00:00:00.01 |
|*  7 |    VIEW PUSHED PREDICATE                 |                |      0 |      0 |00:00:00.01 |
|*  8 |     FILTER                               |                |      0 |      0 |00:00:00.01 |
|   9 |      SORT AGGREGATE                      |                |      0 |      0 |00:00:00.01 |
|* 10 |       FILTER                             |                |      0 |      0 |00:00:00.01 |
|  11 |        PARTITION RANGE ALL               |                |      0 |      0 |00:00:00.01 |
|  12 |         TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |      0 |      0 |00:00:00.01 |
|  13 |          BITMAP CONVERSION TO ROWIDS     |                |      0 |      0 |00:00:00.01 |
|* 14 |           BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |      0 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MAIN
   4 - MAIN         / S@MAIN
   7 - SEL$22B9BCDF / VW_SQ_1@MAIN
   8 - SEL$22B9BCDF
  12 - SEL$22B9BCDF / C@SUB

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$22B9BCDF")
      PUSH_PRED(@"MAIN" "VW_SQ_1"@"MAIN" 1)
      OUTLINE_LEAF(@"MAIN")
      OUTLINE(@"SUB")
      OUTLINE(@"MAIN")
      BITMAP_TREE(@"MAIN" "S"@"MAIN" AND(("SALES"."CUST_ID")))
      NO_ACCESS(@"MAIN" "VW_SQ_1"@"MAIN")
      LEADING(@"MAIN" "S"@"MAIN" "VW_SQ_1"@"MAIN")
      USE_NL(@"MAIN" "VW_SQ_1"@"MAIN")
      BITMAP_TREE(@"SEL$22B9BCDF" "C"@"SUB" AND(("SALES"."CUST_ID")))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("S"."CUST_ID">=100 AND "S"."CUST_ID"<=1000)
   7 - filter("S"."QUANTITY_SOLD"="VW_SQ_1"."MAX(QUANTITY_SOLD)")
   8 - filter((COUNT(*)>0 AND 100<=1000))
  10 - filter((1000>="S"."CUST_ID" AND 100<="S"."CUST_ID"))
  14 - access("C"."CUST_ID"="S"."CUST_ID")
       filter(("C"."CUST_ID">=100 AND "C"."CUST_ID"<=1000))

-- QT가 바꾼 쿼리의 실행 계획이 원본 쿼리의 실행 계획과 완전히 같다 (책은 HASH JOIN, 예제는 NL JOIN)

|

CSU 예제 - 10053 Trace
{code:sql}
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block MAIN (#1) that are valid to unnest.
Subquery Unnesting on query block MAIN (#1)SU: Performing unnesting that does not require costing. <<<<< 메인 쿼리 체크 결과 Costing 불필요 판단
SU: Considering subquery unnest on query block MAIN (#1).
SU: Checking validity of unnesting subquery SUB (#2)
SU: Passed validity checks, but requires costing.
SU: Using search type: exhaustive <<<<< 서브 쿼리가 Complex 서브 쿼리 인 경우 Costing 반드시 필요, 모든 경우의 수를 Costing
SU: Starting iteration 1, state space = (2) : (1) <<<<< CSU 적용 케이스
SU: Unnesting subquery query block SUB (#2)Subquery elimination for query block SUB (#2)
... 중략 ...
try to generate transitive predicate from check constraints for query block SEL$75CB63B7 (#2)
finally: "C"."CUST_ID">=100 AND 100<=1000 AND "C"."CUST_ID"<=1000 <<<<< TP 적용

SU: Costing transformed query.
CBQT: Looking for cost annotations for query block SEL$75CB63B7, key = SEL$75CB63B7_00002202_2
CBQT: Could not find stored cost annotations.
... 중략 ...
Registered qb: SEL$75CB63B7 0xe27ad34 (SUBQ INTO VIEW FOR COMPLEX UNNEST SUB) <<<<< 서브쿼리가 인라인뷰로 변환

-
QUERY BLOCK SIGNATURE
-
signature (): qb_name=SEL$75CB63B7 nbfros=1 flg=0
fro(0): flg=0 objn=74290 hint_alias="C"@"SUB"

Registered qb: SEL$EDD5E67B 0xe2795f4 (VIEW ADDED MAIN) <<<<< 위에서 만든 인라인뷰 를 메인 쿼리에 추가

-
QUERY BLOCK SIGNATURE
-
signature (): qb_name=SEL$EDD5E67B nbfros=2 flg=0
fro(0): flg=0 objn=74290 hint_alias="S"@"MAIN"
fro(1): flg=5 objn=0 hint_alias="VW_SQ_1"@"SEL$EDD5E67B"

Registered qb: SEL$43BFB197 0xe2795f4 (SUBQUERY UNNEST SEL$EDD5E67B; SUB)

-
QUERY BLOCK SIGNATURE
-
signature (): qb_name=SEL$43BFB197 nbfros=2 flg=0
fro(0): flg=0 objn=74290 hint_alias="S"@"MAIN"
fro(1): flg=1 objn=0 hint_alias="VW_SQ_1"@"SEL$EDD5E67B"
... 중략 ...
SU: Updated best state, Cost = 2891.06 <<<<< Iteration 1 비용
SU: Starting iteration 2, state space = (2) : (0)
FPD: Considering simple filter push in query block MAIN (#1)
"S"."QUANTITY_SOLD"= (SELECT /*+ QB_NAME ("SUB") */ MAX("C"."QUANTITY_SOLD") FROM "SALES" "C") AND "S"."CUST_ID">=100 AND "S"."CUST_ID"<=1000
FPD: Considering simple filter push in query block SUB (#2)
"C"."CUST_ID"=:B1
try to generate transitive predicate from check constraints for query block SUB (#2)
finally: "C"."CUST_ID"=:B1

try to generate transitive predicate from check constraints for query block MAIN (#1)
finally: "S"."QUANTITY_SOLD"= (SELECT /*+ QB_NAME ("SUB") */ MAX("C"."QUANTITY_SOLD") FROM "SALES" "C") AND "S"."CUST_ID">=100 AND "S"."CUST_ID"<=1000 AND 100<=1000

FPD: transitive predicates are generated in query block MAIN (#1)
100<=1000
SU: Costing transformed query.
CBQT: Looking for cost annotations for query block SUB, key = SUB_00000400_0
CBQT: Could not find stored cost annotations.
kkoqbc: optimizing query block SUB (#2)

:
call(in-use=212664, alloc=263496), compile(in-use=303444, alloc=317856), execution(in-use=2356, alloc=4060)

kkoqbc-subheap (create addr=0x0E50DE6C)
... 중략 ...
*********************************
Number of join permutations tried: 1
*********************************
Final adjusted join cardinality: 7059, sq. fil. factor: 11.165321
Trying or-Expansion on query block MAIN (#1)
Transfer Optimizer annotations for query block MAIN (#1)
Final cost for query block MAIN (#1) - All Rows Plan:
Best join order: 1
Cost: 2328556.5906 Degree: 1 Card: 7059.0000 Bytes: 1812745
Resc: 2328556.5906 Resc_io: 2327697.0390 Resc_cpu: 19039852537
Resp: 2328556.5906 Resp_io: 2327697.0390 Resc_cpu: 19039852537
kkoqbc-subheap (delete addr=0x0E509FBC, in-use=13752, alloc=16464)
kkoqbc-end:
:
call(in-use=253620, alloc=279872), compile(in-use=304692, alloc=317856), execution(in-use=2356, alloc=4060)

kkoqbc: finish optimizing query block MAIN (#1)
CBQT: Saved costed qb# 2 (SUB), key = SUB_00000400_0
CBQT: Saved costed qb# 1 (MAIN), key = MAIN_00000000_0
SU: Updated best state, Cost = 2328556.59 <<<<< CSU 적용 쿼리의 Costing(2891.06)과 큰 차이 발생, 옵티마이저는 CSU 선택
SU: Will unnest subquery SUB (#2)
SU: Reconstructing original query from best state.
SU: Considering subquery unnest on query block MAIN (#1).
SU: Checking validity of unnesting subquery SUB (#2)
SU: Passed validity checks.
SU: Unnesting subquery query block SUB (#2)Subquery elimination for query block SUB (#2)
Subquery unchanged.

|

||UNNEST/NO_UNNEST 로 Control 예제 - UNNEST||
|{code:sql|borderStyle=solid}
SELECT /*+ GATHER_PLAN_STATISTICS UNNEST(@SUB) */ count(*)
  FROM sales s
 WHERE quantity_sold IN (SELECT /*+ QB_NAME(SUB) */  MAX (c.quantity_sold)
                           FROM sales c
                          WHERE c.cust_id = s.cust_id)
  AND s.cust_id between 100 and 1000 ;

--------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                |      1 |      1 |00:00:00.01 |
|   1 |  SORT AGGREGATE                          |                |      1 |      1 |00:00:00.01 |
|   2 |   NESTED LOOPS                           |                |      1 |      0 |00:00:00.01 |
|   3 |    PARTITION RANGE ALL                   |                |      1 |      0 |00:00:00.01 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID    | SALES          |     28 |      0 |00:00:00.01 |
|   5 |      BITMAP CONVERSION TO ROWIDS         |                |      0 |      0 |00:00:00.01 |
|*  6 |       BITMAP INDEX RANGE SCAN            | SALES_CUST_BIX |      0 |      0 |00:00:00.01 |
|*  7 |    VIEW PUSHED PREDICATE                 | VW_SQ_1        |      0 |      0 |00:00:00.01 |
|*  8 |     FILTER                               |                |      0 |      0 |00:00:00.01 |
|   9 |      SORT AGGREGATE                      |                |      0 |      0 |00:00:00.01 |
|* 10 |       FILTER                             |                |      0 |      0 |00:00:00.01 |
|  11 |        PARTITION RANGE ALL               |                |      0 |      0 |00:00:00.01 |
|  12 |         TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |      0 |      0 |00:00:00.01 |
|  13 |          BITMAP CONVERSION TO ROWIDS     |                |      0 |      0 |00:00:00.01 |
|* 14 |           BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |      0 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$0200D8B2
   4 - SEL$0200D8B2 / S@SEL$1
   7 - SEL$A624FCD2 / VW_SQ_1@SEL$63284A59
   8 - SEL$A624FCD2
  12 - SEL$A624FCD2 / C@SUB

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$A624FCD2")
      PUSH_PRED(@"SEL$0200D8B2" "VW_SQ_1"@"SEL$63284A59" 2)
      OUTLINE_LEAF(@"SEL$0200D8B2")
      UNNEST(@"SUB")
      OUTLINE(@"SEL$75CB63B7")
      OUTLINE(@"SEL$0200D8B2")
      UNNEST(@"SUB")
      OUTLINE(@"SEL$63284A59")
      OUTLINE(@"SUB")
      OUTLINE(@"SEL$1")
      BITMAP_TREE(@"SEL$0200D8B2" "S"@"SEL$1" AND(("SALES"."CUST_ID")))
      NO_ACCESS(@"SEL$0200D8B2" "VW_SQ_1"@"SEL$63284A59")
      LEADING(@"SEL$0200D8B2" "S"@"SEL$1" "VW_SQ_1"@"SEL$63284A59")
      USE_NL(@"SEL$0200D8B2" "VW_SQ_1"@"SEL$63284A59")
      BITMAP_TREE(@"SEL$A624FCD2" "C"@"SUB" AND(("SALES"."CUST_ID")))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("S"."CUST_ID">=100 AND "S"."CUST_ID"<=1000)
   7 - filter("QUANTITY_SOLD"="MAX(C.QUANTITY_SOLD)")
   8 - filter((COUNT(*)>0 AND 100<=1000))
  10 - filter((1000>="S"."CUST_ID" AND 100<="S"."CUST_ID"))
  14 - access("C"."CUST_ID"="S"."CUST_ID")
       filter(("C"."CUST_ID">=100 AND "C"."CUST_ID"<=1000))

|

UNNEST/NO_UNNEST 로 Control 예제 - NO_UNNEST
{code:sqlborderStyle=solid}
SELECT /*+ GATHER_PLAN_STATISTICS NO_UNNEST(@SUB) / count()
FROM sales s
WHERE quantity_sold IN (SELECT /*+ QB_NAME(SUB) */ MAX (c.quantity_sold)
FROM sales c
WHERE c.cust_id = s.cust_id)
AND s.cust_id between 100 and 1000 ;

---

IdOperationNameStartsA-RowsA-Time

---

0SELECT STATEMENT1100:00:00.01
1SORT AGGREGATE1100:00:00.01
  • 2
FILTER1000:00:00.01
3PARTITION RANGE ALL1000:00:00.01
4TABLE ACCESS BY LOCAL INDEX ROWIDSALES28000:00:00.01
5BITMAP CONVERSION TO ROWIDS0000:00:00.01
  • 6
BITMAP INDEX RANGE SCANSALES_CUST_BIX0000:00:00.01
7SORT AGGREGATE0000:00:00.01
8PARTITION RANGE ALL0000:00:00.01
9TABLE ACCESS BY LOCAL INDEX ROWIDSALES0000:00:00.01
10BITMAP CONVERSION TO ROWIDS0000:00:00.01
  • 11
BITMAP INDEX SINGLE VALUESALES_CUST_BIX0000:00:00.01

---

Query Block Name / Object Alias (identified by operation id):

-

1 - SEL$1
4 - SEL$1 / S@SEL$1
7 - SUB
9 - SUB / C@SUB

Outline Data

-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SUB")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SUB")
BITMAP_TREE(@"SEL$1" "S"@"SEL$1" AND(("SALES"."CUST_ID")))
BITMAP_TREE(@"SUB" "C"@"SUB" AND(("SALES"."CUST_ID")))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):

---

2 - filter("QUANTITY_SOLD"=)
6 - access("S"."CUST_ID">=100 AND "S"."CUST_ID"<=1000)
11 - access("C"."CUST_ID"=:B1)

|
 * CSU 는 HASH AREA 를 추가로 사용 하지만, Buffers, Reads 항목을 비교해 보면 Filter 서브쿼리는 CSU가 적용된 경우를 따라오지 못함
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 상반기 - 오라클 트러블슈팅 스터디
  4. 3.7 CSU (Complex Subquery Unnesting) 복잡한 서브쿼리를 조인으로 바꾸어라