테이블 관련 쿼리 정리

## PARTITION TABLE

- Range

-- CREATE TABLE
CREATE TABLE DAMIN.sales
        (sales_no 	NUMBER(2)	NOT NULL,
         sales_date 	DATE		NOT NULL,
 	 sales_region	CHAR(2),
         customer_id    NUMBER(5),
         price 		NUMBER(8))
PARTITION BY RANGE (sales_date)
       (PARTITION sales_2013 VALUES LESS THAN (to_date('01-01-2014','dd-mm-yyyy')) TABLESPACE DAMIN_DATA_TS,
       PARTITION sales_2014 VALUES LESS THAN (to_date('01-01-2015','dd-mm-yyyy')) TABLESPACE DAMIN3_DATA_TS);
       
       
       

-- 테이블 정보 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('DAMIN', 'SALES');

-- 테이블 파티션 정보 조회
SELECT PARTITION_NAME,TABLESPACE_NAME, NUM_ROWS
FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='SALES' AND TABLE_OWNER='DAMIN'
ORDER BY TABLE_NAME, PARTITION_NAME;

-- 파티션별 조회
SELECT * FROM DAMIN.SALES PARTITION(SALES_2013) ORDER BY SALES_NO;

-- INSERT시
INSERT INTO DAMIN.SALES PARTITION(SALES_2014) VALUES (30,'17-10-2014','NY',8226,21);
INSERT INTO DAMIN.SALES PARTITION(SALES_2013) VALUES (30,'17-10-2013','NY',8226,21);

-- 파티션 테이블 추가
ALTER TABLE DAMIN.SALES ADD PARTITION SALES_2015 VALUES LESS THAN(TO_DATE('01-01-2016','DD-MM-YYYY')) TABLESPACE DAMIN_DATA_TS;

-- 통계정보 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('DAMIN', 'SALES');
SELECT PARTITION_NAME,TABLESPACE_NAME, NUM_ROWS
FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='SALES' AND TABLE_OWNER='DAMIN'
ORDER BY TABLE_NAME, PARTITION_NAME;

-- DROP
ALTER TABLE DAMIN.SALES DROP PARTITION SALES_2015;

-- 다시 통계정보 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('DAMIN', 'SALES');
SELECT PARTITION_NAME,TABLESPACE_NAME, NUM_ROWS
FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='SALES' AND TABLE_OWNER='DAMIN'
ORDER BY TABLE_NAME, PARTITION_NAME;


-- PARTITION NAME RENAME
ALTER TABLE DAMIN.SALES RENAME PARTITION SALES_2014 TO SALES_2014_LAST;

-- TABLESPACE 변경
ALTER TABLE DAMIN.SALES MOVE PARTITION SALES_2014 TABLESPACE DAMIN_DATA_TS;

-- MERGE 
ALTER TABLE DAMIN.SALES MERGE PARTITIONS SALES_2013,SALES_2014
INTO PARTITION SALES_2013_2014;

-- SPLIT
ALTER TABLE DAMIN.SALES SPLIT PARTITION SALES_2013_2014 AT('01-01-2014')
INTO (PARTITION SALES_2013,PARTITION SALES_2014);

-- 일반 테이블과의 EXCHANGE도 가능
일반테이블로 만들고 테이블 구조를 동일하게 생성
ALTER TABLE DAMIN.SALES EXCHANGE PARTITION SALES_2014 WITH TABLE DAMIN.SALES2;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'SALES');
SELECT * FROM DAMIN.SALES PARTITION(SALES_2014) ORDER BY SALES_NO;
SELECT * FROM DAMIN.SALES2 ORDER BY SALES_NO;
-- 서로 내용이 바뀌어서 출력되는걸 확인할 수 있음

 

- Hash

-- HASH 파티션 테이블 생성
CREATE TABLE DAMIN.SALES
        (SALES_NO 	NUMBER(2)	NOT NULL,
         SALES_DATE 	DATE		NOT NULL,
 	 SALES_REGION	CHAR(2),
         CUSTOMER_ID    NUMBER(5),
         PRICE 		NUMBER(8))
