Oracle Partition TABLE 기본 구성

#Oracle Partition Table 기본구성

#테이블 스페이스 생성
CREATE TABLESPACE TEST DATAFILE '+ASM' SIZE 1G AUTOEXTEND ON;
 
#유저생성
CREATE USER PCW IDENTIFIED BY 'oracle1' DEFAULT TABLESPACE  TABLESPACE  TEMP;
 
#권한부여
ALTER USER PCW DEFAULT TABLESPACE TEST QUOTA UNLIMITED ON TEST;
GRANT CREATE SESSION TO PCW;
GRANT CONNECT , RESOURCE TO PCW;


#RANGE PARTITION TABLE 생성

CREATE TABLE PCW.ORDERS_PT
(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_202101 VALUES LESS THAN(TO_DATE('20210201', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202102 VALUES LESS THAN(TO_DATE('20210301', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202103 VALUES LESS THAN(TO_DATE('20210401', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202104 VALUES LESS THAN(TO_DATE('20210501', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202105 VALUES LESS THAN(TO_DATE('20210601', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202106 VALUES LESS THAN(TO_DATE('20210701', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202107 VALUES LESS THAN(TO_DATE('20210801', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202108 VALUES LESS THAN(TO_DATE('20210901', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202109 VALUES LESS THAN(TO_DATE('20211001', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202110 VALUES LESS THAN(TO_DATE('20211101', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202111 VALUES LESS THAN(TO_DATE('20211201', 'YYYYMMDD'))TABLESPACE TEST,
 PARTITION PCW_202112 VALUES LESS THAN(TO_DATE('20220101', 'YYYYMMDD'))TABLESPACE TEST
 );
 
 
#시퀀스 오브젝트 생성

CREATE SEQUENCE  pcw.ORDERS_PT_SEC
 START WITH  8000
INCREMENT BY 1
 CACHE 20;
 
 
 
#데이터 입력
begin
for i in 1 .. 100000
loop
insert into PCW.ORDERS_PT values
(
 pcw.ORDERS_PT_SEC.NEXTVAL,                       -- ORDER_ID       NUMBER
(with case_d as (
select trunc(DBMS_RANDOM.value(1,3)) 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')  
    end
from case_d CD), -- ORDER_DATE     DATE
'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;
/ 

# 로컬 인덱스 오브젝트 생성 
CREATE UNIQUE INDEX PCW.PX_ORDERS_PT
ON PCW.ORDERS_PT(ORDER_ID,ORDER_DATE)
TABLESPACE TEST LOCAL;

# PK 로컬인덱스 생성
ALTER TABLE PCW.ORDERS_PT
ADD CONSTRAINT PX_ORDERS_PT PRIMARY KEY(ORDER_ID,ORDER_DATE) TABLESPACE TEST

#파티션 유니크 인덱스 조회
SELECT * FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER='PCW';

#인덱스 오브젝트 조회
SELECT * FROM DBA_INDEXES
WHERE OWNER='PCW';

#논 유니크 인덱스 로컬 생성
CREATE INDEX PCW.IX_ORDERS_PT_01
ON PCW.ORDERS_PT(EMPLOYEE_ID, ORDER_DATE)
TABLESPACE TEST LOCAL;
 
CREATE INDEX PCW.IX_ORDERS_PT_02
ON PCW.ORDERS_PT(CUSTOMER_ID, ORDER_DATE)
TABLESPACE TEST LOCAL;

#글로벌 인덱스 생성
CREATE INDEX PCW.IX_ORDERS_PT_03
ON PCW.ORDERS_PT(CUSTOMER_ID, ORDER_DATE,ORDER_TOTAL)
TABLESPACE TEST ;