파티션과 대량 DML

CREATE TABLE emp_partition2
PARTITION BY RANGE (deptno)
( PARTITION p1 VALUES LESS THAN (20),
  PARTITION p2 VALUES LESS THAN (30),
  PARTITION p3 VALUES LESS THAN (40) )
AS SELECT * FROM emp;

CREATE TABLE 주문_해쉬파티션
( 주문번호 NUMBER, 주문일자 VARCHAR2(8), 고객ID VARCHAR2(5), 주문금액 NUMBER )
PARTITION BY HASH (고객id) PARTITIONS 8;

CREATE TABLE emp_job_p
( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4),
  hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2),
  CONSTRAINT emp_job_p_pk PRIMARY KEY (empno) )
PARTITION BY LIST (job)
( PARTITION p_president VALUES ('PRESIDENT'),
  PARTITION p_manager   VALUES ('MANAGER'),
  PARTITION p_analyst   VALUES ('ANALYST'),
  PARTITION p_salesman  VALUES ('SALESMAN'),
  PARTITION p_clerk     VALUES ('CLERK'),
  PARTITION p_def       VALUES (DEFAULT) );

SELECT partition_name, num_rows
  FROM user_tab_partitions
 WHERE table_name = 'EMP_JOB_P';

CREATE INDEX 주문_x01 ON 주문 (주문일자, 주문금액) LOCAL;      -- 로컬 Prefixed
CREATE INDEX 주문_x02 ON 주문 (고객id, 주문일자) LOCAL;        -- 로컬 Nonprefixed
CREATE INDEX 주문_x03 ON 주문 (주문금액, 주문일자) GLOBAL
  PARTITION BY RANGE (주문금액)
  ( PARTITION p_1  VALUES LESS THAN (10000),
    PARTITION p_mx VALUES LESS THAN (MAXVALUE) );              -- 글로벌 Prefixed
CREATE INDEX 주문_x04 ON 주문 (고객id, 배송일자);              -- 비파티션

ALTER TABLE 주문 DROP PARTITION P2017_Q1;

SELECT index_name, status FROM user_indexes WHERE table_name = '주문';

SELECT index_name, partition_name, status
  FROM user_ind_partitions
 WHERE index_name = '주문_X03'
 ORDER BY partition_name;

ALTER INDEX 주문_X03 REBUILD PARTITION p_1;
ALTER INDEX 주문_X03 REBUILD PARTITION p_mx;

CREATE TABLE 거래_t NOLOGGING AS SELECT * FROM 거래 WHERE 1 = 2;

INSERT /*+ append */ INTO 거래_t
SELECT 고객번호, 거래일자, 거래순번,
       CASE WHEN 상태코드 <> 'ZZZ' THEN 'ZZZ' ELSE 상태코드 END
  FROM 거래
 WHERE 거래일자 < '20150101';
COMMIT;

CREATE UNIQUE INDEX 거래_t_pk ON 거래_t (고객번호, 거래일자, 거래순번) NOLOGGING;
CREATE INDEX 거래_t_x1 ON 거래_t (거래일자, 고객번호) NOLOGGING;
CREATE INDEX 거래_t_x2 ON 거래_t (상태코드) NOLOGGING;

ALTER TABLE 거래 EXCHANGE PARTITION p201412 WITH TABLE 거래_t
  INCLUDING INDEXES WITHOUT VALIDATION;

DROP TABLE 거래_t;

ALTER TABLE 거래 MODIFY PARTITION p201412 LOGGING;
ALTER INDEX 거래_pk MODIFY PARTITION p201412 LOGGING;
ALTER INDEX 거래_x1 MODIFY PARTITION p201412 LOGGING;
ALTER INDEX 거래_x2 MODIFY PARTITION p201412 LOGGING;

-- 튜닝 전: 행 단위 Undo/Redo, 인덱스 리프 삭제 (실습: 20만 건 중 68902건)
DELETE FROM 거래이력 WHERE 거래일자 < '20150101';
ROLLBACK;

-- 튜닝 후
ALTER TABLE 거래이력 DROP PARTITION P201412;

ALTER TABLE target_t MODIFY PARTITION P201712 NOLOGGING;
ALTER INDEX target_t_x01 MODIFY PARTITION P201712 UNUSABLE;

INSERT /*+ append */ INTO target_t
SELECT * FROM source_t WHERE 주문일자 BETWEEN '20171201' AND '20171231';
COMMIT;

ALTER INDEX target_t_x01 REBUILD PARTITION P201712 NOLOGGING;
ALTER TABLE target_t MODIFY PARTITION P201712 LOGGING;
ALTER INDEX target_t_x01 MODIFY PARTITION P201712 LOGGING;

ALTER TABLE target MODIFY CONSTRAINT target_pk DISABLE DROP INDEX;
ALTER INDEX target_x1 UNUSABLE;

INSERT /*+ append */ INTO target SELECT * FROM source;
COMMIT;

ALTER TABLE target MODIFY CONSTRAINT target_pk ENABLE NOVALIDATE;
ALTER INDEX target_x1 REBUILD ONLINE;