야동우`s Story

-- Range Partition으로 생성된 sales 테이블에 Local Prefixed Index를 생성한 예제
CREATE INDEX sales_idx01
ON sales(sale_year, sale_month, sale_day) LOCAL
(PARTITION idx_sales_p1 TABLESPACE IDX1,
 PARTITION idx_sales_p2 TABLESPACE IDX2,
 PARTITION idx_sales_p3 TABLESPACE IDX3,
 PARTITION idx_sales_p4 TABLESPACE IDX4 )
 

CREATE INDEX sales_idx02
ON sales(sales_no, sale_year) LOCAL
(PARTITION idx_sales_p1 TABLESPACE IDX1,
 PARTITION idx_sales_p2 TABLESPACE IDX2,
 PARTITION idx_sales_p3 TABLESPACE IDX3,
 PARTITION idx_sales_p4 TABLESPACE IDX4 ) ;

-- Range Partition으로 생성된 sales 테이블 sale_year 컬럼에 Global prefixed Index로 생성 예제
CREATE INDEX sales_idx03 ON sales(sale_year)
GLOBAL PARTITION BY RANGE (sale_year)
(PARTITION idx_sales_year_p1 VALUES LESS THAN (2005) TABLESPACE IDX1,
 PARTITION idx_sales_year_p2 VALUES LESS THAN (2010) TABLESPACE IDX2,
 PARTITION idx_sales_year_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE IDX3);

-- Range Partition으로 생성된 sales 테이블 sale_month 컬럼에 Global prefixed Index로 생성 예제
CREATE INDEX sales_idx04 ON sales(sale_month)
GLOBAL PARTITION BY RANGE (sale_month)
(PARTITION idx_sales_month_p1 VALUES LESS THAN (04) TABLESPACE IDX1,
 PARTITION idx_sales_month_p2 VALUES LESS THAN (07) TABLESPACE IDX2,
 PARTITION idx_sales_month_p3 VALUES LESS THAN (10) TABLESPACE IDX3,
 PARTITION idx_sales_month_p4 VALUES LESS THAN (MAXVALUE) TABLESPACE IDX4);

CREATE TABLE emp_range_list
       (EMPNO NUMBER NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2))
PARTITION BY RANGE (deptno)
SUBPARTITION BY LIST (job)
(PARTITION emp_p1 VALUES LESS THAN (11)
  (SUBPARTITION sub1_job_p1 VALUES (’MANAGER’) TABLESPACE ASSM_TBS1,
   SUBPARTITION sub1_job_p2 VALUES (’SALESMAN’) TABLESPACE ASSM_TBS2,
   SUBPARTITION sub1_job_p3 VALUES (’ANALYST’) TABLESPACE ASSM_TBS3,
   SUBPARTITION sub1_job_p4 VALUES (’PRESIDENT’, ’CLERK’) TABLESPACE ASSM_TBS4),
 PARTITION emp_p2 VALUES LESS THAN (21)
  (SUBPARTITION sub2_job_p1 VALUES (’MANAGER’) TABLESPACE ASSM_TBS1,
   SUBPARTITION sub2_job_p2 VALUES (’SALESMAN’) TABLESPACE ASSM_TBS2,
   SUBPARTITION sub2_job_p3 VALUES (’ANALYST’) TABLESPACE ASSM_TBS3,
   SUBPARTITION sub2_job_p4 VALUES (’PRESIDENT’, ’CLERK’) TABLESPACE ASSM_TBS4),
 PARTITION emp_p3 VALUES LESS THAN (31)
  (SUBPARTITION sub3_job_p1 VALUES (’MANAGER’) TABLESPACE ASSM_TBS1,
   SUBPARTITION sub3_job_p2 VALUES (’SALESMAN’) TABLESPACE ASSM_TBS2,
   SUBPARTITION sub3_job_p3 VALUES (’ANALYST’) TABLESPACE ASSM_TBS3,
   SUBPARTITION sub3_job_p4 VALUES (’PRESIDENT’, ’CLERK’) TABLESPACE ASSM_TBS4) ,
 PARTITION emp_p4 VALUES LESS THAN (41)
  (SUBPARTITION sub4_job_p1 VALUES (’MANAGER’) TABLESPACE ASSM_TBS1,
   SUBPARTITION sub4_job_p2 VALUES (’SALESMAN’) TABLESPACE ASSM_TBS2,
   SUBPARTITION sub4_job_p3 VALUES (’ANALYST’) TABLESPACE ASSM_TBS3,
   SUBPARTITION sub4_job_p4 VALUES (’PRESIDENT’, ’CLERK’) TABLESPACE ASSM_TBS4)   
);
  

ALTER TABLE emp_range_list
   ADD PARTITION emp_p5 VALUES LESS THAN (51)
  (SUBPARTITION sub5_job_p1 VALUES (’MANAGER’) TABLESPACE ASSM_TBS1,
   SUBPARTITION sub5_job_p2 VALUES (’SALESMAN’) TABLESPACE ASSM_TBS2,
   SUBPARTITION sub5_job_p3 VALUES (’ANALYST’) TABLESPACE ASSM_TBS3,
   SUBPARTITION sub5_job_p4 VALUES (’PRESIDENT’, ’CLERK’) TABLESPACE ASSM_TBS4)

CREATE TABLE sales_range_hash
    (sales_no NUMBER,
     sale_year INT NOT NULL,
     sale_month INT NOT NULL,
     sale_day INT NOT NULL,
     customer_name  VARCHAR2(30),
     price NUMBER)
       PARTITION BY RANGE (sale_year, sale_month, sale_day)
       SUBPARTITION BY HASH(sales_no)
       SUBPARTITIONS 4
       STORE IN (ASSM_TBS1, ASSM_TBS2, ASSM_TBS3, ASSM_TBS4)
       (PARTITION sales_q1 VALUES LESS THAN (2005, 01, 01),
        PARTITION sales_q2 VALUES LESS THAN (2005, 07, 01),
        PARTITION sales_q3 VALUES LESS THAN (2006, 01, 01),
        PARTITION sales_q4 VALUES LESS THAN (2006, 07, 01) );
 

 CREATE TABLE emp_list_pt
       (EMPNO NUMBER NOT NULL,
       ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2))
 PARTITION BY LIST (JOB)
 (PARTITION emp_list_pt1 VALUES (’MANAGER’) TABLESPACE ASSM_TBS1,
  PARTITION emp_list_pt2 VALUES (’SALESMAN’) TABLESPACE ASSM_TBS2,
  PARTITION emp_list_pt3 VALUES (’ANALYST’) TABLESPACE ASSM_TBS3,
  PARTITION emp_list_pt4 VALUES (’PRESIDENT’, ’CLERK’) TABLESPACE ASSM_TBS4);
 

INSERT INTO emp_list_pt VALUES(1, ’SMITH’,  ’CLERK’,     7902, SYSDATE,  800, NULL, 20);
INSERT INTO emp_list_pt VALUES(2, ’ALLEN’,  ’SALESMAN’,  7698, SYSDATE, 1600,  300, 30);
INSERT INTO emp_list_pt VALUES(3, ’WARD’,   ’SALESMAN’,  7698, SYSDATE, 1250,  500, 30);
INSERT INTO emp_list_pt VALUES(4, ’JONES’,  ’MANAGER’,   7839, SYSDATE,  2975, NULL, 20);
INSERT INTO emp_list_pt VALUES(5, ’MARTIN’, ’SALESMAN’,  7698, SYSDATE, 1250, 1400, 30);
INSERT INTO emp_list_pt VALUES(6, ’BLAKE’,  ’MANAGER’,   7839, SYSDATE,  2850, NULL, 30);
INSERT INTO emp_list_pt VALUES(7, ’CLARK’,  ’MANAGER’,   7839, SYSDATE,  2450, NULL, 10);
INSERT INTO emp_list_pt VALUES(8, ’SCOTT’,  ’ANALYST’,   7566, SYSDATE, 3000, NULL, 20);
INSERT INTO emp_list_pt VALUES(9, ’KING’,   ’PRESIDENT’, NULL, SYSDATE, 5000, NULL, 10);
INSERT INTO emp_list_pt VALUES(10, ’TURNER’, ’SALESMAN’,  7698,SYSDATE,  1500,    0, 30);
INSERT INTO emp_list_pt VALUES(11, ’ADAMS’, ’CLERK’, 7788,SYSDATE,1100,NULL,20);
INSERT INTO emp_list_pt VALUES(12, ’JAMES’,  ’CLERK’,     7698, SYSDATE,   950, NULL, 30);
INSERT INTO emp_list_pt VALUES(13, ’FORD’,   ’ANALYST’,   7566, SYSDATE,  3000, NULL, 20);
INSERT INTO emp_list_pt VALUES(14, ’MILLER’, ’CLERK’,     7782,  SYSDATE, 1300, NULL, 10); 
COMMIT;

SELECT empno FROM emp_list_pt PARTITION (emp_list_pt1); --> 4,6,7
SELECT empno FROM emp_list_pt PARTITION (emp_list_pt2); --> 2,3,5,10
SELECT empno FROM emp_list_pt PARTITION (emp_list_pt3); --> 8,13
SELECT empno FROM emp_list_pt PARTITION (emp_list_pt4); --> 1,9,11,12,14
 

 ALTER TABLE emp_list_pt
 ADD PARTITION emp_list_pt5 VALUES (’DBA’) TABLESPACE ASSM_TBS5  NOLOGGING;
 

 ALTER TABLE emp_list_pt DROP PARTITION emp_list_pt5
 

 -- 생성 예제 1
 CREATE TABLE sales_hash_exam
    (sales_no NUMBER,
     sale_year CHAR(4) NOT NULL,
     sale_month CHAR(2) NOT NULL,
     sale_day CHAR(2) NOT NULL,
     customer_name  VARCHAR2(30),
     price NUMBER)
 PARTITION BY HASH (sales_no)
 PARTITIONS 4   --> 2 의 거듭제곱수로 지정
 STORE IN (ASSM_TBS1, ASSM_TBS2, ASSM_TBS3, ASSM_TBS4);
 

-- 생성 예제 2
-- sales_hash1, sales_hash2..와 같이 파티션을 직접 지정 할 수 있다.
-- 하지만 각 파티션에  어떤 데이터가 지정될지 모르므로 무의미 할 수 있다.
 CREATE TABLE sales_hash
    (sales_no NUMBER,
     sale_year CHAR(4) NOT NULL,
     sale_month CHAR(2) NOT NULL,
     sale_day CHAR(2) NOT NULL,
     customer_name  VARCHAR2(30),
     price NUMBER)
 PARTITION BY HASH (sales_no) 
  ( PARTITION sales_hash1 TABLESPACE ASSM_TBS1,
    PARTITION sales_hash2 TABLESPACE ASSM_TBS2,
    PARTITION sales_hash3 TABLESPACE ASSM_TBS3,
    PARTITION sales_hash4 TABLESPACE ASSM_TBS4);
 

INSERT INTO sales_hash VALUES(1, 2004, 06, 12, ’scott’, 2500);
INSERT INTO sales_hash VALUES(2, 2005, 06, 17, ’jones’, 4300);
INSERT INTO sales_hash VALUES(3, 2005, 12, 12, ’miller’, 1200);
INSERT INTO sales_hash VALUES(4, 2006, 06, 22, ’ford’, 5200);
INSERT INTO sales_hash VALUES(5, 2005, 01, 01, ’lion’, 2200);
INSERT INTO sales_hash VALUES(6, 2006, 12, 22, ’tiger’, 3300);
COMMIT;
 

SELECT sales_no FROM sales_hash PARTITION (sales_hash1); --> 6
SELECT sales_no FROM sales_hash PARTITION (sales_hash2); --> 레코드 없음
SELECT sales_no FROM sales_hash PARTITION (sales_hash3); --> 2,5
SELECT sales_no FROM sales_hash PARTITION (sales_hash4); --> 1,3,4
 

I
NSERT INTO sales VALUES(1, 2004, 06, 12, ’scott’, 2500);
INSERT INTO sales VALUES(2, 2005, 06, 17, ’jones’, 4300);
INSERT INTO sales VALUES(3, 2005, 12, 12, ’miller’, 1200);
INSERT INTO sales VALUES(4, 2006, 06, 22, ’ford’, 5200);
INSERT INTO sales VALUES(5, 2005, 01, 01, ’lion’, 2200); --> sales_q2 에 데이터가 INSERT 됨
INSERT INTO sales VALUES(6, 2006, 12, 22, ’tiger’, 3300);
COMMIT;

INSERT INTO sales VALUES(6, 2006, 12, 22, ’tiger’, 3300); --> 범위 초과로 ORA-14400 에러 발생
1행에 오류:
ORA-14400: 삽입된 분할 영역 키와 매핑되는 분할 영역이 없음