Managing Schema Object

▣ Table Types

Type	Description
Ordinary(heap organized) table	일반 테이블
Partitioned table	1개의 테이블을 다수의 Segment (T/S) 에 저장
Index-organized table	Index에 Data 까지 저장
Clustered table	1개의 Segment에 여러 개의 객체 저장

▣ Partition

범위	범위 분할은 열 값의 범위에 준하여 행을 분할 영역에 매핑합니다. 이 분할 유형은 월 또는 회계 분기와 같이 분산될 수 있는 논리적 범위를 가진 데이터를 처리할 때 유용합니다. 데이터가 범위에 균등하게 분산될 때 성능이 가장 좋습니다.        1~100000 => A t/s        200000 이하 => B t/s        300000 이하 => C t/s
해시	해시 분할은 지정된 수의 분할 영역에 데이터를 균등하게 분배하는 방식을 제공합니다. 행은 분할 키의 해시 값에 준하여 분할 영역에 매핑됩니다. 해시 분할 영역을 생성하고 사용하면 I/O 장치에 동일한 크기로 나누어진 분할 영역(분할)을 가짐으로써 가용성과 성능에 영향을 줄 수 있으므로 매우 뛰어난 데이터 배치 튜닝 방식을 얻게 됩니다.       값에 따른 무작위 (같은 값은 같은 테이블에 들어감)
목록	목록 분할을 통해 행이 분할 영역에 매핑되는 방식을 명시적으로 제어할 수 있습니다. 목록 분할은 비연속 값을 따르는 데이터 분포 모델링용으로 설계되었습니다. 목록 분할의 장점은 정렬되지 않거나 관련되지 않은 데이터 집합을 자연스럽게 그룹화하고 조직할 수 있다는 점입니다.      deptno 10  => A      deptno 20  => B      deptno 30  => C
범위-해시	범위-해시 조합 분할은 범위 방식을 사용하여 데이터를 분할하고 각각의 분할 영역 내에서 해시 메소드를 사용하여 하위 분할합니다. 범위-해시 분할 영역은 범위 분할의 향상된 관리성과 해시 분할의 균등한 데이터 분배 및 병렬화 이점을 조합한 것입니다.
범위-목록	범위-목록 조합 분할은 범위 방식을 사용하여 데이터를 분할하고 각각의 분할 영역 내에서 목록 방식을 사용하여 하위 분할합니다. 범위-목록 분할 영역은 범위 분할의 향상된 관리성과 목록 분할의 명시적인 배치 제어를 조합한 것입니다.

   

▣ 범위 방식 (Range)의 Partitioned Table 생성

S SYS> create tablespace a datafile size 10M;

S SYS> create tablespace b datafile size 10M;

S SYS> create tablespace c datafile size 10M; 

S SYS> create tablespace d datafile size 10M;

범위 방식 파티션 테이블 생성 후 show sql

   

CREATE TABLE SCOTT.PT (

    ID NUMBER(10),

    DATA VARCHAR2(30),

    CONSTRAINT PT_PK PRIMARY KEY (ID) VALIDATE )

    PARTITION BY RANGE (ID) (PARTITION PT_P1 VALUES LESS THAN (100000) TABLESPACE A,

    PARTITION PT_P2 VALUES LESS THAN (200000) TABLESPACE B,

    PARTITION PT_P3 VALUES LESS THAN (300000) TABLESPACE C,

    PARTITION PT_P4 VALUES LESS THAN (MAXVALUE) TABLESPACE D)

/

   

Id 1 ~ 10 만 : A T/S

Id 10만 1 ~ 20만 : B T/S

Id 20만 1 ~ 30만 : C T/S

Id 30만 1 ~ ∞ : D T/S

   

▣ Tablespace 사용량 출력 script

SQL> get tsUsage

1 select name,"FILE USED MB","Used MB"

2 from

3 v$tablespace tu

4 join

5 (select tsn,sum(bytes/1024/1024) "Used MB",

6 sum(blocks) "B num"

7 from TBS_SPACE_USAGE group by tsn)

