오라클 데이터베이스 내부에 데이터를 관리하기 위해 다양한 저장 객체를 생성하는데 이것을 오브젝트라고 한다.
오브젝트 중 특별히 데이터를 저장하기 위한 별도의 저장공간을 가지는 것을 세그먼트 라고 한다.
이런 오브젝트를 생성하고, 변경하고, 관리하는 명령어를 DDL(Data Definition Language)이라고 부른다.
1. CREATE 명령
새로운 오브젝트나 스키마를 생성할 때 사용하는 명령
1) 테이블 이름은 반드시 문자로 시작해야 한다.
2) 테이블 이름이나 컬럼 이름은 최대 30 Bytes 까지 가능하다.
3) 테이블 이름은 한 명의 사용자가 다른 오브젝트들의 이름과 중복으로 사용할 수 없다.
4) 테이블 이름이나 오브젝트 이름은 오라클이 사용하는 키워드를 사용하지 않기를 권장한다.
(3) Temporary Table 생성하기
임시 작업용 데이터를 저장하기 위해 만들어졌다.
테이블을 생성하면 그 정의만 딕셔너리에 저장되어 있다가 사용자가 해당 테이블에 액세스하면 메모리 상에 해당 테이블을 만들고 데이터를 가져온다.
CREATE GLOBAL TEMPORARY TABLE
(
컬럼1 데이터 타입,
컬럼2 데이터 타입,
)
ON COMMIT [ delete | preserve ] ROWS ;
ON COMMIT delete ROWS를 사용하면 COMMIT 시에 데이터를 삭제한다는 뜻이고
ON COMMIT preserve ROWS를 사용하면 세션이 종료해야 데이터가 사라진다.
기본값은 delete
1. Redo log 를 생성하지 않는다.
2. Index, View, Trigger를 생성할 수 있으나 이 오브젝트들의 타입도 전부 Temporary이다.
3. 이 테이블에 들어있는 데이터는 이전이나 백업을 할 수 없다.
(4) 테이블 복사하기 (CTAS라고 부른다.)
1) 모든 컬럼 복사하기
CREATE TABLE dept3
AS
SELECT * FROM dept2;
2) 특정 컬럼만 복사하기
CREATE TABLE dept4
AS
SELECT dcode, dname
FROM dept2 ;
3) 테이블의 구조만 가져오기 (껍데기를 뗀다.)
CREATE TABLE dept5
AS
SELECT *
FROM dept2
WHERE 1=2 ;
(5) 가상 컬럼 테이블 생성하기 (11g 부터 추가된 기능)
1) 가상 컬럼을 가지는 테이블 생성
CREATE TABLE vt001
(
no1 number,
no2 number,
no3 number GENERATED ALWAYS AS (no1 + no2) VIRTUAL
) ;
2) 테이블에 데이터 입력
INSERT INTO vt001 VALUES (1,2,3) ;
가상 컬럼에는 INSERT 할 수 없다고 에러가 난다.
INSERT INTO vt001(no1, no2) VALUES (1,2) ;
3) 입력된 데이터 조회
SELECT * FROM vt001 ;
가상 컬럼에는 데이터를 입력하지 않았지만 자동으로 테이블 생성시에 계신된 식의 값으로 입력됨을 알 수 있다.
4) 기존 값 변경 후 가상 컬럼에 변경되는 지 확인
UPDATE vt001
SET no1=10;
SELECT * FROM vt001;
기존 컬럼의 값이 변경될 경우 즉시 가상 컬럼에도 반영된다는 것을 알 수 있다.
5) 인덱스와 제약조건 생성 가능한지 테스트
INSERT INTO vt001 (no1, no2)
VALUES (3, 4) ;
INSERT INTO vt001 (no1, no2)
VALUES (6, 6) ;
가상 컬럼에 인덱스도 만들어지고 제약조건도 설정 가능하다는 것을 확인할 수 있다.
(위에서 제약 조건을 생성해주지 않아서 INSERT가 된다.)
6) 새로운 가상 컬럼 추가
ALTER TABLE vt001
ADD (no4 GENERATED ALWAYS AS ((no1*12)+no2)) ;
SELECT * FROM vt001 ;
7) 테이블에서 가상 컬럼 내역 조회
set line 200
col column_name for a10
col data_type for a10
col data_default for a25
SELECT column_name, data_type, data_default
FROM user_tab_columns
WHERE table_name = 'VT001'
ORDER BY column_id ;
8) 조건절을 활용한 가상 컬럼 생성하기
CREATE TABLE panmae10
(
no NUMBER,
pcode CHAR(4),
pdate CHAR(8),
pqty NUMBER,
pbungi NUMBER(1)
GENERATED ALWAYS AS
(
CASE
WHEN SUBSTR(pdate, 5, 2) IN ('01', '02', '03') THEN 1
WHEN SUBSTR(pdate, 5, 2) IN ('04', '05', '06') THEN 2
WHEN SUBSTR(pdate, 5, 2) IN ('07', '08', '09') THEN 3
ELSE 4
END
) virtual
) ;
INSERT INTO panmae10 (no, pcode, pdate, pqty)
VALUES(1, '100', '20110112', 10) ;
INSERT INTO panmae10 (no, pcode, pdate, pqty)
VALUES(2, '200', '20110505', 20) ;
INSERT INTO panmae10 (no, pcode, pdate, pqty)
VALUES(3, '300', '20110812', 30) ;
INSERT INTO panmae10 (no, pcode, pdate, pqty)
VALUES(4, '400', '20111024', 40) ;
COMMIT ;
SELECT *
FROM panmae10 ;
(6) 파티션 테이블 생성하기
Oracle 11g PARTITION 전략
파티셔닝 종류 데이터 분할 기준
Range partitioning 연속적인 데이터 입력 시 값의 영역을 지정하여 분할 (예: 판매일자)
List partitioning 일정한 순서가 없을 경우나 특정 값을 기준으로 분할 (예: 상품코드)
Hash partitioning 해쉬 알고리즘 기반으로 랜덤하면서 균등하게 분할
Composite partitioning 위에서 설명된 기법 중 두가지를 복합적으로 구성하여 파티션을 구현
Range-Range
Range-List
Range-Hash
List-List
List-Range
List-Hash
Interval-Range
Interval-List
Interval-Hash
Interval partitioning 동일한 크기의 간격을 기준으로 파티셔닝을 정의함.
첫번째 생성되는 파티션을 제외한 모든 파티션은 조건에 맞는 데이터가 입력되는 시점에 자동으로 생성됨.
Range partitioning 의 확정임
REF partitioning 기본키-외래키 관계를 통해 자식테이블 파티셔닝을 부모테이블로부터 상속.
파티셔닝 키는 자식 테이블의 실제 컬럼에 저장되지 않음
Virtual column based 위에서 설명된 파티셔닝 테크닉 중 하나를 사용하되 파티셔닝 키는 가상 컬럼을 기반으로 설정함.
partitioning 가상 컬럼은 디스크에 저장되지 않으며 메타데이터의 형태로만 존재함
1) Range PARTITION (범위 파티셔닝)
주로 특정 기준에 의해서 범위를 나눌 때 사용하는 방법이다.
STEP1 현재 상태 확인
CONN / AS SYSDBA
SELECT tablespace_name, bytes/1024/1024 MB, file_name
FROM dba_data_files ;
save dd.sql ;
STEP2 분기별 판매 저장용 테이블스페이스 ts_q1, ts_q2, ts_q3, ts_q4를 생성한다.
CREATE TABLESPACE ts_q1
DATAFILE '/app/oracle/oradata/testdb/ts_q101.dbf' SIZE 5M ;
CREATE TABLESPACE ts_q2
DATAFILE '/app/oracle/oradata/testdb/ts_q201.dbf' SIZE 5M ;
CREATE TABLESPACE ts_q3
DATAFILE '/app/oracle/oradata/testdb/ts_q301.dbf' SIZE 5M ;
CREATE TABLESPACE ts_q4
DATAFILE '/app/oracle/oradata/testdb/ts_q401.dbf' SIZE 5M ;
CREATE TABLESPACE ts_q5
DATAFILE '/app/oracle/oradata/testdb/ts_q501.dbf' SIZE 5M ;
@dd
TABLESPACE MB FILE_NAME
---------- ------- --------------------------------------------------
USERS 1890 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1 865 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX 550 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM 1140 /app/oracle/oradata/testdb/system01.dbf
TS_Q1 5 /app/oracle/oradata/testdb/ts_q101.dbf
TS_Q2 5 /app/oracle/oradata/testdb/ts_q201.dbf
TS_Q3 5 /app/oracle/oradata/testdb/ts_q301.dbf
TS_Q4 5 /app/oracle/oradata/testdb/ts_q401.dbf
TS_Q5 5 /app/oracle/oradata/testdb/ts_q501.dbf
STEP3 파티션 테이블 panmae를 생성 후 데이터를 입력한다.
CREATE TABLE panmae
(
pdate varchar2(8),
pcode number(3),
pqty number(5)
)
PARTITION BY RANGE(pdate)
(
PARTITION q1 VALUES LESS THAN ('20100401') TABLESPACE ts_q1,
PARTITION q2 VALUES LESS THAN ('20100701') TABLESPACE ts_q2,
PARTITION q3 VALUES LESS THAN ('20101001') TABLESPACE ts_q3,
PARTITION q4 VALUES LESS THAN ('20110101') TABLESPACE ts_q4,
PARTITION q5 VALUES LESS THAN (maxvalue) TABLESPACE ts_q5
) ;
INSERT INTO panmae
VALUES ('20101001', 100, 20) ;
COMMIT ;
INSERT INTO panmae
VALUES ('20100405', 200, 50) ;
INSERT INTO panmae
VALUES ('20100922', 300, 30) ;
COMMIT ;
STEP4 각 파티션 별로 어떤 테이블에 어떤 데이터가 들어가 있는지 조회하기
SELECT * FROM panmae PARTITION (q2) ;
SELECT * FROM panmae PARTITION (q3) ;
SELECT * FROM panmae PARTITION (q4) ;
STEP5 특정 파티션에만 데이터 입력하는 방법
INSERT INTO panmae PARTITION (q1)
VALUES ('20100406', 200, 70) ;
틀린 데이터가 들어가면 입력되지 않는다.
INSERT INTO panmae PARTITION (q1)
VALUES ('20100101', 200, 70) ;
COMMIT ;
SELECT * FROM panmae PARTITION (q1) ;
STEP6 특정 테이블에 속해 있는 파티션 찾기
set line 200
col table_name for a10
col partition_name for a10
col high_value for a10
SELECT table_name, partition_name, tablespace_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'PANMAE' ;
STEP7 UPDATE 수행하여 데이터가 파티션 이동되는지 확인하기
SELECT * FROM panmae ;
UPDATE panmae
SET pdate='20100805'
WHERE pcode=100 ;
ALTER TABLE panmae ENABLE row movement ;
UPDATE panmae
SET pdate='20100805'
WHERE pcode=100 ;
SELECT * FROM panmae PARTITION (q3) ;
rollback ;
SELECT * FROM panmae PARTITION(q3) ;
UPDATE panmae
SET pdate='20100805'
WHERE pcode=100;
SELECT * FROM panmae PARTITION(q3) ;
COMMIT ;
STEP8 RANGE PARTITION 추가하기
CREATE TABLESPACE ts_mon_01
DATAFILE '/app/oracle/oradata/testdb/ts_mon_01.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_02
DATAFILE '/app/oracle/oradata/testdb/ts_mon_02.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_03
DATAFILE '/app/oracle/oradata/testdb/ts_mon_03.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_04
DATAFILE '/app/oracle/oradata/testdb/ts_mon_04.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_05
DATAFILE '/app/oracle/oradata/testdb/ts_mon_05.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_06
DATAFILE '/app/oracle/oradata/testdb/ts_mon_06.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_07
DATAFILE '/app/oracle/oradata/testdb/ts_mon_07.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_08
DATAFILE '/app/oracle/oradata/testdb/ts_mon_08.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_09
DATAFILE '/app/oracle/oradata/testdb/ts_mon_09.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_10
DATAFILE '/app/oracle/oradata/testdb/ts_mon_10.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_11
DATAFILE '/app/oracle/oradata/testdb/ts_mon_11.dbf' SIZE 5M ;
CREATE TABLESPACE ts_mon_12
DATAFILE '/app/oracle/oradata/testdb/ts_mon_12.dbf' SIZE 5M ;
현재 q1파티션은 1,2,3월 데이터가 들어잇는데 이것을 1월파티션, 2월파티션, 3월파티션으로 SPLIT PARTITION 방법을 이용하여 분리해보겠다.
CREATE TABLESPACE q1
DATAFILE '/app/oracle/oradata/testdb/q01.dbf' SIZE 5M ;
CREATE TABLESPACE q2
DATAFILE '/app/oracle/oradata/testdb/q02.dbf' SIZE 5M ;
CREATE TABLESPACE q3
DATAFILE '/app/oracle/oradata/testdb/q03.dbf' SIZE 5M ;
CREATE TABLESPACE q4
DATAFILE '/app/oracle/oradata/testdb/q04.dbf' SIZE 5M ;
CREATE TABLE panmae2
(
pdate varchar2(8),
pcode number(3),
pqty number(5))
)
PARTITION BY RANGE(pdate)
(
PARTITION q1 VALUES LESS THAN ('20100401') TABLESPACE q1,
PARTITION q2 VALUES LESS THAN ('20100701') TABLESPACE q2,
PARTITION q3 VALUES LESS THAN ('20101001') TABLESPACE q3,
PARTITION q4 VALUES LESS THAN ('20110101') TABLESPACE q4
) ;
INSERT INTO panmae2 VALUES ('20100101', 100, 10);
INSERT INTO panmae2 VALUES ('20100201', 200, 20);
INSERT INTO panmae2 VALUES ('20100301', 300, 30);
INSERT INTO panmae2 VALUES ('20100401', 400, 40);
INSERT INTO panmae2 VALUES ('20100501', 500, 50);
INSERT INTO panmae2 VALUES ('20100601', 600, 60);
INSERT INTO panmae2 VALUES ('20100701', 700, 70);
INSERT INTO panmae2 VALUES ('20100801', 800, 80);
INSERT INTO panmae2 VALUES ('20100901', 900, 90);
INSERT INTO panmae2 VALUES ('20101001', 110, 11);
INSERT INTO panmae2 VALUES ('20101101', 111, 111);
INSERT INTO panmae2 VALUES ('20101201', 120, 12);
COMMIT ;
각 파티션 별 데이터 조회
SELECT * FROM panmae2 PARTITION(q1) ;
SELECT * FROM panmae2 PARTITION(q2) ;
SELECT * FROM panmae2 PARTITION(q3) ;
SELECT * FROM panmae2 PARTITION(q4) ;
각 월별로 파티션 분리하기(SPLIT PARTITION 옵션 사용)
ALTER TABLE panmae2 SPLIT PARTITION q1
AT ('20100201')
INTO (PARTITION d01 TABLESPACE ts_mon_01,
PARTITION d02 TABLESPACE ts_mon_02) ;
SELECT * FROM panmae2 PARTITION (d01) ;
SELECT * FROM panmae2 PARTITION (d02) ;
ALTER TABLE panmae2 SPLIT PARTITION d02
AT ('20100301')
INTO (PARTITION d02 TABLESPACE ts_mon_02,
PARTITION d03 TABLESPACE ts_mon_03) ;
SELECT * FROM panmae2 PARTITION (d01) ;
SELECT * FROM panmae2 PARTITION (d02) ;
SELECT * FROM panmae2 PARTITION (d03) ;
STEP9 새로운 파티션 추가하기 (ADD PARTITION 사용)
CREATE TABLESPACE ts_mon_201101
DATAFILE '/app/oracle/oradata/testdb/201101_01.dbf' SIZE 5M ;
ALTER TABLE panmae2 ADD PARTITION p201101 VALUES LESS THAN ('20110201')
TABLESPACE ts_mon_201101 ;
SELECT table_name, partition_name, high_value, tablespace_name
FROM dba_tab_partitions
WHERE table_name = 'PANMAE2'
ORDER BY 2 ;
STEP10 파티션 삭제하기 (DROP PARTITION 사용하기)
INSERT INTO panmae2 VALUES ('20100101', 100, 20) ;
COMMIT ;
SELECT * FROM panmae2 PARTITION(p201101) ;
ALTER TABLE panmae2 DROP PARTITION p201101 ;
SELECT * FROM panmae2 PARTITION(p201101) ;
SELECT * FROM panmae2 ;
STEP11 파티션 이름 바꾸기 (RENAME PARTITION 사용하기)
ALTER TABLE panmae2 RENAME PARTITION d01 to d1 ;
ALTER TABLE panmae2 RENAME PARTITION d02 to d2 ;
SELECT table_name, partition_name, high_value, tablespace_name
FROM dba_tab_partitions
WHERE table_name='PANMAE2'
ORDER BY 2 ;
2) HASH PARTITION (8i 버전부터 지원)
STEP1 Hash PARTITION 저장할 테이블 스페이스 생성
CREATE TABLE hash_test01
DATAFILE '/app/oracle/oradata/testdb/hash_test01.dbf' SIZE 5M ;
CREATE TABLE hash_test02
DATAFILE '/app/oracle/oradata/testdb/hash_test02.dbf' SIZE 5M ;
CREATE TABLE hash_test03
DATAFILE '/app/oracle/oradata/testdb/hash_test03.dbf' SIZE 5M ;
CREATE TABLE hash_test04
DATAFILE '/app/oracle/oradata/testdb/hash_test04.dbf' SIZE 5M ;
STEP2 HASH PARTITION 테이블 생성하기
CREATE TABLE panmae3
(
pdate varchar2(10),
pcode number(3),
pqty number(5)
)
PARTITION BY HASH(pdate) PARTITIONS 4
STORE IN (hash_test01, hash_test02, hash_test03, hash_test04) ;
INSERT INTO panmae3 VALUES ('20101010',100,30) ;
INSERT INTO panmae3 VALUES ('20101002',100,40) ;
INSERT INTO panmae3 VALUES ('20100401',200,20) ;
INSERT INTO panmae3 VALUES ('20100701',300,30) ;
COMMIT ;
2) LIST PARTITION (목록 분할 파티션)
STEP1 LIST PARTITION을 저장할 테이블 스페이스 생성
CREATE TABLESPACE ts_list_p1
DATAFILE '/app/oracle/oradata/testdb/ts_list_p101.dbf' SIZE 5M
AUTOEXTEND ON ;
CREATE TABLESPACE ts_list_p2
DATAFILE '/app/oracle/oradata/testdb/ts_list_p201.dbf' SIZE 5M
AUTOEXTEND ON ;
CREATE TABLESPACE ts_list_p3
DATAFILE '/app/oracle/oradata/testdb/ts_list_p301.dbf' SIZE 5M
AUTOEXTEND ON ;
@dd
CREATE TABLE scott.list_exam
(
no number, name varchar2(10))
PARTITION BY LIST(name)
(
PARTITION p1 VALUES ('새우짱') TABLESPACE ts_list_p1,
PARTITION p2 VALUES ('짱팔') TABLESPACE ts_list_p3,
PARTITION p3 VALUES ('맛동강') TABLESPACE ts_list_p1
) ;
INSERT INTO scott.list_exam VALUES (1,'새우짱') ;
INSERT INTO scott.list_exam VALUES (2,'짱팔') ;
INSERT INTO scott.list_exam VALUES (3,'맛동강') ;
COMMIT ;
SELECT owner, table_name, partitioned
FROM dba_tables
WHERE owner='SCOTT' ;
INSERT INTO scott.list_exam VALUES (4, '감자짱') ;
예처럼 파티션이 지정되지 않은 값이 입력되면 에러가 발생한다.
그래서 지정된 리스트 이외의 값 저장하는 DEFAULT 파티션을 아래처럼 생성해준다.
CREATE TABLESPACE ts_list_default
DATAFILE '/app/oracle/oradata/test/ts_list_default01.dbf' SIZE 5M
AUTOEXTEND ON ;
ALTER TABLE scott.list_exam
ADD PARTITION p4
VALUES (DEFAULT) TABLESPCE ts_list_default ;
INSERT INTO scott.list_exam VALUES (4, '감자짱') ;
SELECT owner, segment_name, partition_name, tablespace_name, extent_id, bytes, blocks
FROM dba_extents
WHERE owner = 'SCOTT'
AND segment_name = 'LIST_EXAM' ;
SELECT table_owner, table_name, partition_name, high_value, tablespace_name
FROM dba_tab_partitions
WHERE table_owner = 'SCOTT'
AND table_name = 'LIST_EXAM' ;
만약 새로운 list 파티션을 추가하려면 default 파티션 때문에 추가가 안되고 아래와 같이 에러가 발생한다.
그 때는 default 파티션을 삭제 후 새로운 파티션을 생성해야 하는데 이 때 default 파티션에 있던 데이터는 전부 삭제된다.
ALTER TABLE scott.list_exam
ADD PARTITION p5
VALUES ('감자짱') TABLESPACE ts_list_p5
ERROR
ALTER TABLE scott.list_exam
DROP PARTITION p4 ;
SELECT *
FROM scott.list_exam ;
ALTER TABLE scott.list_exam
ADD PARTITION p5
VALUES ('감자짱') TABLESPACE ts_list_p5 ;
INSERT INTO scott.list_exam VALUES (4, '감자짱') ;
SELECT * FROM scott.list_exam ;
SELCT table_owner, table_name, partition_name, high_value, tablespace_name
FROM dba_tab_partitions
WHERE table_owner = 'SCOTT'
AND table_name = 'LIST_EXAM' ;
SELECT * FROM scott.list_exam PARTITION(p1);
SELECT * FROM scott.list_exam PARTITION(p2);
SELECT * FROM scott.list_exam PARTITION(p3);
SELECT * FROM scott.list_exam PARTITION(p5);
4) COMPOSITE PARTITION(복합 파티션)
RANGE - HASH 파티션
STEP1 현재 상태 조회
@dd
STEP2 테스트용 테이블 스페이스 생성하기
(분기별로 사용될 4개의 테이블 스페이스를 생성한다.)
CREATE TABLESPACE ts_ql
DATAFILE '/app/oracle/oradata/testdb/ts_q1_01.dbf' SIZE 5M
AUTOEXTEND ON ;
CREATE TABLESPACE ts_ql
DATAFILE '/app/oracle/oradata/testdb/ts_q2_01.dbf' SIZE 5M
AUTOEXTEND ON ;
CREATE TABLESPACE ts_ql
DATAFILE '/app/oracle/oradata/testdb/ts_q3_01.dbf' SIZE 5M
AUTOEXTEND ON ;
CREATE TABLESPACE ts_ql
DATAFILE '/app/oracle/oradata/testdb/ts_q4_01.dbf' SIZE 5M
AUTOEXTEND ON ;
@dd
STEP3 판매테이블을 생성한다
(Range - HASH)
conn scott/tiger
CREATE TABLE panmae_range_hash
(
pno number,
pcode number,
pdate varchar2(8),
qty number
)
PARTITION BY RANGE (pdate)
SUBPARTITION BY HASH (pcode)
SUBPARTITION 4
(
PARTITION panmae_q1 VALUES LESS THAN ('20110401') TABLESPACE ts_q1,
PARTITION panmae_q2 VALUES LESS THAN ('20110701') TABLESPACE ts_q2,
PARTITION panmae_q3 VALUES LESS THAN ('20111001') TABLESPACE ts_q3,
PARTITION panmae_q4 VALUES LESS THAN ('20120101') TABLESPACE ts_q4
)
STEP4 여러 건의 데이터를 입력한 후 확인한다
INSERT INTO panmae_range_hash VALUES (1,1000,'20110301',5) ;
INSERT INTO panmae_range_hash VALUES (2,2000,'20110502',6) ;
INSERT INTO panmae_range_hash VALUES (3,3000,'20110805',7) ;
INSERT INTO panmae_range_hash VALUES (4,4000,'20111106',8) ;
COMMIT ;
SELECT table_owner, table_name, partition_name, high_value, tablespace_name
FROM dba_tab_partitions
WHERE table_owner='SCOTT'
AND table_name = 'PANMAE_RANGE_HASH' ;
SELECT * FROM panmae_range_hash PARTITION (panmae_q1) ;
SELECT * FROM panmae_range_hash PARTITION (panmae_q2) ;
SELECT * FROM panmae_range_hash PARTITION (panmae_q3) ;
SELECT * FROM panmae_range_hash PARTITION (panmae_q4) ;
RANGE - LIST 파티션
CREATE TABLE panmae_range_list
(
pno number,
pname varchar2(10),
pdate varchar2(8),
qty number
)
PARTITION BY RANGE(pdate)
SUBPARTITION BY LIST(pname)
(
PARTITION p_q1 VALUES LESS THAN ('20110401') TABLESPACE ts_q1
(
SUBPARTITION p_mat_q1 VALUES ('맛동산'),
SUBPARTITION p_gam_q1 VALUES ('감자깡'),
SUBPARTITION p_ja_q1 VALUES ('자갈치'),
SUBPARTITION p_kan_q1 VALUES ('칸쵸')
),
PARTITION p_q2 VALUES LESS THAN ('20110701') TABLESPACE ts_q2
(
SUBPARTITION p_mat_q2 VALUES ('맛동산'),
SUBPARTITION p_gam_q2 VALUES ('감자깡'),
SUBPARTITION p_ja_q2 VALUES ('자갈치'),
SUBPARTITION p_kan_q2 VALUES ('칸쵸')
),
PARTITION p_q3 VALUES LESS THAN ('20110401') TABLESPACE ts_q3
(
SUBPARTITION p_mat_q3 VALUES ('맛동산'),
SUBPARTITION p_gam_q3 VALUES ('감자깡'),
SUBPARTITION p_ja_q3 VALUES ('자갈치'),
SUBPARTITION p_kan_q3 VALUES ('칸쵸')
),
PARTITION p_q4 VALUES LESS THAN ('20110401') TABLESPACE ts_q4
(
SUBPARTITION p_mat_q4 VALUES ('맛동산'),
SUBPARTITION p_gam_q4 VALUES ('감자깡'),
SUBPARTITION p_ja_q4 VALUES ('자갈치'),
SUBPARTITION p_kan_q4 VALUES ('칸쵸')
)
) ;
INSERT INTO panmae_range_list VALUES (1, '맛동산', '20110102', 3) ;
INSERT INTO panmae_range_list VALUES (2, '감자깡', '20110505', 4) ;
INSERT INTO panmae_range_list VALUES (3, '자갈치', '20110302', 5) ;
INSERT INTO panmae_range_list VALUES (4, '칸쵸', '20110801', 6) ;
COMMIT ;
SELECT * FROM panmae_range_list PARTITION(p_q1) ;
SELECT * FROM panmae_range_list PARTITION(p_q2) ;
SELECT * FROM panmae_range_list PARTITION(p_q3) ;
SELECT * FROM panmae_range_list PARTITION(p_q4) ;
이상으로 많이 사용되는 주요 파티셔닝 기법을 살펴보았다.
다음으로 11g버전에서 추가된 주요 파티셔닝 기법을 살펴보겠다.
5) Interval PARTITION(11g New Feature)
11g 부터 추가된 기능으로 Range PARTITION의 확장형이다.
Range PARTITION에서 파티션의 범위를 벗어난 데이터가 입력될 경우 에러가 발생하면서 입력이 되지 않는데
이런 문제는 interval PARTITION 을 활용하면 오라클이 필요한 파티션을 스스로 생성한 후 데이터를 입력하게 된다.
이 때 자동으로 생성되는 파티션들은 모두 동일한 범위의 크기를 가지게 되며 파티션 이름은 오라클이 자동으로 지정하게 된다.
Range PARTITION 생성 후 범위를 벗어난 데이터 입력으로 에러 발생 상황
SELECT tablespace_name, bytes/1024/1024 MB, file_name
FROM dba_data_files ;
CREATE TABLESPACE ts_range_q1
DATAFILE '/app/oracle/oradata/testdb/ts_range_q101.dbf' SIZE 5M
AUTOEXTEND ON ;
CREATE TABLESPACE ts_range_q2
DATAFILE '/app/oracle/oradata/testdb/ts_range_q201.dbf' SIZE 5M
AUTOEXTEND ON ;
CREATE TABLESPACE ts_range_q3
DATAFILE '/app/oracle/oradata/testdb/ts_range_q301.dbf' SIZE 5M
AUTOEXTEND ON ;
CREATE TABLESPACE ts_range_q4
DATAFILE '/app/oracle/oradata/testdb/ts_range_q401.dbf' SIZE 5M
AUTOEXTEND ON ;
COL tablespace_name FOR a15
COL file_name FOR a50
SET LINE 200
SELECT tablespace_name, bytes/1024/1024MB, file_name
FROM dba_data_files ;
CREATE TABLE scott.sales
(
sno number,
sname varchar2(10),
sdate varchar2(8)
)
PARTITION BY RANGE(sdate)
(
PARTITION q1 VALUES LESS THAN ('20110401') TABLESPACE ts_range_q1
)
INSERT INTO scott.sales VALUES (1, '새우깡', '20110101') ;
INSERT INTO scott.sales VALUES (2, '감자깡', '20110401') ;
위에서 해당 값을 저장할 파티션이 존재하지 않아서 데이터가 입력되지 않고 에러가 발생한다.
다른 테이블을 INTERVAL PARTITION 으로 생성해서 다시 입력하면서 확인하겠다.
CREATE TABLE scott.sales_in
(
sno number,
sname varchar2(10),
sdate date
)
PARTITION BY RANGE(sdate)
INTERVAL(numtoyminterval(1, 'MONTH'))
(
PARTITION q1 VALUES LESS THAN (TO_DATE('20110401', 'YYYYMMDD'))
TABLESPACE ts_range_q1
) ;
INSERT INTO scott.sales_in
VALUES (1, '새우깡', TO_DATE('20110101', 'YYYYMMDD')) ;
INSERT INTO scott.sales_in
VALUES (2, '감자깡', TO_DATE('20110101', 'YYYYMMDD')) ;
INSERT INTO scott.sales_in
VALUES (3, '고구마깡', TO_DATE('20110501', 'YYYYMMDD')) ;
INSERT INTO scott.sales_in
VALUES (4, '양파깡', TO_DATE('20110531', 'YYYYMMDD')) ;
INSERT INTO scott.sales_in
VALUES (5, '꽃게랑', TO_DATE('20110601', 'YYYYMMDD')) ;
COMMIT ;
여러 건의 데이터를 생성했다. 자동으로 생성된 파티션을 조회한다.
SELECT table_name, partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'SALES_IN' ;
SELECT *
FROM scott.sales_in PARTITION (q1) ;
SELECT *
FROM scott.sales_in PARTITION (sys_p41) ;
SELECT *
FROM scott.sales_in PARTITION (sys_p42) ;
SELECT *
FROM scott.sales_in PARTITION (sys_p43) ;
자동으로 만들어지는 파티션은 어떤 테이블스페이스에 들어가는지 확인한다.
SELECT table_name, partition_name, tablespace_name
FROM dba_tab_partitions
WHERE table_name = 'SALES_IN' ;
SELECT username, default_tablespace
FROM dba_users
WHERE username = 'SOCTT' ;
CREATE TABLE scott.sales_in2
(
sno number,
sname varchar2(10),
sdate date
)
PARTITION BY RANGE(sdate)
INTERVAL(numtoyminterval(1, 'MONTH'))
STORE IN (ts_range_q2, ts_range_q3, ts_range_q4)
(
PARTITION q1 VALUES LESS THAN (to_date('20110401', 'YYYYMMDD'))
TABLESPACE ts_range_q1
) ;
INSERT INTO scott.sales_in2
VALUES (1, '새우깡', TO_DATE('20110101', 'YYYYMMDD')) ;
INSERT INTO scott.sales_in2
VALUES (2, '감자깡', TO_DATE('20110101', 'YYYYMMDD')) ;
INSERT INTO scott.sales_in2
VALUES (3, '고구마깡', TO_DATE('20110501', 'YYYYMMDD')) ;
INSERT INTO scott.sales_in2
VALUES (4, '양파깡', TO_DATE('20110531', 'YYYYMMDD')) ;
INSERT INTO scott.sales_in2
VALUES (5, '꽃게랑', TO_DATE('20110601', 'YYYYMMDD')) ;
COMMIT ;
SELECT table_name, partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'SALES_IN2' ;
SELECT *
FROM scott.sales_in2 PARTITION (sys_p44) ;
SELECT *
FROM scott.sales_in2 PARTITION (sys_p45) ;
SELECT *
FROM scott.sales_in2 PARTITION (sys_p46) ;
테이블 스페이스를 지정해주면 해당 테이블 스페이스에 생성되는 것을 확인할 수 있다.
INTERVAL PARTITION 에서 자동으로 파티션을 생성할 때 사용하는 함수
interval (numtoYMinterval( 1, 'MONTH')) 1달 단위
interval (numtoYMinterval( 2, 'MONTH')) 2달 단위
interval (numtoYMinterval( 1, 'YEAR')) 1년 단위
하루 단위나 시간, 분 초 등의 단위는
numtoDSinterval(숫자, '단위')
6) SYSTEM PARTITION (11g New Feature)
System PARTITION 은 PARTITION KEY 를 파티션 생성시에 지정하지 않고 데이터를 삽입할 때 직접 지정하는 방식이다.
또한 검색할 때도 파티션명을 명시해야만 해당 파티션에서 데이터를 찾게 되며, 만약 명시하지 않으면 전체파티션에서 데이터를 읽게 되어
속도가 급격히 저하될 수 있다.
CREATE TABLE panmae_system
(
pno number,
pname number,
pdate varchar2(8)
)
PARTITION BY SYSTEM
(
PARTITION p_q1 TABLESPACE ts_q1,
PARTITION p_q1 TABLESPACE ts_q2,
PARTITION p_q1 TABLESPACE ts_q3,
PARTITION p_q1 TABLESPACE ts_q4
) ;
INSERT INTO panmae_system VALUES (1, 1000, '20110101') ;
위 에러 메세지는 system 파티션일 경우 데이터를 입력할 때 파티션 이름을 지정하지 않게되면 에러가 나는것을 보여준다.
아래와 같이 파티션 명을 지정해 주어야 한다.
INSERT INTO panmae_system PARTITION (p_q1) VALUES (1, 1000, '20110101') ;
(7) 파티션의 인덱스
대량의 데이터가 들어간 파티션 테이블의 데이터를 관리할 때 해당 파티션에 인덱스를 생성할 수 있다.
크게 Local Index, Global Index로 나뉘게 된다.
Local Index 란 해당 파티션만 관리하는 인덱스를 생성하는 것이며
Global Index는 전체 파티션을 다 포함하는 인덱스를 생성하는 것이다.
1) Local Index
Local Prefixed Index - 파티션을 나눌 때 기준이 되는 컬럼으로 인덱스를 생성한 것
Unique / Non Unique index 모두 생성할 수 있다.
Local Non-Prefixed Index - 파티션 생성 기준 컬럼 이외의 컬럼으로 인덱스를 생성한 것
2. ALTER 명령
만들어져 있는 오브젝트를 변경하는 명령.
테이블같은 경우에는 컬럼을 추가하거나 삭제하거나 컬럼이름이나 테이블 이름을 바꾸는 등의 작업을 할 수 있다.
이 명령은 부하가 많이 걸리는 명령이므로 사용량이 많은 시간에 수행하는 것은 위험하니 조심해야 한다.
(1) 새로운 컬럼 추가하기
CREATE TABLE dept6
AS
SELECT dcode, dname
FROM dept2
WHERE dcode IN (1000, 1001, 1002) ;
SELECT *
FROM dept6
LOC 컬럼 추가
ALTER TABLE dept6
ADD ( LOC VARCHAR2(10) ) ;
SELECT *
FROM dept6 ;
NULL 값이 아닌 다른 값을 기본값으로 하고 싶으면 DEFAULT 값을 지정하면 됨
ALTER TABLE dept6
ADD ( LOC2 VARCHAR2(10) DEFAULT '서울') ;
SELECT *
FROM dept6 ;
(2) 테이블 컬럼 이름 변경하기
ALTER TABLE dept6 RENAME COLUMN LOC2 TO AREA ;
테이블 이름 변경
RENAME dept6 TO dept7 ;
(3) 컬럼 데이터 크기 변경하기
DESC dept7 ;
ALTER TABLE dept7
MODIFY (dcode VARCHAR2(10)) ;
DESC dept7 ;
(4) 컬럼 삭제하기
ALTER TABLE dept7 DROP COLUMN loc ;
만약 참조키로 설정되어 있는 부모테이블의 컬럼을 삭제하려 할 경우에는 아래와 같은 방법으로 지우면 된다.
ALTER TABLE dept7 DROP COLUMN loc CASCADE CONSTRAINTS ;
(5) 읽기 전용 테이블로 변경하기
만약 어떤 경우에 특정 테이블의 내용을 모두 변경할 수 없게 하고 조회만 가능하도록 설정해야 한다면
트리거를 사용하거나 제약조건을 DISABLE NOVALIDATE 해서 작업해야 한다.
가능하지만 번거롭게 여러가지 작업을 해야 한다.
11g 부터는 아주 간단하게 테이블을 읽기 전용으로 변경할 수 있다.
CREATE TABLE t_read
(
no number,
name varchar2(10)
) ;
INSERT INTO t_read
VALUES (1, 'AAA') ;
COMMIT ;
SELECT *
FROM t_read ;
읽기 전용으로 변경
ALTER TABLE t_read read only ;
입력 시도
INSERT INTO t_read
VALUES (2, 'BBB') ;
INSERT INTO t_read
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."T_READ"
Table dropped.
데이터 입력이나 테이블 구조 변경은 불가하지만 테이블 자체는 DROP할 수 있다.
읽기 전용 테이블을 다시 읽기/쓰기 상태로 변경하려면
ALTER TABLE t_read read write ;
현재 테이블이 읽기 전용인지를 조회하려면 user_tables의 read_only 컬럼을 조회하면 된다. yes면 읽기 전용
3. TRUNCATE 명령
테이블의 데이터를 전부 삭제하고 사용하고 있던 공간을 반납하는 명령이다.
TRUNCATE TABLE dept7 ;
4. DROP 명령
테이블 자체를 삭제한다.
DROP TABLE detp7 ;
오라클 10g부터는 테이블이 삭제되는 것이 아니라 휴지통으로 보낸다. 삭제된 테이블 이름은 BIN$...로 변경된다.
이 기능은 10g에서 아주 막강하게 성능을 발휘하는 FLASHBACK TABLE 이라는 기능을 구현하기 위함.
5. DELETE, TRUNCATE, DROP 명령어의 차이점
DELETE는 데이터만 지워지고 쓰고 있던 디스크 상의 공간은 그대로 있다.
TRUNCATE는 최초에 테이블이 만들어졌던 상태, 즉 데이터가 1건도 없는 상태로 모든 데이터를 삭제하고 컬럼값만 남겨 놓는다.
용량도 줄어들고 인덱스 등도 모두 삭제된다. DELETE처럼 원하는 데이터만 골라서 삭제할 순 없다.
DROP은 데이터와 테이블 모두를 삭제한다. 사용하던 공간도 모두 반납하고 인덱스나 제약조건 등 오브젝트도 삭제된다.
6. 데이터 딕셔너리 (Dictionary)
오라클 데이터베이스를 운영하기 위한 정보들을 모아둔 곳.
- 오라클 데이터베이스의 메모리 구조와 파일에 대한 구조 정보들
- 각 오브젝트들이 사용하고 있는 공간들의 정보들
- 제약조건 정보들
- 사용자에 대한 정보들
- 권한이나 프로파일, 롤에 대한 정보들
- 감사(audit) 에 대한 정보들
Data
Dictionary - Base Table - Database 를 생성하는 시점에 자동으로 만들어진다.
ㄴ Data Dictionary View - Static(수동 업데이트) - DBA_xxx - 모든 오브젝트를 볼 수 있다. (DBA권한)
(catalog.sql 이란 파일이 수행되어야 함) ㄴ ALL_xxx - 해당사용자가 생성한 오브젝트와 접근 가능한 모든 오브젝트 조회 가능
ㄴ USER_xxx - 해당 사용자가 생성한 오브젝트만 조회 가능
ㄴ Dynamic - V$xxx (자동 업데이트)
STEP1 연습용 테이블 static_table 생성하고 데이터 입력
CREATE TABLE static_table
(no number) ;
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO static_table VALUES (i) ;
END LOOP ;
COMMIT ;
END ;
/
SELECT COUNT(*)
FROM static_table ;
STEP2 데이터 딕셔너리 조회하여 해당 테이블에 데이터가 몇 건 있는지 확인
SELECT NUM_ROWS, BLOCKS
FROM USER_TABLES
WHERE TABLE_NAME = 'STATIC_TABLE' ;
딕셔너리 내용이 변경이 안되어서 데이터가 한 건도 없는 것으로 조회됨
STEP3 딕셔너리를 수동으로 업데이트 한 후 다시 조회
ANALYZE TABLE static_table COMPUTE STATISTICS ;
SELECT NUM_ROWS, BLOCKS
FROM USER_TABLES
WHERE TABLE_NAME = 'STATIC_TABLE' ;
ANALYZE 명령어는 실제 테이블이나 인덱스, 클러스터 등을 하나씩 조사해서 그 결과를 딕셔너리에 반영시키는 명령어이다.
이 명령어와 더불어 8i 버전 이후 부터는 DBMS_STATS 라는 패키지도 많이 사용하는데 이 명령어들은 해당 오브젝트를 하나씩 다 검사하기 때문에
오라클에 부하를 많이 주게 된다.
그러므로 절대로 사용량이 많은 업무시간에는 해당 작업(통계 정보를 최신으로 업데이트 하는 작업) 을 피하기 권한다.
Static Data Dictionary 와 반대로 Dynamic Performance View 는 정보를 실시간으로 조회하게 되며 사용자가 Dynamic Performance View 에 조회를 하게 되면
오라클은 해당 정보를 Control file이나 현재 메모리에서 조회하여 보여주게 된다.
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저