PARTITION BY HASH (SALES_NO)
       	PARTITIONS 2 STORE IN (DAMIN_DATA_TS, DAMIN3_DATA_TS);

-- 통계정보 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('DAMIN', 'SALES');
SELECT PARTITION_NAME,TABLESPACE_NAME, NUM_ROWS
FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='SALES' AND TABLE_OWNER='DAMIN'
ORDER BY TABLE_NAME, PARTITION_NAME;


-- INSERT 시
SELECT * FROM DAMIN.SALES PARTITION(SYS_P610) ORDER BY SALES_NO;
INSERT INTO DAMIN.SALES PARTITION(SYS_P610) VALUES (30,'17-10-2013','CA',8226,21); -- 범위가 맞지않으면 에러반환
INSERT INTO DAMIN.SALES VALUES (30,'17-10-2013','CA',8226,21); -- 알아서 파티션영역으로 들어감

-- DELETE 시
DELETE FROM DAMIN.SALES PARTITION(SYS_P610);

-- 파티션테이블 추가
ALTER TABLE DAMIN.SALES ADD PARTITION SALES_HASH TABLESPACE DAMIN3_DATA_TS;


-- HASH 파티션은 삭제 불가


-- RENAME 
ALTER TABLE DAMIN.SALES RENAME PARTITION SYS_P610 TO SALES_TEMP;


-- MOVE
ALTER TABLE DAMIN.SALES MOVE PARTITION SYS_P609 TABLESPACE DAMIN3_DATA_TS;


-- EXCHANGE
ALTER TABLE DAMIN.SALES EXCHANGE PARTITION SYS_P609 WITH TABLE DAMIN.SALES2 WITHOUT VALIDATION;


-- HASH파티션은 SPLIT 및 MERGE불가

 

- Composite

-- LIST RANGE 복합 파티션 테이블 생성
CREATE TABLE DAMIN.sales
        (sales_no 	NUMBER(2)	NOT NULL,
         sales_date 	DATE		NOT NULL,
 	 sales_region	CHAR(2),
         customer_id    NUMBER(5),
         price 		NUMBER(8))
PARTITION BY RANGE (sales_date)
SUBPARTITION BY LIST(sales_region)
       (PARTITION sales_2013 VALUES LESS THAN (to_date('01-01-2014','dd-MM-yyyy'))
		(SUBPARTITION sales_2013_NJ values('NJ') TABLESPACE DAMIN_DATA_TS,
		 SUBPARTITION sales_2013_NY values('NY') TABLESPACE DAMIN_DATA_TS,
		 SUBPARTITION sales_2013_CA values('CA') TABLESPACE DAMIN_DATA_TS,
		 SUBPARTITION sales_2013_PA values('PA') TABLESPACE DAMIN_DATA_TS),
       PARTITION sales_2014 VALUES LESS THAN (to_date('01-01-2015','dd-MM-yyyy'))
		(SUBPARTITION sales_2014_NJ values('NJ') TABLESPACE DAMIN3_DATA_TS,
		 SUBPARTITION sales_2014_NY values('NY') TABLESPACE DAMIN3_DATA_TS,
		 SUBPARTITION sales_2014_CA values('CA') TABLESPACE DAMIN3_DATA_TS,
		 SUBPARTITION sales_2014_PA values('PA') TABLESPACE  DAMIN3_DATA_TS)
     );
     
     
-- HASH RANGE 복합 파티션 테이블
CREATE TABLE DAMIN.sales
        (sales_no 	NUMBER(2)	NOT NULL,
         sales_date 	DATE		NOT NULL,
 	 sales_region	CHAR(2),
         customer_id    NUMBER(5),
         price 		NUMBER(8))
PARTITION BY RANGE (sales_date)
SUBPARTITION BY HASH(sales_no)
      	SUBPARTITIONS 4
	STORE IN (DAMIN_DATA_TS, DAMIN3_DATA_TS, DAMIN_DATA_TS, DAMIN3_DATA_TS)
	(PARTITION sales_2013 VALUES LESS THAN (to_date('01-01-2014','dd-MM-yyyy')),
       	PARTITION sales_2014 VALUES LESS THAN (to_date('01-01-2015','dd-MM-yyyy')),
       	PARTITION sales_2015 VALUES LESS THAN (to_date('01-01-2016','dd-MM-yyyy')),
       	PARTITION sales_2016 VALUES LESS THAN (to_date('01-01-2017','dd-MM-yyyy'))
     	);

 

 

 

 

 

