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

3.5 TE (Table Expansion) : 여러 개의 파티션을 액세스 할 때 파티션 마다 Union All로 분리해서 Index scan을 할지 FTS를 할지 판단하라

1. 여러 개의 파티션을 액세스 할 때 파티션마다 인덱스 사용 / 테이블 스캔 으로 실행 계획을 다르게 함
2. 단 11.2.0.1 까지는 정상적인 경우에 수행되지 않음 (Local Partition Index 필요, 특정 파티션의 인덱스가 Unusable 상태 필요, Bind 변수 불가)
3. _optimizer_table_expansion = True
4. 힌트 (EXPAND_TABLE / NO_EXPAND_TABLE)

TE 예제(변수)
{code:sql	borderStyle=solid} var v_from varchar2(8); var v_to varchar2(8);

exec :v_from := '19991230';
exec :v_to := '20000331';

SELECT COUNT(*)
FROM sales s
WHERE s.time_id BETWEEN TO_DATE(:V_FROM, 'YYYYMMDD')
AND TO_DATE(:V_TO, 'YYYYMMDD');

-

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	FILTER		1	0	00:00:00.01
3	PARTITION RANGE ITERATOR		1	0	00:00:00.01
4	BITMAP CONVERSION COUNT		2	0	00:00:00.01
5	BITMAP INDEX FAST FULL SCAN	SALES_TIME_BIX	2	0	00:00:00.01

-

Predicate Information (identified by operation id):

---

2 - filter(TO_DATE(:V_FROM,'YYYYMMDD')<=TO_DATE(:V_TO,'YYYYMMDD'))
5 - filter(("S"."TIME_ID">=TO_DATE(:V_FROM,'YYYYMMDD') AND
"S"."TIME_ID"<=TO_DATE(:V_TO,'YYYYMMDD')))

– 19991230 ~ 19991231 범위는 인덱스 스캔이 유리
– 20000101 ~ 20000331 범위는 테이블 풀 스캔이 유리
-- 하지만 TE 가 발생하지 않는다 (아래 10053 Trace 참조 : 바인드 변수)

|
||10053 Trace||
|{code:sql}
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: Predicates contain binds.
...생략...

|

TE 예제(상수)
{code:sql	borderStyle=solid} SELECT COUNT (*) FROM sh.sales s WHERE time_id BETWEEN TO_DATE('19991230','YYYYMMDD') AND TO_DATE('20000331','YYYYMMDD');

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	PARTITION RANGE ITERATOR		1	0	00:00:00.01
3	BITMAP CONVERSION COUNT		2	0	00:00:00.01
4	BITMAP INDEX FAST FULL SCAN	SALES_TIME_BIX	2	0	00:00:00.01

Predicate Information (identified by operation id):

---

