꿈꾸는 개발자, DBA 커뮤니티 구루비

3.3 OR-Expansion (OR To Union All Conversion) : OR 조건을 이용하여 Union All로 변경시켜라

1. WHERE 절에서 OR/IN 조건을 만나면 UNION ALL 로 변형
2. 같은 컬럼의 OR/IN 은 대부분 INLIST ITERATER 로 처리됨, 다른 컬럼은 UNION ALL 로만 변환 가능
3. _no_or_expansion = False (True 설정 시 OR-Expansion 비 활성화)
4. _optimizer_cost_based_transformation 과 관계 없이 동작 : 불완전 CBQT (Search Type, Iteration 나타나지 않음)

OR 로 분기되는 컬럼이 서로 다름 (d.department_id, e.manager_id)
{code:sql	borderStyle=solid}SELECT /+ QB_NAME(MAIN) USE_CONCAT / e.employee_id, e.first_name, e.last_name, e.email, d.department_name FROM employee e, department d WHERE e.department_id = d.department_id AND ( d.department_id IN (10, 20) OR e.manager_id IN (101, 102) );{code}
{code:sql	borderStyle=solid} -	Id	Operation	Name	Starts	A-Rows	A-Time	Buffers	OMem	1Mem	Used-Mem

-

0	SELECT STATEMENT		1	8	00:00:00.01	13
1	CONCATENATION		1	8	00:00:00.01	13
2	MERGE JOIN		1	6	00:00:00.01	7
3	TABLE ACCESS BY INDEX ROWID	DEPARTMENT	1	12	00:00:00.01	4
4	INDEX FULL SCAN	DEPT_ID_PK	1	12	00:00:00.01	2
5	SORT JOIN		12	6	00:00:00.01	3	2048	2048	2048 (0)
6	INLIST ITERATOR		1	6	00:00:00.01	3
7	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	2	6	00:00:00.01	3
8	INDEX RANGE SCAN	EMP_MANAGER_IX	2	6	00:00:00.01	2				<<<<< e.manager_id	9	NESTED LOOPS		1	2	00:00:00.01	6
10	NESTED LOOPS		1	3	00:00:00.01	5
11	INLIST ITERATOR		1	2	00:00:00.01	3
12	TABLE ACCESS BY INDEX ROWID	DEPARTMENT	2	2	00:00:00.01	3
13	INDEX UNIQUE SCAN	DEPT_ID_PK	2	2	00:00:00.01	2				<<<<< d.department_id	14	INDEX RANGE SCAN	EMP_DEPARTMENT_IX	2	3	00:00:00.01	2
15	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	3	2	00:00:00.01	1

-

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

-

1 - MAIN
3 - MAIN_1 / D@MAIN
4 - MAIN_1 / D@MAIN
7 - MAIN_1 / E@MAIN
8 - MAIN_1 / E@MAIN
12 - MAIN_2 / D@MAIN_2
13 - MAIN_2 / D@MAIN_2
14 - MAIN_2 / E@MAIN_2
15 - MAIN_2 / E@MAIN_2

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(@"MAIN")
OUTLINE_LEAF(@"MAIN_1")
USE_CONCAT(@"MAIN" 8 OR_PREDICATES(2)) <<<<< 쿼리 블럭명이 UNION ALL 기준으로 MAIN_1 과 MAIN_2 로 나뉜 원인
OUTLINE_LEAF(@"MAIN_2")
OUTLINE(@"MAIN")
INDEX(@"MAIN_1" "D"@"MAIN" ("DEPARTMENT"."DEPARTMENT_ID"))
INDEX_RS_ASC(@"MAIN_1" "E"@"MAIN" ("EMPLOYEE"."MANAGER_ID"))
INDEX_RS_ASC(@"MAIN_2" "D"@"MAIN_2" ("DEPARTMENT"."DEPARTMENT_ID"))
INDEX(@"MAIN_2" "E"@"MAIN_2" ("EMPLOYEE"."DEPARTMENT_ID"))
LEADING(@"MAIN_1" "D"@"MAIN" "E"@"MAIN")
LEADING(@"MAIN_2" "D"@"MAIN_2" "E"@"MAIN_2")
USE_MERGE(@"MAIN_1" "E"@"MAIN")
USE_NL(@"MAIN_2" "E"@"MAIN_2")
NLJ_BATCHING(@"MAIN_2" "E"@"MAIN_2")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):