## 테이블 PARALLEL

 

SELECT DEGREE FROM DBA_TABS
WHERE TABLE_NAME='';

-- DEGREE가 1이면 단일 프로세스로 돌아가고있는 것

ALTER TABLE DAMIN.STUDENT PARALLEL 4;

-- 4중으로 돌아감


ALTER TABLE DAMIN.STUDENT NOPARALLEL;

 

## 임시테이블 생성

- 트랜잭션이 완료된 이후에도 데이터가 유지되도록 허락하며 최종적으로 세션을 종료되는 경우 데이터가 삭제

 

CREATE GLOBAL TEMPORARY TABLE DAMIN.TEMP_TAB (
ID NUMBER,
DESCRIPTION VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- 여기서 ON COMMIT DELETE ROWS;를 하면 트랜잭션이 마무리 될때 없어지는 구조


INSERT INTO DAMIN.TEMP_TAB VALUES (1, 'ONE');
COMMIT;

SELECT * FROM DAMIN.TEMP_TAB;

CREATE TABLE CD_TAB(
ID NUMBER,
DESCRIPTION VARCHAR2(20)
)
SEGMENT CREATION DEFERRED;

-- CREATION IMMEDIATE;

-- 확인할 수 있는방법은 DBA_SEGMENTS 확인
-- IMMEDIATE : CREATE 만해도 생성
-- DEFFERED : INSERT 전에 생성되지 않음

 

 

## 테이블 CONSTRAINTS 변경

-- 제약조건 VALIDATION 상태 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, VALIDATED
FROM DBA_CONSTRAINTS 
WHERE TABLE_NAME=''
AND OWENR_NAME='DAMIN';

-- STATUS : ENABLED이면 현재 사용가능한 상태


## NOVALIDATE 상태로 변경
ALTER TABLE DAMIN.STUDENT
ENABLE NOVALIDATE PRIMARY KEY
ENABLE NOVALIDATE CONSTRAINT [제약조건이름];

-- 그 후에 다시 조회하면 NOVALIDATE로 변경된걸 확인할 수 있음

-- 새로운 제약조건 추가
ALTER TABLE DAMIN.STUDENT ADD CONSTRAINT [제약조건이름]
CHECK (STUDENTNUM <= 5000)
INVALIDATE;

-- DROP
ALTER TABLE DAMIN.STUDENT DROP CONSTRAINT [제약조건이름];

-- 비활성화

ALTER TABLE DAMIN.STUDENT ENABLE ALL TRIGGERS;

ALTER TABLE DAMIN.[트리거이름] DISABLE;

-- 후에 DBA_TRIGGERS에서 확인 가능

 

ENABLE VALIDATE	기존 데이터와 새로운 데이터에 제약조건을 검사
ENABLE NOVALIDATE	테이블의 기존 데이터는 제약조건 검사를 하지않고, 새로운 DML 작업부터 제약조건을 검사

 

 

 

## DEFAULT COLUMN VALUE

ALTER TABLE DAMIN.PRODUCT_INFO
MODIFY (NAME DEFALUT 'DAMIN');

--다시 DEFAULT값 없애고 싶으면 NULL로 변경
ALTER TABLE DAMIN.PRODUCT_INFO
MODIFY (NAME DEFALUT NULL);

 

 

## 테이블 RENAME

-- COLUMN 이름 변경

ALTER TABLE DAMIN.STUDENT
RENAME COLUMN NAME TO NAME_2;

-- COLUMN 제거 (제약조건까지)
ALTER TABLE DAIN.STUDENT DROP (NAME) CASCADE CONSTRAINTS;

 

 

## DROP

DROP TABLE DAMIN.STUDENT;

SELECT OBJECT_NAME, ORIGINAL_NAME, TS_NAME FROM RECYCLEBIN;

FLASHBACK TABLE DAMIN.STUDENT TO BEFORE DROP;