복사되었습니다!
OS 환경 : Oracle Linux 9.6 (64bit)
DB 환경 : Oracle Database 19.31.0.0
방법 : 오라클 19c 비파티션 테이블에서 파티션 테이블로 전환 및 성능 비교
오라클에서 비파티션 테이블을 사용하다가 용량이 너무 커지면 파티션 테이블로의 전환을 고려하게됨
본문에서는 비파티션 테이블에서 파티션 테이블로 전환하는 방법을 설명하고 이때 고려해야할 사항을 실행계획을 보면서 확인해봄
참고로 본문 방식 외에도 Redefinition 이나 12c 부터 제공되는 alter table modify online 옵션을 이용해 온라인으로도 변경가능함
참고 : 오라클 19c 비파티션 테이블에서 파티션 테이블 온라인 전환 방법 ( https://positivemh.tistory.com/1148 )
https://www.oracle.com/kr/database/technologies/high-availability/online-ops.html
테스트
비파티션 테이블 생성
파티션 테이블 전환시 sale_dt 컬럼을 파티션 키로 사용 예정임
|
1
2
3
4
5
6
7
|
SQL>
drop table sales_hst purge;
create table sales_hst (
id number,
sale_dt varchar2(8),
val varchar2(100)
);
|
테이블에 데이터 100만 건 적재
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
insert into sales_hst
select level,
'2026'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09'),
lpad('X', 50, 'X')
from dual
connect by level <= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
|
인덱스 3개 생성
|
1
2
3
4
|
SQL>
create index sales_hst_idx01 on sales_hst(sale_dt, id) online;
create index sales_hst_idx02 on sales_hst(id, sale_dt) online;
create index sales_hst_idx03 on sales_hst(val, sale_dt) online;
|
테이블 통계정보 수집
|
1
2
3
|
SQL> exec dbms_stats.gather_table_stats(ownname=>'IMSI', tabname=>'SALES_HST', cascade=>true);
PL/SQL procedure successfully completed.
|
전체 데이터 건수 확인
|
1
2
3
4
5
6
7
8
|
SQL>
set lines 200 pages 1000
col count for 9999999
select count(*) from sales_hst;
COUNT(*)
----------
1000000
|
100만건임
마이그레이션 전 특정 월(3월) 조회 플랜 및 버퍼 확인
플랜확인1. 파티션키를 포함한 full scan 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
SQL> alter session set statistics_level=all;
Session altered.
SQL>
select /*+ initial_test full(a) */ count(*)
from sales_hst a
where sale_dt >= '20260301'
and sale_dt < '20260401';
COUNT(*)
----------
90768
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry -rows -bytes'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 995rwyqmkst6f, child number 0
-------------------------------------
select /*+ initial_test full(a) */ count(*) from sales_hst a where
sale_dt >= '20260301' and sale_dt < '20260401'
Plan hash value: 2494548075
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2749 (100)| | 1 |00:00:00.04 | 9858 |
| 1 | SORT AGGREGATE | | 1 | | | 1 |00:00:00.04 | 9858 |
|* 2 | TABLE ACCESS FULL| SALES_HST | 1 | 2749 (1)| 00:00:01 | 90768 |00:00:00.04 | 9858 |
---------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("SALE_DT">='20260301' AND "SALE_DT"<'20260401'))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - initial_test
2 - SEL$1 / A@SEL$1
- full(a)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
54 rows selected.
|
아직 파티션 테이블이 아니기 때문에 full scan을 하여 총 9858 buffer를 소모함
플랜확인2. 파티션키를 포함한 index scan 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
SQL>
select /*+ initial_test index(a) */ count(*)
from sales_hst a
where id = 790983
and sale_dt >= '20260301'
and sale_dt < '20260401';
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry -rows -bytes'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6hnqh0ggrgt3q, child number 0
-------------------------------------
select /*+ initial_test index(a) */ count(*) from sales_hst a where id
= 790983 and sale_dt >= '20260301' and sale_dt < '20260401'
Plan hash value: 2466835552
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (100)| | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | | | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN| SALES_HST_IDX02 | 1 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "A"@"SEL$1" ("SALES_HST"."ID" "SALES_HST"."SALE_DT"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=790983 AND "SALE_DT">='20260301' AND "SALE_DT"<'20260401')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - initial_test
2 - SEL$1 / A@SEL$1
- index(a)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
54 rows selected.
|
SALES_HST_IDX02 인덱스를 이용하여 3 buffer를 소모함
플랜확인3. 파티션키를 미포함한 index scan 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
SQL>
select /*+ initial_test index(a) */ count(*)
from sales_hst a
where id = 12345;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry -rows -bytes'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4mvjd7f8mrdw4, child number 0
-------------------------------------
select /*+ initial_test index(a) */ count(*) from sales_hst a where id
= 12345
Plan hash value: 2466835552
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (100)| | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | | | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN| SALES_HST_IDX02 | 1 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "A"@"SEL$1" ("SALES_HST"."ID" "SALES_HST"."SALE_DT"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=12345)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - initial_test
2 - SEL$1 / A@SEL$1
- index(a)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
54 rows selected.
|
SALES_HST_IDX02 인덱스를 이용하여 3 buffer를 소모함
파티션 테이블로 전환
신규 파티션 테이블(TMP) 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL>
create table sales_hst_tmp (
id number,
sale_dt varchar2(8),
val varchar2(100)
)
partition by range (sale_dt) (
partition p_202601 values less than ('20260201'),
partition p_202602 values less than ('20260301'),
partition p_202603 values less than ('20260401'),
partition p_202604 values less than ('20260501'),
partition p_202605 values less than ('20260601'),
partition p_202606 values less than ('20260701'),
partition p_202607 values less than ('20260801'),
partition p_202608 values less than ('20260901'),
partition p_202609 values less than ('20261001'),
partition p_202610 values less than ('20261101'),
partition p_202611 values less than ('20261201'),
partition p_202612 values less than ('20270101'),
partition p_max values less than (maxvalue)
)
nologging;
Table created.
|
parallel append 힌트를 이용한 itas로 빠르게 적재
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> alter session enable parallel dml;
Session altered.
SQL>
insert /*+ append parallel(t 8) */ into sales_hst_tmp t
select /*+ parallel(s 8) */ * from sales_hst s;
1000000 rows created.
SQL> commit;
Commit complete.
|
기존 테이블 및 파티션 테이블 Rename
*이때부터 기존 테이블에 접근 못하는 다운타임이라고 보면됨
|
1
2
3
4
5
6
7
|
SQL> rename sales_hst to sales_hst_bkp;
Table renamed.
SQL> rename sales_hst_tmp to sales_hst;
Table renamed.
|
(선택)기존 인덱스 rename 및 local 파티션 인덱스 생성
|
1
2
3
4
5
6
7
8
9
10
|
--선택--
SQL>
alter index sales_hst_idx01 rename to sales_hst_idx01new;
alter index sales_hst_idx02 rename to sales_hst_idx02new;
alter index sales_hst_idx03 rename to sales_hst_idx03new;
create index sales_hst_idx01 on sales_hst(sale_dt, id) local parallel 8 nologging online;
create index sales_hst_idx02 on sales_hst(id, sale_dt) local parallel 8 nologging online;
create index sales_hst_idx03 on sales_hst(val, sale_dt) local parallel 8 nologging online;
--선택--
|
또는 다른이름으로 local 파티션 인덱스 생성
(기존 인덱스를 rename 하지 않으면 원복이 조금더 간편함)
|
1
2
3
4
|
SQL>
create index sales_hst_part_idx01 on sales_hst(sale_dt, id) local parallel 8 nologging online;
create index sales_hst_part_idx02 on sales_hst(id, sale_dt) local parallel 8 nologging online;
create index sales_hst_part_idx03 on sales_hst(val, sale_dt) local parallel 8 nologging online;
|
nologging과 parallel 옵션 원복
|
1
2
3
4
5
6
7
8
|
--table
SQL> alter table sales_hst logging;
--index
SQL>
alter index sales_hst_part_idx01 noparallel logging;
alter index sales_hst_part_idx02 noparallel logging;
alter index sales_hst_part_idx03 noparallel logging;
|
파티션 테이블 통계정보 수집
|
1
2
3
|
SQL> exec dbms_stats.gather_table_stats(ownname=>'IMSI', tabname=>'SALES_HST', cascade=>true);
PL/SQL procedure successfully completed.
|
마이그레이션 전 특정 월(3월) 조회 플랜 및 버퍼 확인
플랜확인1. 파티션키를 포함한 full scan 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
SQL> alter session set statistics_level=all;
Session altered.
SQL>
select /*+ partition_test full(a) */ count(*)
from sales_hst a
where sale_dt >= '20260301'
and sale_dt < '20260401';
COUNT(*)
----------
90768
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry -rows -bytes'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5b9us4bum72z3, child number 0
-------------------------------------
select /*+ partition_test full(a) */ count(*) from sales_hst a where
sale_dt >= '20260301' and sale_dt < '20260401'
Plan hash value: 2230796340
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 247 (100)| | | | 1 |00:00:00.01 | 885 |
| 1 | SORT AGGREGATE | | 1 | | | | | 1 |00:00:00.01 | 885 |
| 2 | PARTITION RANGE SINGLE| | 1 | 247 (1)| 00:00:01 | 3 | 3 | 90768 |00:00:00.01 | 885 |
| 3 | TABLE ACCESS FULL | SALES_HST | 1 | 247 (1)| 00:00:01 | 3 | 3 | 90768 |00:00:00.01 | 885 |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
END_OUTLINE_DATA
*/
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - partition_test
3 - SEL$1 / A@SEL$1
- full(a)
46 rows selected.
|
full scan을 하였지만 단일 파티션만 읽어(Operation의 PARTITION RANGE SINGLE 부분) 885 buffer만 소모함
파티션 프루닝이 잘 동작함
플랜확인2. 파티션키를 포함한 index scan 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
SQL>
select /*+ partition_test index(a) */ count(*)
from sales_hst a
where id = 790983
and sale_dt >= '20260301'
and sale_dt < '20260401';
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry -rows -bytes'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6hnqh0ggrgt3q, child number 0
-------------------------------------
select /*+ partition_test index(a) */ count(*) from sales_hst a where id
= 790983 and sale_dt >= '20260301' and sale_dt < '20260401'
Plan hash value: 2441563893
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (100)| | | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | | | | | 1 |00:00:00.01 | 2 |
| 2 | PARTITION RANGE SINGLE| | 1 | 2 (0)| 00:00:01 | 3 | 3 | 1 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN | SALES_HST_PART_IDX02 | 1 | 2 (0)| 00:00:01 | 3 | 3 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "A"@"SEL$1" ("SALES_HST"."ID" "SALES_HST"."SALE_DT"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=790983 AND "SALE_DT">='20260301' AND "SALE_DT"<'20260401')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - partition_test
3 - SEL$1 / A@SEL$1
- index(a)
51 rows selected.
|
SALES_HST_PART_IDX02 인덱스를 이용하여 총 2 buffer를 소모함
마찬가지로 단일 파티션 인덱스만 읽음(Operation의 PARTITION RANGE SINGLE 부분)
플랜확인3. 파티션키를 미포함한 index scan 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
SQL>
select /*+ partition_test index(a) */ count(*)
from sales_hst a
where id = 12345;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry -rows -bytes'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f5k7r4jj2bvkn, child number 0
-------------------------------------
select /*+ partition_test index(a) */ count(*) from sales_hst a where id
= 12345
Plan hash value: 61747622
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 (100)| | | | 1 |00:00:00.01 | 24 |
| 1 | SORT AGGREGATE | | 1 | | | | | 1 |00:00:00.01 | 24 |
| 2 | PARTITION RANGE ALL| | 1 | 14 (0)| 00:00:01 | 1 | 13 | 1 |00:00:00.01 | 24 |
|* 3 | INDEX RANGE SCAN | SALES_HST_PART_IDX02 | 13 | 14 (0)| 00:00:01 | 1 | 13 | 1 |00:00:00.01 | 24 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "A"@"SEL$1" ("SALES_HST"."ID" "SALES_HST"."SALE_DT"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=12345)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - partition_test
3 - SEL$1 / A@SEL$1
- index(a)
51 rows selected.
|
SALES_HST_PART_IDX02 인덱스를 이용하였지만 파티션을 전체 스캔하여(Operation의 PARTITION RANGE ALL 부분) 총 24 buffer를 소모함
인덱스는 일반적으로 root block, branch block(n개), leaf block로 구성됨
하지만 데이터가 많이 없는 인덱스의 경우 branch 없이 root block, leaf block만 존재하기도 함
현재 파티션 테이블의 인덱스 blevel 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>
set lines 200 pages 1000
col partition_name for a10
select partition_name, blevel, leaf_blocks, num_rows
from dba_ind_partitions
where index_name = 'SALES_HST_PART_IDX02'
order by partition_position;
PARTITION_ BLEVEL LEAF_BLOCKS NUM_ROWS
---------- ---------- ----------- ----------
P_202601 1 158 45283
P_202602 1 316 90454
P_202603 1 317 90768
P_202604 1 320 91627
P_202605 1 315 90395
P_202606 1 318 91165
P_202607 1 316 90695
P_202608 1 318 91031
P_202609 1 317 90747
P_202610 1 318 91233
P_202611 1 317 90752
P_202612 1 160 45850
P_MAX 0 0 0
13 rows selected.
|
개별 날짜 파티션 12개의 blevel이 1이고(1 root block, 1 leaf block), max 파티션은 blevel이 0임(데이터 0건)
읽은 블록을 계산해보면 아래와 같음
2 block(root, leaf) * 12(날짜 파티션 갯수) = 24 block
이렇게 총 24 블록을 읽은것임
추가1. 글로벌 인덱스 생성 후 파티션키를 미포함한 index scan 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
SQL> create index sales_hst_glob_idx01 on sales_hst(id) global online;
Index created.
SQL>
select /*+ partition_test index(a sales_hst_glob_idx01) */ count(*)
from sales_hst a
where id = 12345;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry -rows -bytes'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f3gchdrpp0vwm, child number 0
-------------------------------------
select /*+ partition_test index(a sales_hst_glob_idx01) */ count(*)
from sales_hst a where id = 12345
Plan hash value: 129318242
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (100)| | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | | | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN| SALES_HST_GLOB_IDX01 | 1 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "A"@"SEL$1" ("SALES_HST"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=12345)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - partition_test
2 - SEL$1 / A@SEL$1
- index(a sales_hst_glob_idx01)
50 rows selected.
|
글로벌 인덱스 사용 시 비파티션 테이블처럼 3 buffer만 소모함
비교해보면 아래와 같음
| 읽은 블록 비교 | 파티션키 포함 full scan |
파티션키 포함 index scan |
파티션키 미포함 index scan |
파티션키 미포함 global index scan |
| 비파티션 테이블 | 9858 | 3 | 3 | - |
| 파티션 테이블 | 885 | 2 | 24 | 3 |
결론 :
본문의 방식을 통해 비파티션 테이블에서 파티션 테이블로 전환할 수 있음
단순히 테이블 용량이 크다는 이유만으로 파티션 테이블로 전환하게 되면 예상치 못한 부작용이 발생할 수 있기때문에 사전에 어플리케이션의 sql 액세스 패턴을 반드시 고려해 봐야 함
가장 중요한 것은 파티션 키 조건이 where 절에 자주 들어오는가임
이 조건이 들어오게 되면 파티션 프루닝이 일어나 특정 파티션에만 접근하여 데이터를 가져오면서 읽는 block 수가 줄어듬
하지만 파티션 키 조건이 where 절에 들어오지 않는다면 위와 같이 모든 파티션의 인덱스 B-Tree를 개별적으로 반복 탐색(PARTITION RANGE ALL)해야해서
비파티션 테이블일 때(3 buffer)보다 오히려 파티션 개수만큼 논리적 I/O가 배수로 증가(24 buffer)하는 성능 저하를 겪을 수 있음
만약 운영 환경에서 파티션 개수가 수십~수백개이고 데이터양도 많아서 blevel도 높다면 buffer를 많이 낭비할 수 있음
이 경우 글로벌 인덱스를 사용하면 buffer 낭비를 막을 수는 있지만 추후 파티션 제거, 변경 등의 작업을 하면 인덱스가 unusable 상태로 빠져 rebuild를 다시 해주기 전까지 플랜이 틀어 질 수 있음
참조 :
오라클 19c 비파티션 테이블에서 파티션 테이블 온라인 전환 방법 ( https://positivemh.tistory.com/1148 )
오라클 19c 대량 샘플데이터 생성용 쿼리 벌크 인서트 ( https://positivemh.tistory.com/808 )
https://www.oracle.com/kr/database/technologies/high-availability/online-ops.html
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c Restart 환경 hostname 변경 가이드 (0) | 2026.06.21 |
|---|---|
| 오라클 19c Active Data Guard(ADG)와 Data Guard(DG) 차이점 및 DML Redirection 기능 (0) | 2026.06.16 |
| 오라클 19c ADG Snapshot Standby Database (0) | 2026.06.14 |
| 오라클 19c ADG 구성 중 db_file_name_convert, log_file_name_convert 파라미터 테스트 (0) | 2026.06.12 |
| 오라클 19c ADG Gap 발생시 rman 증분백업이용 복구 시나리오 (0) | 2026.06.11 |