---

5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access(("E"."MANAGER_ID"=101 OR "E"."MANAGER_ID"=102))
13 - access(("D"."DEPARTMENT_ID"=10 OR "D"."DEPARTMENT_ID"=20))
14 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
15 - filter((LNNVL("E"."MANAGER_ID"=101) AND LNNVL("E"."MANAGER_ID"=102))) <<<<< CONCATENATION 으로 분리된 조건 끼리 중복되는 데이터 제거 (MANAGER_ID 가 NULL 이거나 101, 102 가 아닐 경우 참)



{info:title=LNNVL}
* WHERE 절 혹은 CASE 문 에서만 쓰임
* 인자가 TRUE 이면 FALSE, FALSE/UNKNOWN 이면 TRUE 반환

||예제||Return||
|{{LNNVL(1=1)}}|{{FALSE}}|
|{{LNNVL(1=2)}}|{{TRUE}}|
|{{LNNVL(1=Null)}}|{{TRUE}}|
{info}

{info:title=USE_CONCAT 인자}
||두번째 인자||의미||예제||비고||
|8|분기 조건 단위로 분리|OR 기준으로 두개 SQL 로 분리|기본값|
|1|가능한 한 모두 분리|IN 절 내 인자 및 OR 기준으로 네개 SQL 로 분리|모두 분리 됨에 따라 파티션 테이블의 경우 최적화 가능|

* 세번째 인자 : OR_PREDICATES(2) : SQL 을 두개로 분리
{info}

||USE_CONCAT 두번째 인자를 1 적용시 쿼리 블럭 정보||
|{code:sql|borderStyle=solid}Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MAIN
   4 - MAIN_1 / E@MAIN
   5 - MAIN_1 / E@MAIN
   6 - MAIN_1 / D@MAIN
   7 - MAIN_1 / D@MAIN
   9 - MAIN_2 / D@MAIN_2
  10 - MAIN_2 / D@MAIN_2
  12 - MAIN_2 / E@MAIN_2
  13 - MAIN_2 / E@MAIN_2
  15 - MAIN_3 / D@MAIN_3
  16 - MAIN_3 / D@MAIN_3
  17 - MAIN_3 / E@MAIN_3
  18 - MAIN_3 / E@MAIN_3
  20 - MAIN_4 / D@MAIN_4
  21 - MAIN_4 / D@MAIN_4
  22 - MAIN_4 / E@MAIN_4
  23 - MAIN_4 / E@MAIN_4

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(@"MAIN")
      OUTLINE_LEAF(@"MAIN_1")
      USE_CONCAT(@"MAIN" OR_PREDICATES(2))
      OUTLINE_LEAF(@"MAIN_2")
      OUTLINE_LEAF(@"MAIN_3")
      OUTLINE_LEAF(@"MAIN_4")
      OUTLINE(@"MAIN")
      INDEX_RS_ASC(@"MAIN_1" "E"@"MAIN" ("EMPLOYEE"."MANAGER_ID"))
      INDEX(@"MAIN_1" "D"@"MAIN" ("DEPARTMENT"."DEPARTMENT_ID"))
      INDEX(@"MAIN_2" "D"@"MAIN_2" ("DEPARTMENT"."DEPARTMENT_ID"))
      INDEX_RS_ASC(@"MAIN_2" "E"@"MAIN_2" ("EMPLOYEE"."MANAGER_ID"))
      INDEX_RS_ASC(@"MAIN_3" "D"@"MAIN_3" ("DEPARTMENT"."DEPARTMENT_ID"))
      INDEX_RS_ASC(@"MAIN_3" "E"@"MAIN_3" ("EMPLOYEE"."DEPARTMENT_ID"))
      INDEX_RS_ASC(@"MAIN_4" "D"@"MAIN_4" ("DEPARTMENT"."DEPARTMENT_ID"))
      INDEX_RS_ASC(@"MAIN_4" "E"@"MAIN_4" ("EMPLOYEE"."DEPARTMENT_ID"))
      LEADING(@"MAIN_1" "E"@"MAIN" "D"@"MAIN")
      LEADING(@"MAIN_2" "D"@"MAIN_2" "E"@"MAIN_2")
      LEADING(@"MAIN_3" "D"@"MAIN_3" "E"@"MAIN_3")
      LEADING(@"MAIN_4" "D"@"MAIN_4" "E"@"MAIN_4")
      USE_NL(@"MAIN_1" "D"@"MAIN")
      NLJ_BATCHING(@"MAIN_1" "D"@"MAIN")
      USE_MERGE(@"MAIN_2" "E"@"MAIN_2")
      USE_NL(@"MAIN_3" "E"@"MAIN_3")
      USE_NL(@"MAIN_4" "E"@"MAIN_4")
      END_OUTLINE_DATA
  */

