Local Partition INDEX Test

■ 개요

---

유니크 인덱스를 위주로 관리 편의를 위해 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;