[Tibero] Partition table

Partition table TEST

-테스트 유저 생성
create user test identified by 'tibero' default tablespace test0;
grant dba, connect, resource to test;

CREATE TABLESPACE test01 DATAFILE '/dev/tibero_vg/rhd512_01' SIZE 100M AUTOEXTEND OFF;
CREATE TABLESPACE test02 DATAFILE '/dev/tibero_vg/rhd512_02' SIZE 100M AUTOEXTEND OFF;
CREATE TABLESPACE test03 DATAFILE '/dev/tibero_vg/rhd512_03' SIZE 100M AUTOEXTEND OFF;
CREATE TABLESPACE test04 DATAFILE '/dev/tibero_vg/rhd512_04' SIZE 100M AUTOEXTEND OFF;
CREATE TABLESPACE test05 DATAFILE '/dev/tibero_vg/rhd512_05' SIZE 100M AUTOEXTEND OFF;




CREATE TABLESPACE test00 DATAFILE 'test00.dtf' SIZE 30M AUTOEXTEND OFF;
CREATE TABLESPACE test11 DATAFILE 'test11.dft' SIZE 30M AUTOEXTEND OFF;
CREATE TABLESPACE test22 DATAFILE 'test22.dft' SIZE 30M AUTOEXTEND OFF;
CREATE TABLESPACE test33 DATAFILE 'test33.dft' SIZE 30M AUTOEXTEND OFF;
CREATE TABLESPACE test44 DATAFILE 'test44.dft' SIZE 30M AUTOEXTEND OFF;

-테스트 테이블 생성

CREATE TABLE sales
        (sales_no NUMBER,
         sale_year INT NOT NULL,
         sale_month INT NOT NULL,
         sale_day INT NOT NULL,
         customer_name  VARCHAR2(30),
         price NUMBER)
		 TABLESPACE test0
       PARTITION BY RANGE (sale_year, sale_month, sale_day)
       (PARTITION sales_q1 VALUES LESS THAN (2005, 01, 01) TABLESPACE test1,
        PARTITION sales_q2 VALUES LESS THAN (2005, 07, 01) TABLESPACE test2,
        PARTITION sales_q3 VALUES LESS THAN (2006, 01, 01) TABLESPACE test3,
        PARTITION sales_q4 VALUES LESS THAN (2006, 07, 01) TABLESPACE test4 );
-테이터 삽입

INSERT 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)
/
commit

SQL> INSERT INTO sales VALUES(6, 2006, 12, 22, 'tiger', 3300);
TBR-10018: Partition key does not map to a partition.   


SELECT sales_no FROM sales PARTITION (sales_q1); --> 1
SELECT sales_no FROM sales PARTITION (sales_q2); --> 2, 5
SELECT sales_no FROM sales PARTITION (sales_q3); --> 3
SELECT sales_no FROM sales PARTITION (sales_q4); --> 4


-- partition table move

ALTER TABLE test.sales MOVE PARTITION sales_q1 TABLESPACE test11;
ALTER TABLE test.sales MOVE PARTITION sales_q2 TABLESPACE test22;
ALTER TABLE test.sales MOVE PARTITION sales_q3 TABLESPACE test33;
ALTER TABLE test.sales MOVE PARTITION sales_q4 TABLESPACE test44;  --> 이것만으로 table이 다 옮겨 진건지?

SELECT sales_no FROM sales PARTITION (sales_q1); --> 1
SELECT sales_no FROM sales PARTITION (sales_q2); --> 2, 5
SELECT sales_no FROM sales PARTITION (sales_q3); --> 3
SELECT sales_no FROM sales PARTITION (sales_q4); --> 4

--
ALTER TABLE test.sales MOVE tablespace test00;
TBR-7002: Unsupported DDL. 

-- partition 추가

ALTER TABLE sales ADD PARTITION sales_q5 VALUES LESS THAN (2007, 01, 20);
--> test0(default tablespace)에 적재된다 

-- default tablespace에 적제된 데이터도 옮겨주고
ALTER TABLE test.sales MOVE PARTITION sales_q5 TABLESPACE test00;

-- partition 삭제

ALTER TABLE sales DROP PARTITION sales_q5;

######################

##Default tablespace 변경

