Knowledge/Oracle
Local Partition INDEX Test
neo-orcl
2018. 4. 24. 00:33
■ 개요
유니크 인덱스를 위주로 관리 편의를 위해 LOCAL 인덱스로 변경하거나 처음부터 LOCAL 인덱스로 설계할 때 고려해야할 점을 간단히 테스트해보고자 한다.
최근 고객사에서 이걸 고려하는 건이 있어 정리겸.
■ 사전 스크립트
|
CREATE TABLE sales
( prod_id NUMBER
, cust_id NUMBER
, time_id DATE
)
PARTITION BY RANGE (time_id)
( PARTITION sales_2017_12 VALUES LESS THAN (TO_DATE('01-JAN-2018','dd-MON-yyyy'))
TABLESPACE users
, PARTITION sales_2018_01 VALUES LESS THAN (TO_DATE('01-FEB-2018','dd-MON-yyyy'))
TABLESPACE users
, PARTITION sales_2018_02 VALUES LESS THAN (TO_DATE('01-MAR-2018','dd-MON-yyyy'))
TABLESPACE users
, PARTITION sales_2018_03 VALUES LESS THAN (TO_DATE('01-APR-2018','dd-MON-yyyy'))
TABLESPACE users
, PARTITION sales_2018_04 VALUES LESS THAN (TO_DATE('01-MAY-2018','dd-MON-yyyy'))
TABLESPACE users
);
create sequence sales_seq;
insert into sales values(sales_seq.nextval,1121,to_date('20180101','yyyymmdd'));
insert into sales values(sales_seq.nextval,1122,to_date('20180101','yyyymmdd'));
insert into sales values(sales_seq.nextval,1120,to_date('20180101','yyyymmdd'));
insert into sales values(sales_seq.nextval,1111,to_date('20180101','yyyymmdd'));
insert into sales values(sales_seq.nextval,1123,to_date('20180201','yyyymmdd'));
insert into sales values(sales_seq.nextval,1123,to_date('20180201','yyyymmdd'));
insert into sales values(sales_seq.nextval,1234,to_date('20180201','yyyymmdd'));
insert into sales values(sales_seq.nextval,1643,to_date('20180201','yyyymmdd'));
insert into sales values(sales_seq.nextval,1111,to_date('20180301','yyyymmdd'));
insert into sales values(sales_seq.nextval,2222,to_date('20180301','yyyymmdd'));
insert into sales values(sales_seq.nextval,1321,to_date('20180301','yyyymmdd'));
insert into sales values(sales_seq.nextval,1955,to_date('20180301','yyyymmdd'));
insert into sales values(sales_seq.nextval,1754,to_date('20180401','yyyymmdd'));
insert into sales values(sales_seq.nextval,1027,to_date('20180401','yyyymmdd'));
insert into sales values(sales_seq.nextval,1013,to_date('20180401','yyyymmdd'));
insert into sales values(sales_seq.nextval,1100,to_date('20180401','yyyymmdd'));
commit;
create unique index sales_pk on sales(prod_id) local;
--유니크 인덱스는 파티션 키를 포함해야 local로 만들어진다.
*
--ERROR at line 1:
--ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
create unique index sales_pk on sales(prod_id,time_id) local;
create index sales_custid on sales(cust_id) local;
exec dbms_stats.gather_table_stats(user,'sales');
alter session set statistics_level=all;
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
select /*+gather_plan_statistics*/ * from sales;
PROD_ID CUST_ID TIME_ID
---------- ---------- -----------------
21 1121 20180101 00:00:00
22 1122 20180101 00:00:00
23 1120 20180101 00:00:00
24 1111 20180101 00:00:00
25 1123 20180201 00:00:00
26 1123 20180201 00:00:00
27 1234 20180201 00:00:00
28 1643 20180201 00:00:00
29 1111 20180301 00:00:00
30 2222 20180301 00:00:00
31 1321 20180301 00:00:00
32 1955 20180301 00:00:00
33 1754 20180401 00:00:00
34 1027 20180401 00:00:00
35 1013 20180401 00:00:00
36 1100 20180401 00:00:00 |
■ 엑세스 테스트
이제 where prod_id=NN, where cust_id=NN 할 때 어떻게될까?
1. 파티션 하나의 인덱스만 액세스한다
2. 파티션 전부의 인덱스를 액세스한다?
|
select /*+gather_plan_statistics*/ * from sales where prod_id=28;
set long 5000 line 200 pages 1000
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 |00:00:00.01 | 5 |
| 1 | PARTITION RANGE ALL | | 1 | 1 | 5 | 1 |00:00:00.01 | 5 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 5 | 1 | 5 | 1 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | SALES_PK | 4 | 1 | 5 | 1 |00:00:00.01 | 4 |
----------------------------------------------------------------------------------------------------------------
select /*+gather_plan_statistics*/ * from sales where cust_id=1643;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 |00:00:00.01 | 5 |
| 1 | PARTITION RANGE ALL | | 1 | 1 | 5 | 1 |00:00:00.01 | 5 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 5 | 1 | 5 | 1 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | SALES_CUSTID | 4 | 1 | 5 | 1 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------------------------------------- |
파티션을 하나 추가한다면 버퍼가 하나 더 늘것이다. 데이터가 없어 인덱스 블럭이 하나이기 때문이다
|
alter session set deferred_segment_creation=false;
alter table sales add partition sales_2018_05 VALUES LESS THAN (TO_DATE('01-JUN-2018','dd-MON-yyyy'))
TABLESPACE users;
exec dbms_stats.GATHER_TABLE_STATS(null,'sales',no_invalidate=>false);
select /*+gather_plan_statistics*/ * from sales where prod_id=28;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 |00:00:00.01 | 6 |
| 1 | PARTITION RANGE ALL | | 1 | 1 | 6 | 1 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 6 | 1 | 6 | 1 |00:00:00.01 | 6 |
|* 3 | INDEX RANGE SCAN | SALES_PK | 5 | 1 | 6 | 1 |00:00:00.01 | 5 |
----------------------------------------------------------------------------------------------------------------
select /*+gather_plan_statistics*/ * from sales where cust_id=1643;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 |00:00:00.01 | 6 |
| 1 | PARTITION RANGE ALL | | 1 | 1 | 6 | 1 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 6 | 1 | 6 | 1 |00:00:00.01 | 6 |
|* 3 | INDEX RANGE SCAN | SALES_CUSTID | 5 | 1 | 6 | 1 |00:00:00.01 | 5 |
-------------------------------------------------------------------------------------------------------------------- |
예상대로 하나가 늘었다. 추가된 sales_2018_05 Local Index 블럭이 하나 늘었기 때문이다.
당연히 partition 조건도 같이 들어온다면 single partition만 스캔할것이다.
|
select /*+gather_plan_statistics*/ * from sales where prod_id=28 and time_id=to_date('20180201 00:00:00','yyyymmdd hh24:mi:ss');
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 |00:00:00.01 | 2 |
| 1 | PARTITION RANGE SINGLE | | 1 | 3 | 3 | 1 |00:00:00.01 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 1 | 3 | 3 | 1 |00:00:00.01 | 2 |
|* 3 | INDEX UNIQUE SCAN | SALES_PK | 1 | 3 | 3 | 1 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------------------- |
마지막으로 prod_id는 시퀀스에 의해 right grow Index라서 Heavy하게 Insert될 경우 Index Split 현상으로 경합이 발생하기에
hash partition 인덱스를 만들어서 테스트해본다.
|
drop index sales_pk;
create index sales_pk on sales(prod_id) global
partition by hash(prod_id)
partitions 10
store in (users);
select /*+gather_plan_statistics*/ * from sales where prod_id=23;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 |00:00:00.01 | 3 |
| 1 | PARTITION HASH SINGLE | | 1 | 7 | 7 | 1 |00:00:00.01 | 3 |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 1 | ROWID | ROWID | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | SALES_PK | 1 | 7 | 7 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------
실무에서 그럴일은 없겠지만 Hash 파티션된 대상에 부등호 조건으로 조회한다면 어찌될까
select /*+gather_plan_statistics*/ * from sales where prod_id<=22;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 |00:00:00.01 | 13 |
| 1 | PARTITION HASH ALL | | 1 | 1 | 10 | 2 |00:00:00.01 | 13 |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 10 | ROWID | ROWID | 2 |00:00:00.01 | 13 |
|* 3 | INDEX RANGE SCAN | SALES_PK | 10 | 1 | 10 | 2 |00:00:00.01 | 11 |
----------------------------------------------------------------------------------------------------------------- |
비파티션은 테스트는 생략한다. 위 결과와 의미는 같다.
■결론
테이블을 날짜나 월 등으로 나누어 파티션하고 과거 파티션을 주기적으로 DROP하려고 한다면 LOCAL 인덱스가 관리에 유리하다.
단, 파티션 키가 조건에 같이 사용되지 않는다면 성능은 나쁠 것이다.
미래의 파티션을 만들어둘 경우 파티션의 갯수만큼 블럭 접근이 더 많아질 것이다만 한 파티션당 1 블럭 정도야 문제 없을 것이다.
문제는 과거의 파티션이다. 과거의 파티션은 RANSE SCAN을 위해 파티션 인덱스마다 root-branch를 거쳐 leaf 블럭을
하나는 접근하게 될 것이다.
데이터가 많으면 blevel이 높을 것이다. 추출 행이 적다면 포기할 성능 수준은 감안할 수 있지만추출행이
추출행이 많고 NL같은 조인이 일어난다면 그 차이는 심각해진다.
비파티션은 Leaf 블럭까지 접근은 한번만 일어날 것이다.
한가지 더 짚고 넘어가야 할 것은 중복 무결성 문제다.
UNIQUE 조건은 LOCAL 인덱스를 만들기 위해 파티션된 컬럼을 포함해줘야 한다.
만약 prod_id가 시퀀스를 통해 입력되지 않았다면? prod_id를 UPDATE하는 건이 있다면?
|
PROD_ID CUST_ID TIME_ID
---------- ---------- -----------------
21 1121 20180101 00:00:00
22 1122 20180101 00:00:00
23 1120 20180101 00:00:00
24 1111 20180101 00:00:00
25 1123 20180201 00:00:00
26 1123 20180201 00:00:00
27 1234 20180201 00:00:00
28 1643 20180201 00:00:00
29 1111 20180301 00:00:00
30 2222 20180301 00:00:00
31 1321 20180301 00:00:00
32 1955 20180301 00:00:00
33 1754 20180401 00:00:00
34 1027 20180401 00:00:00
35 1013 20180401 00:00:00
36 1100 20180401 00:00:00
Insert into sales values(36, 1231 , to_date('20180401 00:00:01','yyyymmdd hh24:mi:ss')); |
만약 global이나 non-partition으로 unique 인덱스를 만들었다면 에러 나겠지만,
local로 prod_id+time_id를 만들었다면 정상적으로 입력되어 버린다.
|
drop index sales_pk;
create unique index sales_pk on sales(prod_id,time_id) local;
SQL> Insert into sales values(36, 1231 , to_date('20180401 00:00:01','yyyymmdd hh24:mi:ss'));
1 row created.
SQL> commit;
Commit complete.
select /*+gather_plan_statistics*/ * from sales where prod_id=36;
PROD_ID CUST_ID TIME_ID
---------- ---------- -----------------
36 1100 20180401 00:00:00
36 1231 20180401 00:00:01 |
회원정보 테이블의 중복을 허용하지 말아야 하는 유저닉네임 이라면 어찌될까? 잘 생각하자
이런 점들 때문에 OLTP는 일반적으로 GLOBAL이나 비파티션을 권장하게 된다.
그런데 주로 history성 테이블에 파티션을 range로 주로 적용하기에 문제가 안될 경우도 많다.
■정리 script
|
drop table sales purge;
drop sequence sales_seq; |