QT가 변환 한 쿼리

{code:sql

borderStyle=solid}
-- 위의 쿼리는 아래와 같이 재작성 됨, 데이터 중복 방지를 위한 LNNVL 조건이 추가됨
SELECT e.employee_id, e.first_name, e.last_name, e.email, d.department_name
FROM employee e, department d
WHERE e.department_id = d.department_id
AND e.manager_id IN (101, 102)
UNION ALL
SELECT e.employee_id, e.first_name, e.last_name, e.email, d.department_name
FROM employee e, department d
WHERE e.department_id = d.department_id
AND d.department_id IN (10, 20)
AND (lnnvl (e.manager_id = 101) AND lnnvl (e.manager_id = 102));

Plan hash value: 781920826

-

Id

Operation

Name

Starts

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

-

0	SELECT STATEMENT		1	8	00:00:00.01	13
1	UNION-ALL		1	8	00:00:00.01	13				<<<<< CONCATENATION > UNION-ALL 만 바뀜	2	MERGE JOIN	1	6	00:00:00.01	7
3	TABLE ACCESS BY INDEX ROWID	DEPARTMENT	1	12	00:00:00.01	4
4	INDEX FULL SCAN	DEPT_ID_PK	1	12	00:00:00.01	2
5	SORT JOIN		12	6	00:00:00.01	3	2048	2048	2048 (0)
6	INLIST ITERATOR		1	6	00:00:00.01	3
7	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	2	6	00:00:00.01	3
8	INDEX RANGE SCAN	EMP_MANAGER_IX	2	6	00:00:00.01	2
9	NESTED LOOPS		1	2	00:00:00.01	6
10	NESTED LOOPS		1	3	00:00:00.01	5
11	INLIST ITERATOR		1	2	00:00:00.01	3
12	TABLE ACCESS BY INDEX ROWID	DEPARTMENT	2	2	00:00:00.01	3
13	INDEX UNIQUE SCAN	DEPT_ID_PK	2	2	00:00:00.01	2
14	INDEX RANGE SCAN	EMP_DEPARTMENT_IX	2	3	00:00:00.01	2
15	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	3	2	00:00:00.01	1

-

Predicate Information (identified by operation id):

---

5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access(("E"."MANAGER_ID"=101 OR "E"."MANAGER_ID"=102))
13 - access(("D"."DEPARTMENT_ID"=10 OR "D"."DEPARTMENT_ID"=20))
14 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter(("E"."DEPARTMENT_ID"=10 OR "E"."DEPARTMENT_ID"=20))
15 - filter((LNNVL("E"."MANAGER_ID"=101) AND LNNVL("E"."MANAGER_ID"=102)))



||10053 Trace||
|{code:sql}
-- Query Transformation 과정에서는 OR-Expansion 관련 정보를 찾을 수 없고, Physical Optimizing 과정에서 흔적을 찾을 수 있음
-- Transformation 후, OR-Expansion 적용전 SQL Cost 산출 [kkoqbc(CostEstimator)]
*******************************************
Peeked values of the binds in SQL statement
*******************************************

