파티션 테이블 데이터 삭제 - patisyeon teibeul deiteo sagje

파티션 테이블의 사용목적이 있겠지만,

데이터 보관주기 적용을 위하여 파티션 테이블을 자주 사용하는것 같습니다.

그러면 당연히 기존 오래된 데이터를 삭제하기 위해서는 파티션의 DROP혹은 TRUNCATE 작업도 하게 되는데,

두작업은 테이블의 구조가 변경되느냐의 차이가 있어. 이는 곧 PLAN의 변화에 영향을 주게 되므로 , 관리정책과, 업무 영향도를 고려해서 작업해야될것 입니다.

아래 예제는 파티션 DROP하였을 경우 PLAN이 변경되는 사례입니다.

drop table SALES purge;
CREATE TABLE SALES
( COL1 NUMBER        NOT NULL,
  COL2 VARCHAR2(4000)
)PARTITION BY RANGE ( COL1 )
 (
    PARTITION P01 VALUES LESS THAN (100),
    PARTITION P02 VALUES LESS THAN (200),
    PARTITION P03 VALUES LESS THAN (300),
    PARTITION P04 VALUES LESS THAN (400),
    PARTITION P05 VALUES LESS THAN (MAXVALUE)
) ;

insert into sales
select level, lpad(level,4000,'A')
from dual
connect by level < 400;
commit;

create index IX_SALES_01 on SALES
(COL1)
local ;

exec dbms_stats.gather_table_stats('SYS','SALES');

- 기본 PLAN

select /*+ gather_plan_statistics */ max(col2) from sales where col1 > 198;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

SQL_ID  22688xv9pdwkq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(col2) from sales where col1 >
198

Plan hash value: 1500327972

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       |      1 |        |      1 |00:00:00.01 |     315 |
|   1 |  SORT AGGREGATE           |       |      1 |      1 |      1 |00:00:00.01 |     315 |
|   2 |   PARTITION RANGE ITERATOR|       |      1 |    202 |    201 |00:00:00.01 |     315 |
|*  3 |    TABLE ACCESS FULL      | SALES |      4 |    202 |    201 |00:00:00.01 |     315 |
---------------------------------------------------------------------------------------------

- 파티션 DROP후 PLAN

alter table SALES drop partition p01;
select /*+ gather_plan_statistics */ max(col2) from sales where col1 > 198;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

SQL_ID  22688xv9pdwkq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(col2) from sales where col1 >
198

Plan hash value: 3519235612

----------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 |        |      1 |00:00:00.01 |     315 |
|   1 |  SORT AGGREGATE      |       |      1 |      1 |      1 |00:00:00.01 |     315 |
|   2 |   PARTITION RANGE ALL|       |      1 |    202 |    201 |00:00:00.01 |     315 |
|*  3 |    TABLE ACCESS FULL | SALES |      4 |    202 |    201 |00:00:00.01 |     315 |
----------------------------------------------------------------------------------------

파티션 DROP작업으로 지금과 같은 예제에서는 성능 차이가 크게 나지 않겠지만.

대부분의 DB가 통계정보를 잘 관리못하고 있는 실정이므로, PLAN변경이라는 것 자체가 운영에서는 큰 Risk로 다가갈수 잇습니다.

고래서,

좀 지저분 하더라도, PLAN변경방지를 위하여 DROP보다는 truncate하는것으로 고려하는게 좋을것 같습니다.