반응형
Oracle Partition Table SPLIT (파티션 스플릿)
#Partition Table 기본 생성
#Partition Range
CREATE TABLE PCW.ORDERS_PARTITION
(ORDER_ID NUMBER,
ORDER_DATE DATE,
ORDER_MODE VARCHAR2(10),
CUSTOMER_ID VARCHAR2(10),
EMPLOYEE_ID VARCHAR2(10),
ORDER_STATUS NUMBER,
ORDER_TOTAL NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION PCW_201701 VALUES LESS THAN(TO_DATE('20170201', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201702 VALUES LESS THAN(TO_DATE('20170301', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201703 VALUES LESS THAN(TO_DATE('20170401', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201704 VALUES LESS THAN(TO_DATE('20170501', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201705 VALUES LESS THAN(TO_DATE('20170601', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201706 VALUES LESS THAN(TO_DATE('20170701', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201707 VALUES LESS THAN(TO_DATE('20170801', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201708 VALUES LESS THAN(TO_DATE('20170901', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201709 VALUES LESS THAN(TO_DATE('20171001', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201710 VALUES LESS THAN(TO_DATE('20171101', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201711 VALUES LESS THAN(TO_DATE('20171201', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201712 VALUES LESS THAN(TO_DATE('20180101', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201801 VALUES LESS THAN(TO_DATE('20180201', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201802 VALUES LESS THAN(TO_DATE('20180301', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201803 VALUES LESS THAN(TO_DATE('20180401', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201804 VALUES LESS THAN(TO_DATE('20180501', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201805 VALUES LESS THAN(TO_DATE('20180601', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201806 VALUES LESS THAN(TO_DATE('20180701', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201807 VALUES LESS THAN(TO_DATE('20180801', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201808 VALUES LESS THAN(TO_DATE('20180901', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201809 VALUES LESS THAN(TO_DATE('20181001', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201810 VALUES LESS THAN(TO_DATE('20181101', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201811 VALUES LESS THAN(TO_DATE('20181201', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201812 VALUES LESS THAN(TO_DATE('20190101', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201901 VALUES LESS THAN(TO_DATE('20190201', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201902 VALUES LESS THAN(TO_DATE('20190301', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201903 VALUES LESS THAN(TO_DATE('20190401', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201904 VALUES LESS THAN(TO_DATE('20190501', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201905 VALUES LESS THAN(TO_DATE('20190601', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201906 VALUES LESS THAN(TO_DATE('20190701', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201907 VALUES LESS THAN(TO_DATE('20190801', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201908 VALUES LESS THAN(TO_DATE('20190901', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201909 VALUES LESS THAN(TO_DATE('20191001', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201910 VALUES LESS THAN(TO_DATE('20191101', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201911 VALUES LESS THAN(TO_DATE('20191201', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_201912 VALUES LESS THAN(TO_DATE('20200101', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202001 VALUES LESS THAN(TO_DATE('20200201', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202002 VALUES LESS THAN(TO_DATE('20200301', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202003 VALUES LESS THAN(TO_DATE('20200401', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202004 VALUES LESS THAN(TO_DATE('20200501', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202005 VALUES LESS THAN(TO_DATE('20200601', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202006 VALUES LESS THAN(TO_DATE('20200701', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202007 VALUES LESS THAN(TO_DATE('20200801', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202008 VALUES LESS THAN(TO_DATE('20200901', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202009 VALUES LESS THAN(TO_DATE('20201001', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202010 VALUES LESS THAN(TO_DATE('20201101', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202011 VALUES LESS THAN(TO_DATE('20201201', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202012 VALUES LESS THAN(TO_DATE('20210101', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION MAXVALUE VALUES LESS THAN(MAXVALUE) TABLESPACE USERS_DATA
);
#Partition Table 제약 조건 및 INDEX 생성
-- 시퀀스 생성
CREATE SEQUENCE pcw.ORDERS_PT_SEC
START WITH 8000
INCREMENT BY 1
CACHE 20;
-- 로컬 유니크 인덱스 생성
CREATE UNIQUE INDEX PCW.ORDERS_PARTITION
ON PCW.ORDERS_PARTITION(ORDER_ID,ORDER_DATE)
TABLESPACE USERS_DATA LOCAL;
-- 로컬 PK 생성
ALTER TABLE PCW.ORDERS_PARTITION
ADD CONSTRAINT PX_ORDERS_PARTITION PRIMARY KEY(ORDER_ID,ORDER_DATE);
-- 파티션 유니크 인덱스 조회
SELECT * FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER='PCW';
SELECT * FROM DBA_INDEXES
WHERE OWNER='PCW';
-- 논 유니크 인덱스 로컬 생성
CREATE INDEX PCW.IX_ORDERS_PARTITION_01
ON PCW.ORDERS_PARTITION(EMPLOYEE_ID, ORDER_DATE)
TABLESPACE USERS_DATA LOCAL;
CREATE INDEX PCW.IX_ORDERS_PARTITION_02
ON PCW.ORDERS_PARTITION(CUSTOMER_ID, ORDER_DATE)
TABLESPACE USERS_DATA LOCAL;
-- 글로벌 인덱스 생성
CREATE INDEX PCW.IX_ORDERS_PARTITION_03
ON PCW.ORDERS_PARTITION(CUSTOMER_ID, ORDER_DATE,ORDER_TOTAL)
TABLESPACE USERS_DATA ;
-- 파티션 유니크 인덱스 조회
SELECT * FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER='PCW';
SELECT * FROM DBA_INDEXES
WHERE OWNER='PCW';
OWNER INDEX_NAME INDEX_TYPE UNIQUENESS TABLESPACE_NAME STATUS
---------- ----------------------- --------------------------- ---------- ------------------------------
PCW ORDERS_PARTITION NORMAL UNIQUE N/A
PCW IX_ORDERS_PARTITION_01 NORMAL NONUNIQUE N/A
PCW IX_ORDERS_PARTITION_02 NORMAL NONUNIQUE N/A
PCW IX_ORDERS_PARTITION_03 NORMAL NONUNIQUE USERS_DATA VALID
#DML 발생
begin
for i in 1 .. 100000
loop
insert into PCW.ORDERS_PARTITION values
(
pcw.ORDERS_PT_SEC.NEXTVAL, -- ORDER_ID NUMBER
(with case_d as (
select trunc(DBMS_RANDOM.value(1,11)) as var from dual
)
select
case
when cd.var = 1 then
TO_DATE('20210'|| trunc(DBMS_RANDOM.value(1,10)) || trunc(DBMS_RANDOM.value(10,29)),'YYYYMMDD HH24MISS')
when cd.var = 2 then
TO_DATE('2021'|| trunc(DBMS_RANDOM.value(10,13)) || trunc(DBMS_RANDOM.value(10,29)),'YYYYMMDD HH24MISS')
when cd.var = 3 then
TO_DATE('20200'|| trunc(DBMS_RANDOM.value(1,10)) || trunc(DBMS_RANDOM.value(10,29)),'YYYYMMDD HH24MISS')
when cd.var = 4 then
TO_DATE('2020'|| trunc(DBMS_RANDOM.value(10,13)) || trunc(DBMS_RANDOM.value(10,29)),'YYYYMMDD HH24MISS')
when cd.var = 5 then
TO_DATE('20190'|| trunc(DBMS_RANDOM.value(1,10)) || trunc(DBMS_RANDOM.value(10,29)),'YYYYMMDD HH24MISS')
when cd.var = 6 then
TO_DATE('2019'|| trunc(DBMS_RANDOM.value(10,13)) || trunc(DBMS_RANDOM.value(10,29)),'YYYYMMDD HH24MISS')
when cd.var = 7 then
TO_DATE('20180'|| trunc(DBMS_RANDOM.value(1,10)) || trunc(DBMS_RANDOM.value(10,29)),'YYYYMMDD HH24MISS')
when cd.var = 8 then
TO_DATE('2018'|| trunc(DBMS_RANDOM.value(10,13)) || trunc(DBMS_RANDOM.value(10,29)),'YYYYMMDD HH24MISS')
when cd.var = 9 then
TO_DATE('20170'|| trunc(DBMS_RANDOM.value(1,10)) || trunc(DBMS_RANDOM.value(10,29)),'YYYYMMDD HH24MISS')
when cd.var = 10 then
TO_DATE('2017'|| trunc(DBMS_RANDOM.value(10,13)) || trunc(DBMS_RANDOM.value(10,29)),'YYYYMMDD HH24MISS')
end
from case_d CD),
'test_'||dbms_random.string('P', 1), -- ORDER_MODE VARCHAR2(10)
'test_'||dbms_random.string('C', 1), -- CUSTOMER_ID VARCHAR2(10)
'test_'||dbms_random.string('W', 1), -- EMPLOYEE_ID VARCHAR2(10)
rpad(ABS(dbms_random.random),2), -- ORDER_STATUS NUMBER
rpad(ABS(dbms_random.random),1) -- ORDER_TOTAL NUMBER)
);
end loop;
commit;
end;
/
#Partition Table Split
#PARTITION(MAXVALUE) 값 형식 확인하기
SELECT ORDER_DATE , count(*) FROM PCW.ORDERS_PARTITION PARTITION(MAXVALUE)
GROUP BY ORDER_DATE;
RDER_ID ORDER_DATE ORDER_MODE CUSTOMER_ID EMPLOYEE_ID ORDER_STATUS ORDER_TOTAL
--------- -------------------- ---------- ----------- ----------- ------------ -----------
1781859 2021/11/20 00:00:00 test_T test_P test_I 12 7
1781860 2021/03/26 00:00:00 test_g test_P test_F 12 1
1781861 2021/05/11 00:00:00 test_] test_W test_K 74 1
1781862 2021/12/18 00:00:00 test_M test_G test_R 16 1
1781863 2021/05/11 00:00:00 test_H test_R test_K 67 1
1781864 2021/04/16 00:00:00 test_z test_Z test_A 12 1
1781865 2021/05/19 00:00:00 test_9 test_T test_G 26 5
1781866 2021/09/10 00:00:00 test_p test_A test_R 56 1
-- 2021/MM/DD 년 DATE 데이터형식에 파티셔닝 스플릿 진행합니다.#생성될 파티션
PARTITION PCW_202101 VALUES LESS THAN(TO_DATE('20210201', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202102 VALUES LESS THAN(TO_DATE('20210301', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202103 VALUES LESS THAN(TO_DATE('20210401', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202104 VALUES LESS THAN(TO_DATE('20210501', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202105 VALUES LESS THAN(TO_DATE('20210601', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202106 VALUES LESS THAN(TO_DATE('20210701', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202107 VALUES LESS THAN(TO_DATE('20210801', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202108 VALUES LESS THAN(TO_DATE('20210901', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202109 VALUES LESS THAN(TO_DATE('20211001', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202110 VALUES LESS THAN(TO_DATE('20211101', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202111 VALUES LESS THAN(TO_DATE('20211201', 'YYYYMMDD'))TABLESPACE USERS_DATA
PARTITION PCW_202112 VALUES LESS THAN(TO_DATE('20220101', 'YYYYMMDD'))TABLESPACE USERS_DATA#파티셔닝 스플릿 수행
ALTER TABLE PCW.ORDERS_PARTITION
SPLIT PARTITION MAXVALUE
INTO
(
PARTITION PCW_202101 VALUES LESS THAN(TO_DATE('20210201', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202102 VALUES LESS THAN(TO_DATE('20210301', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202103 VALUES LESS THAN(TO_DATE('20210401', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202104 VALUES LESS THAN(TO_DATE('20210501', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202105 VALUES LESS THAN(TO_DATE('20210601', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202106 VALUES LESS THAN(TO_DATE('20210701', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202107 VALUES LESS THAN(TO_DATE('20210801', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202108 VALUES LESS THAN(TO_DATE('20210901', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202109 VALUES LESS THAN(TO_DATE('20211001', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202110 VALUES LESS THAN(TO_DATE('20211101', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202111 VALUES LESS THAN(TO_DATE('20211201', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION PCW_202112 VALUES LESS THAN(TO_DATE('20220101', 'YYYYMMDD'))TABLESPACE USERS_DATA,
PARTITION MAXVALUE
)
UPDATE GLOBAL INDEXES ; -- 인덱스 리빌드 작업과 동일(GLOBAL INDEX 재생성)
#정리
| PARTITION SPLIT 완료후 해당 테이블의 인덱스를 확인합니다. GLOBAL INDEX 의 경우 UNUSEBLE 상태로 변경되어 인덱스 리빌드 작업을 수행해야 합니다. MAXVALUE 에 존재하는 데이터에 대해 PARTITION SPLIT 진행중 해당 데이터 파티셔닝 테이블에 대해서 DML 작업을 할 수 없습니다. 데이터 정의 언어(DDL)는 데이터베이스 스키마를 정의하는 데 사용되고, 데이터 처리 언어(DML)는 데이터베이스의 테이블을 수정하는 데 사용됩니다. 데이터 무결성을 유지하기 위해 데이터베이스는 해당 테이블 내에서 테이블 또는 행을 갱신하거나 읽기 전에 먼저 잠급니다(읽기, 쓰기, 배타적 등과 같은 다양한 모드). DDL 및 DML이 테이블 내 모든 행에 영향을 미치므로 배타적 잠금이 필요하며 해당 테이블 내에 있는 행에 잠금이 있으면 실패가 발생합니다. Oracle에서 DDL 및 DML이 잠금을 발견하는 경우 다음과 같은 오류가 발생합니다. ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired |
반응형
'Oracle > Oracle Admin' 카테고리의 다른 글
| Oracle Partition TABLE 기본 구성 (0) | 2022.02.16 |
|---|---|
| Oracle Controlfile 재 생성 오라클 구성 변경 (0) | 2021.12.20 |
| Oracle Partition Table Shrink 및 Lock 확인 테스트 (0) | 2021.12.09 |
| Oracle Partition Table Fragmentation(단편화 비교) (0) | 2021.11.22 |
| Oracle Partition Table Index 기본 구성기준 및 SQL 실행계획 (0) | 2021.11.17 |
댓글