kkoqbc: optimizing query block MAIN (#0)
        
        :
    call(in-use=968, alloc=16360), compile(in-use=53704, alloc=54496), execution(in-use=1960, alloc=4060)

kkoqbc-subheap (create addr=0x0D2FBFB0)

... 중략 ...

-- OR-Expansion 의 시작, 분기 조건(Branch)별로 최적화, 첫 번째 Branch에 대해 Costing
*********************************
Number of join permutations tried: 2
*********************************
(newjo-save)    [0 1 ]
Trying or-Expansion on query block MAIN (#0)

******** Next OR predicate ********

Trying or-Expansion on query block MAIN (#0)

******** OR-branching ********

or-expansion-subheap (create addr=0x0D2F781C)  <<<<< 첫 번째 조건(Branch) 최적화

... 중략 ...

-- 두 번째 Branch에 대해 Costing, 적용 전과 비교
*********************************
Number of join permutations tried: 2
*********************************
or-expansion-subheap (delete addr=0x0D2F781C, in-use=36168, alloc=40152)

******** OR-branching ********

or-expansion-subheap (create addr=0x0D3D83CC)  <<<<< 두 번째 조건(Branch) 최적화

... 중략...

*********************************
Number of join permutations tried: 3
*********************************
or-expansion-subheap (delete addr=0x0D3D83CC, in-use=53200, alloc=58212)
or-expansion is better cost:0.000000  <<<<< Cost 비교

******** Same chain after expansion ********

... 중략 ...

-- OR-Expansion 적용 (첫 번째 Branch ; MAIN_1 최적화/생성)
*********************************
Number of join permutations tried: 3
*********************************
Trying or-Expansion on query block MAIN (#0)  <<<<< 저렴한 OR-Expansion 선택

******** Final costing  ********
Registered qb: MAIN_1 0xd39905c (OR EXPANSION MAIN; MAIN; 8)  <<<<< 쿼리블럭 MAIN_1 생성
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=MAIN_1 nbfros=2 flg=0
    fro(0): flg=0 objn=74260 hint_alias="D"@"MAIN"
    fro(1): flg=0 objn=74263 hint_alias="E"@"MAIN"

... 중략 ...

-- 두 번째 Branch ; MAIN_2 최적화/생성
Final cost for query block MAIN_1 (#0) - All Rows Plan:
  Best join order: 1
  Cost: 5.0022  Degree: 1  Card: 6.0000  Bytes: 324
  Resc: 5.0022  Resc_io: 4.0000  Resc_cpu: 22200509
  Resp: 5.0022  Resp_io: 4.0000  Resc_cpu: 22200509
Registered qb: MAIN_2 0xd39905c (OR EXPANSION MAIN)  <<<<< 쿼리블럭 MAIN_2 생성
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=MAIN_2 nbfros=2 flg=0
    fro(0): flg=4 objn=74260 hint_alias="D"@"MAIN_2"
    fro(1): flg=4 objn=74263 hint_alias="E"@"MAIN_2"

...중략...

Final cost for query block MAIN_2 (#0) - All Rows Plan:  <<<<< MAIN_2 의 최저 Cost 와 조인 순서 결정
  Best join order: 1
  Cost: 9.0045  Degree: 1  Card: 13.0000  Bytes: 702
  Resc: 9.0045  Resc_io: 8.0000  Resc_cpu: 22251456
  Resp: 9.0045  Resp_io: 8.0000  Resc_cpu: 22251456

|

OR/IN 조건을 사용할 때 나타나는 OR-Expansion 의 특징
- 특별한 Cost Based Query Transformation 과정 없이 Physical Optimizing 과정에서 발생
- OR-Expansion 변환 결정은 분기 조건 별 분리, 모든 조건에 대해서 분리, 안 분리 로 구분 하며, 기본 값은 분기 조건 별 분리(8)
- UNION ALL 로 분리된 SQL이 3개 이면, 3번의 비용계산 과정을 거쳐 가장 저렴한 Cost 를 가진 3개의 Plan 으로 결정 (쿼리블럭 마다 Physical Optimization 수행)

3.3 OR-Expansion (OR To Union All Conversion) OR 조건을 이용하여 Union All로 변경시켜라 (by openlsw) [2013.06.29]

3.3 OR-Expansion (OR To Union All Conversion) : OR 조건을 이용하여 Union All로 변경시켜라