ALTER TABLE test.sales MODIFY DEFAULT ATTRIBUTES TABLESPACE test00;
alter tablespace test0 offline; -- default tablespace 
select * from test.sales; -- 조회 성공 

alter tablespace test0 online;
ALTER TABLE test.sales MODIFY DEFAULT ATTRIBUTES TABLESPACE test0;
alter tablespace test0 offline;

 

Patition table data 옮기기

######. 유저생성
drop user TS_TEST_USER cascade;
create user ts_test_user identified by 'ts_test_user' default tablespace TEST1;
grant dba to ts_test_user;

######. 특정 TS 사용하는 object 조회 쿼리
SELECT SEGMENT_NAME,SEGMENT_TYPE,OWNER, TABLESPACE_NAME, BYTES/1024/1024 MB  FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='TEST1';

######. USER 의 기본 TS 확인
SELECT  * FROM DBA_USERS;

######. OBJECT TS 조회 쿼리
SELECT * FROM DBA_TABLES WHERE OWNER='TS_TEST_USER';
SELECT * FROM DBA_INDEXES WHERE OWNER='TS_TEST_USER';

SELECT * FROM DBA_TAB_PARTITIONS WHERE OWNER='TS_TEST_USER';
SELECT * FROM DBA_PART_TABLES  WHERE OWNER='TS_TEST_USER';

SELECT * FROM DBA_IDX_PARTITIONS WHERE OWNER='TS_TEST_USER';
SELECT * FROM DBA_PART_INDEXES  WHERE OWNER='TS_TEST_USER';


######. 일반 테이블 및 인덱스
CREATE TABLE TS_TEST_USER.TEST_TAB(V1 INT);
INSERT INTO TS_TEST_USER.TEST_TAB SELECT LEVEL FROM DUAL CONNECT BY LEVEL <100;
CREATE INDEX TS_TEST_USER.TEST_IDX ON TS_TEST_USER.TEST_TAB(V1);

# 유저 TS 변경
ALTER USER TS_TEST_USER DEFAULT TABLESPACE TEST_TS001;
ALTER USER TS_TEST_USER DEFAULT TABLESPACE TEST1;

# TABLE TS 변경
ALTER TABLE TS_TEST_USER.TEST_TAB MOVE TABLESPACE TEST_TS001;

######. INDEX  TS 변경
ALTER INDEX TS_TEST_USER.TEST_IDX REBUILD TABLESPACE TEST_TS001;


######. 파티션 테이블 및 인덱스
CREATE TABLE TS_TEST_USER.TEST_TAB_PART (A NUMBER, B NUMBER)
TABLESPACE TEST1
PARTITION BY RANGE(A)
(
PARTITION P1 VALUES LESS THAN (10) TABLESPACE TEST1,
PARTITION P2 VALUES LESS THAN (20) TABLESPACE TEST1
);

INSERT INTO TS_TEST_USER.TEST_TAB_PART SELECT LEVEL,LEVEL FROM DUAL CONNECT BY LEVEL <20;

CREATE INDEX TS_TEST_USER.TEST_IDX_PART on TS_TEST_USER.TEST_TAB_PART(A) local;

# TABLE TS 변경
ALTER TABLE TS_TEST_USER.TEST_TAB_PART MOVE PARTITION P1 TABLESPACE TEST_TS001;
ALTER TABLE TS_TEST_USER.TEST_TAB_PART MOVE PARTITION P2 TABLESPACE TEST_TS001;
ALTER TABLE TS_TEST_USER.TEST_TAB_PART MODIFY DEFAULT ATTRIBUTES TABLESPACE TEST_TS001;


######. INDEX  TS 변경
ALTER INDEX TS_TEST_USER.TEST_IDX_PART REBUILD PARTITION P1 TABLESPACE TEST_TS001;
ALTER INDEX TS_TEST_USER.TEST_IDX_PART REBUILD PARTITION P2 TABLESPACE TEST_TS001;

# index default TS 변경
alter index TS_TEST_USER.TEST_IDX_PART modify default attributes tablespace TEST_TS001;



######. clob 테이블 move
alter table T1 move lob(c2) store as (tablespace TBSLOB);
alter table T1 move lob(c2,c1,c3) store as (tablespace TBSLOB);


######. clob 테이블 move
alter table T1 move lob(c2) store as (tablespace TBSLOB);