8 on (tsn=tu.ts#)

9 join

10 (select ts#,sum(bytes)/1024/1024 "FILE USED MB"

11 from v$datafile group by ts#) df

12 on (tu.ts#=df.ts#)

13* and name in ('A','B','C','D')

   

▣ 범위 방식 partitioned 테이블 실습

SQL> get testSet

1 drop sequence pts

2 /

3 create sequence pts

4 /

5 create or replace procedure pti(

6 su number,

7 data varchar2 default 'abcdefg')as

8 begin

9 for i in 1..su loop

10 insert into pt values(pts.nextval,data);

11 commit;

12 end loop;

13 end;

14 /

15 exec pti(150000) => id 150000 개 삽입

16 /

17* select count(*) from pt

   

SQL> @testSet

   

▣ 용량 증가 확인 : 데이터를 15만개 넣었기 때문에 B T/S 까지 데이터가 삽입된다.

   

▣ 데이터 추가 및 확인

SQL> exec pti(300000);

   

SQL> select trunc((id-1)/100000),count(*) from pt group by trunc((id-1)/100000);

   

TRUNC((ID-1)/100000) COUNT(*)

-------------------- ----------

1 100000 - b

2 100000 - c

4 50000 - d

3 100000 - d

0 100000 - a

   

▣ Range-list와 list 방식의 Partitioned Table에 사용가능한 데이터

   

▣ RangeList 방식의 Partitioned Table

CREATE TABLE SCOTT.PT2 ( EMPNO NUMBER(10),

 ENAME VARCHAR2(20),

 SAL NUMBER(6),

 DEPTNO NUMBER(2),

 CONSTRAINT PT2_PK PRIMARY KEY (EMPNO) VALIDATE ,

 CONSTRAINT PT2_FK FOREIGN KEY (DEPTNO) REFERENCES SCOTT.DEPT (DEPTNO) VALIDATE )

 TABLESPACE USERS PARTITION BY RANGE (SAL,DEPTNO)

    SUBPARTITION BY LIST (DEPTNO) SUBPARTITION TEMPLATE (

       SUBPARTITION S1 VALUES (10) ,

       SUBPARTITION S2 VALUES (20) ,

       SUBPARTITION S3 VALUES (30) ,

       SUBPARTITION S4 VALUES (DEFAULT) )

    (PARTITION PT2_P1 VALUES LESS THAN (999,MAXVALUE),

     PARTITION PT2_P2 VALUES LESS THAN (1999,MAXVALUE),

     PARTITION PT2_P3 VALUES LESS THAN (2999,MAXVALUE),

     PARTITION PT2_P4 VALUES LESS THAN (MAXVALUE,MAXVALUE)

    )

/

   

   

   

SQL> create sequence pt2s;

   

Sequence created.

   

SQL> insert into pt2 values(pt2s.nextval,'aaa',500,10);

   

1 row created.

   

SQL> begin

2 for i in 1..100000 loop

3 insert into pt2 values(pt2s.nextval,'aaa',500,10);

4 end loop;

5 end;

6 /

   

PL/SQL procedure successfully completed.

   

   

   

   

SQL> alter table pt2 move subpartition pt2_p1_s1 tablespace example;

partition pt2 tablespace users; <= 가능

   

Table altered.

   

   

▣ Range-list by EM

   

   

▣ Show SQL

CREATE TABLE "SCOTT"."PT2" ( "EMPNO" NUMBER(4), "ENAME" VARCHAR2(10), "SAL" NUMBER(7, 2), "DEPTNO" NUMBER(2), CONSTRAINT "PT2_PK" PRIMARY KEY ("EMPNO") VALIDATE , CONSTRAINT "PT2_FK" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") VALIDATE ) TABLESPACE "USERS" PARTITION BY RANGE ("SAL","DEPTNO") SUBPARTITION BY LIST ("DEPTNO") SUBPARTITION TEMPLATE (SUBPARTITION "S1" VALUES (10) , SUBPARTITION "S2" VALUES (20) , SUBPARTITION "S3" VALUES (30) , SUBPARTITION "S4" VALUES (DEFAULT) ) (PARTITION "PT2_P1"

VALUES LESS THAN (999,MAXVALUE), PARTITION "PT2_P2" VALUES LESS THAN (1999,MAXVALUE), PARTITION "PT2_P3" VALUES LESS THAN (2999,MAXVALUE), PARTITION "PT2_P4" VALUES LESS THAN (MAXVALUE,MAXVALUE))

   

   

▣ View Tables