4 - filter(("TIME_ID">=TO_DATE(' 1999-12-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "TIME_ID"<=TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))

-- 역시 TE가 발생하지 않는다

|
||TE 예제(상수 + Unusable Local Index)||
|{code:sql|borderStyle=solid}
alter index sh.sales_time_bix modify partition sales_q1_2000 unusable;

-----------------------------------------------------------------------------------------
| 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 |   VIEW                          | VW_TE_2        |      1 |      0 |00:00:00.01 |
|   3 |    UNION-ALL                    |                |      1 |      0 |00:00:00.01 |
|   4 |     PARTITION RANGE SINGLE      |                |      1 |      0 |00:00:00.01 |
|   5 |      BITMAP CONVERSION TO ROWIDS|                |      1 |      0 |00:00:00.01 |
|*  6 |       BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX |      1 |      0 |00:00:00.01 |
|   7 |     PARTITION RANGE SINGLE      |                |      1 |      0 |00:00:00.01 |
|*  8 |      TABLE ACCESS FULL          | SALES          |      1 |      0 |00:00:00.01 |
-----------------------------------------------------------------------------------------

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

   1 - SEL$0737CF93
   2 - SET$6B41CA49   / VW_TE_2@SEL$0737CF93
   3 - SET$6B41CA49
   4 - SET$6B41CA49_1
   5 - SET$6B41CA49_1 / S@SEL$1
   7 - SET$6B41CA49_2
   8 - SET$6B41CA49_2 / S@SEL$1

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(@"SET$6B41CA49_2")
      OUTLINE_LEAF(@"SET$6B41CA49_1")
      OUTLINE_LEAF(@"SET$6B41CA49")
      EXPAND_TABLE(@"SEL$1" "S"@"SEL$1")  <<<<< TE의 비밀
      OUTLINE_LEAF(@"SEL$0737CF93")
      OUTLINE(@"SET$6B41CA49")
      EXPAND_TABLE(@"SEL$1" "S"@"SEL$1")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$0737CF93" "VW_TE_2"@"SEL$0737CF93")
      BITMAP_TREE(@"SET$6B41CA49_1" "S"@"SEL$1" AND(("SALES"."TIME_ID")))  <<<<< 위쪽 쿼리 블럭 BITMAP INDEX RANGE SCAN
      FULL(@"SET$6B41CA49_2" "S"@"SEL$1")                                  <<<<< 아래쪽 쿼리 블럭 TABLE ACCESS FULL
      END_OUTLINE_DATA
  */

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

   6 - access("TIME_ID">=TO_DATE(' 1999-12-30 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "S"."TIME_ID"<TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   8 - filter("TIME_ID"<=TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

-- TE 발생 : Troubleshooting Oracle Performance 의 저자인 스위스의 Christian Antognini 가 최초로 발견 (http://antognini.ch/ Zero-Size Unusable Indexes and the Query Optimizer)

|

10053 Trace
{code:sql} ********************************* Cost-Based Table Expansion ********************************* TE: Checking validity of TE for query block SEL$1 (#1) TE: Checking validity of table expansion for query block SEL$1 (#1)

TE: Using search type: linear
Table expansion on query block SEL$1 (#1)
TE: Considering table expansion on query block SEL$1 (#1)
TE: Starting iteration 1, state space = (1) : (0) <<<<<<<<<< TE 안된 상태 Costing (Iteration 1)
FPD: Considering simple filter push in query block SEL$1 (#1)
"S"."TIME_ID">=TO_DATE(' 1999-12-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID"<=TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
try to generate transitive predicate from check constraints for query block SEL$1 (#1)
finally: "S"."TIME_ID">=TO_DATE(' 1999-12-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID"<=TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(' 1999-12-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-d

FPD: transitive predicates are generated in query block SEL$1 (#1)
"S"."TIME_ID">=TO_DATE(' 1999-12-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID"<=TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(' 1999-12-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-d
TE: Costing transformed query.
CBQT: Looking for cost annotations for query block SEL$1, key = SEL$1_00000000_0
CBQT: Could not find stored cost annotations.
kkoqbc: optimizing query block SEL$1 (#1)

... 중략 ...

TE: Updated best state, Cost = 203.21 <<<<<<<<<<<<<< Iteration 1 의 Cost
TE: Starting iteration 2, state space = (1) : (1) <<<<<<<<<< TE 적용 상태 Costing (Iteration 2)
TE: before predicates generation:******* UNPARSED QUERY IS *******
SELECT COUNT() "COUNT()" FROM "TLO"."SALES" "S" WHERE "S"."TIME_ID">=TO_DATE('19991230','YYYYMMDD') AND "S"."TIME_ID"<=TO_DATE('20000331','YYYYMMDD')
Registered qb: SEL$0737CF93 0x74f67268 (QUERY BLOCK TABLES CHANGED SEL$1)

-
QUERY BLOCK SIGNATURE

-
signature (): qb_name=SEL$0737CF93 nbfros=1 flg=0
fro(0): flg=5 objn=0 hint_alias="VW_TE_1"@"SEL$0737CF93"

Registered qb: SEL$7A21928B 0x74f6f5c0 (SPLIT/MERGE QUERY BLOCKS SEL$0737CF93)

-
QUERY BLOCK SIGNATURE

-
signature (): qb_name=SEL$7A21928B nbfros=1 flg=0
fro(0): flg=0 objn=80643 hint_alias="S"@"SEL$1"

Registered qb: SEL$7A21928B 0x74f8bf30 (COPY SEL$7A21928B)

-
QUERY BLOCK SIGNATURE

-
signature(): NULL
TE: after table expansion:******* UNPARSED QUERY IS *******
SELECT COUNT() "COUNT()" FROM ( (SELECT 0 FROM "TLO"."SALES" "S" WHERE "S"."TIME_ID"<=TO_DATE('20000331','YYYYMMDD') AND "S"."TIME_ID">=TO_DATE('19991230','YYYYMMDD') AND ("S"."TIME_ID"<TO_DATE(' 2000-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') OR "S"."TIME_ID">=GREATEST(TO_DATE('19991230','YYYYMMDD'),TO_DATE(' 2000-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) AND "S"."TIME_ID"<TO_DATE(' 2004-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) UNION ALL (SELECT 0 FROM "TLO"."SALES" "S" WHERE "S"."TIME_ID"<=TO_DATE('20000331','YYYYMMDD') AND "S"."TIME_ID">=GREATEST(TO_DATE('19991230','YYYYMMDD'),TO_DATE(' 2000-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) AND "S"."TIME_ID"<TO_DATE(' 2000-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) "VW_TE_1"
<<<<<<< 중간단계쿼리(GREATEST, NLS_CALENDAR=GREGORIAN 비효율)
Registered qb: SET$6B41CA49 0x74f89698 (TABLE EXPANSION SEL$1; SEL$1; "S"@"SEL$1")

... 중략 ...

TE: Updated best state, Cost = 104.65 <<<<<<<<<<<<<< Iteration 2 의 Cost (선택)
TE: Transferring best state space to preseved query.
TE: Will expand table #1 named SALES (Preserved)
TE: Transferring best state space to preseved query.
TE: Will expand table #1 named SALES (Original)
kkqctdrvTD-cleanup: transform(in-use=16848, alloc=20880) :
call(in-use=160872, alloc=196392), compile(in-use=302736, alloc=308392), execution(in-use=2512, alloc=4032)

kkqctdrvTD-end:
call(in-use=160872, alloc=196392), compile(in-use=281712, alloc=308392), execution(in-use=2512, alloc=4032)

TE: Checking validity of table expansion for query block SEL$1 (#1)

TE: before predicates generation:******* UNPARSED QUERY IS *******
SELECT COUNT() "COUNT()" FROM "TLO"."SALES" "S" WHERE "S"."TIME_ID">=TO_DATE('19991230','YYYYMMDD') AND "S"."TIME_ID"<=TO_DATE('20000331','YYYYMMDD') <<<<< TE 전
Registered qb: SEL$7A21928B 0x753ec260 (COPY SEL$7A21928B)

-
QUERY BLOCK SIGNATURE

-
signature(): NULL
TE: after table expansion:******* UNPARSED QUERY IS *******
SELECT COUNT() "COUNT()" FROM ( (SELECT 0 FROM "TLO"."SALES" "S" WHERE "S"."TIME_ID"<=TO_DATE('20000331','YYYYMMDD') AND "S"."TIME_ID">=TO_DATE('19991230','YYYYMMDD') AND ("S"."TIME_ID"<TO_DATE(' 2000-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') OR "S"."TIME_ID">=GREATEST(TO_DATE('19991230','YYYYMMDD'),TO_DATE(' 2000-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) AND "S"."TIME_ID"<TO_DATE(' 2004-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) UNION ALL (SELECT 0 FROM "TLO"."SALES" "S" WHERE "S"."TIME_ID"<=TO_DATE('20000331','YYYYMMDD') AND "S"."TIME_ID">=GREATEST(TO_DATE('19991230','YYYYMMDD'),TO_DATE(' 2000-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) AND "S"."TIME_ID"<TO_DATE(' 2000-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) "VW_TE_2"
<<<< TE 후
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT() "COUNT()" FROM ( (SELECT 0 FROM "TLO"."SALES" "S" WHERE "S"."TIME_ID"<=TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID">=TO_DATE(' 1999-12-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID"<TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')>=TO_DATE(' 1999-12-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(' 1999-12-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) UNION ALL (SELECT 0 FROM "TLO"."SALES" "S" WHERE "S"."TIME_ID"<=TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID"<TO_DATE(' 2000-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(' 2000-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')>=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<TO_DATE(' 2000-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) "VW_TE_2" <<<<< TE 최종

3.5 TE (Table Expansion) 여러 개의 파티션을 액세스 할 때 파티션 마다 Union All로 분리해서 Index scan을 할지 FTS를 할지 판단하라 (by openlsw) [2013.06.29]

3.5 TE (Table Expansion) : 여러 개의 파티션을 액세스 할 때 파티션 마다 Union All로 분리해서 Index scan을 할지 FTS를 할지 판단하라