Table 관리

목차

1. Table 관리

1-1 리스트 파티션 테이블 생성과 관리

1-2 레인지 파티션 테이블 생성과 관리

1-3 해시 파티션 테이블 생성과 관리

1-4 추가 실습

 

 

1. Table 관리

1-1 리스트 파티션 테이블 생성과 관리

동시 사용량이 많은 데이터베이스 테이블이 있을 경우 확장성과 가용성은 매우 중요하다. 이러한 경우, 테이블 내의 데이터(행)를 각각 다른 테이블스페이스에 상주하는 여러 분할 영역에 저장할 수 있다. LIST, RANGE, HASH 분할 방식으로 나뉜다.

 

● 사용 명령어

SQL> SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME 
 2   FROM DBA_TAB_PARTITIONS;

- 테이블의 파티션을 조회한다.

- HIGH_VALUE : 리스트 파티션에서는 일치하는 값, 레인지 파티션에서는 상한 값을 나타낸다.

 

SQL> SELECT OWNER, NAME, COLUMN_NAME
 2   FROM DBA_PART_KEY_COLUMNS;

- 파티션의 기준이 되는 컬럼을 조회한다.

 

SQL> CREATE TABLE <table명> (
     ..........
     )
     PARTITION BY LIST (<column명>)
     (
     PARTITION <partition명> VALUES (<column값>) [ TABLESPACE <tablespace 명> ],
     PARTITION <partition명> VALUES (<column값>) [ TABLESPACE <tablespace 명> ],
     ......
     );

- 파티션으로 구현된 테이블을 생성한다.

- LIST 절에 컬럼 명은 여러 개 지정할 수 없다.

- 해당 컬럼의 특정 값을 만족하는 경우 지정된 파티션에 행이 저장되며 해당 파티션은 지정된 테이블스페이스에 저장된다.

- 테이블스페이스를 지정하지 않을 시 테이블 오너의 테이블스페이스에 저장된다.

 

컬럼 값에 지정하지 않은 값은 테이블에 저장되지 않으므로 새 값이 들어오려면 다음과 같이 행을 추가해야 한다.

SQL> ALTER TABLE <table명>
 2   ADD PARTITION <partition명> VALUES (<column값>) [ TABLESPACE <tablespace명> ];

- 테이블에 파티션을 추가한다.

 

SQL> ALTER TABLE <table명>
 2   DROP PARTITION <partition명>;

- 파티션을 삭제한다.

 

SQL> ALTER TABLE <table명>
 2   MOVE PARTITION <partition명> TABLESPACE <tablespace명>;

- 파티션을 이동시킨다.

 

● 실습

- 현재 생성된 테이블스페이스를 확인한다.

 

- 새 테이블스페이스 3개 생성 후 상태를 확인한다.

- st 유저에게 새로 생성한 테이블스페이스에 대해 쿼타를 할당한다.

 

- 기존 st.student 테이블의 구조를 확인한다.

 

- st.student와 동일한 구조로 테이블 생성 후 st.student의 데이터를 그대로 인서트한다. 이 경우 반드시 두 테이블의 구조가 완전히 일치해야 한다.

- using index tablespace indx : 인덱스의 위치를 지정하는 문장으로, 8행의 제약조건으로 생긴 인덱스가 indx 테이블에 만들어진다.  

- 때에 따라 리스트 절은 제약조건과 같은 역할을 하기도 한다.

- 생성된 파티션을 지정하여 읽을 수 없다. 사용자가 st.st 테이블을 읽으면 데이터베이스가 알아서 파티션에서 데이터를 읽어온다.

 

- st.st 테이블에 st.student 테이블의 데이터가 인서트된 것을 확인한다.

 

- st 파티션 테이블을 확인한다.

- 파티션 구분의 기준이 되는 컬럼을 확인한다.

- st 테이블의 파티션 구조를 확인한다.

 

- '사회' 과목 추가를 위해 파티션을 추가한다.

- 기존 '화학' 과목이 있던 파티션을 삭제한다. 해당 데이터 모두 삭제된다.

- 삭제 시 인덱스가 망가지므로 반드시 인덱스 리빌딩 작업을 수행한다.

 

1-2 레인지 파티션 테이블 생성과 관리

컬럼의 정확한 값이 아닌 값의 범위를 기준으로 테이블을 파티션으로 분할할 수 있다.

 

● 사용 명령어

SQL> CREATE TABLE <table명> (
     ..........
     )
     PARTITION BY RANGE (<column명>)
     (
     PARTITION <partition명> VALUES LESS THEN (<상한값>) [ TABLESPACE <tablespace 명> ],
     PARTITION <partition명> VALUES LESS THEN (<상한값>) [ TABLESPACE <tablespace 명> ],
     ......
     );

- 레인지 파티션으로 구현된 테이블을 생성한다.

- VALUES LESS THEN : 상한값을 정의한다. 상한값 미만의 값이 해당 파티션에 저장된다.

 

 SQL> ALTER TABLE <table명>
  2   ADD PARTITION <partition명> VALUES LESS THEN (<상한값>) [ TABLESPACE <tablespace명> ];

- 테이블에 레인지 파티션을 추가한다.

 

● 실습

- p200 파티션은 100이상 200미만의 값을 저장한다.

- pr_max 파티션은 상한값이 MAXVALUE이므로 300이상의 모든 값을 저장한다.

 

- MAXVALUE를 설정한 경우 새로운 파티션을 추가할 수 없다.

- MAXVALUE가 설정된 파티션 삭제 이후 새로운 파티션을 추가한다.

 

- 새로운 실습을 위해 st.sale테이블을 재생성한다.

- 분할 기준 컬럼으로 날짜를 사용할 수 있으며 이러한 경우가 매우 많다.

- 날짜 입력 시 TO_DATE 함수를 반드시 사용한다.

 

 

 

 

 

 

1-3 해시 파티션 테이블 생성과 관리

컬럼의 값을 기준으로 파티션을 분할하지 않고 파티션 개수와 테이블스페이스만 지정하여 데이터베이스가 알아서 파티션을 테이블스페이스에 분배할 수 있다.

 

● 사용 명령어

SQL> CREATE TABLE <table명> (
     ..........
     )
     PARTITION BY HASH (<column명>)
     (
     PARTITIONS <partition 개수> STORE IN (<tablespace명>, <tablespace명>, ...);
     );

- 해시 파티션으로 구현된 테이블을 생성한다.

- PARTITIONS : 생성될 파티션의 개수

- STORE IN : 파티션이 저장될 테이블스페이스 리스트

- 생성될 파티션의 개수와 테이블스페이스의 개수는 다를 수 있다.

 

● 실습

- 8개의 파티션이 t1, t2, t3 테이블스페이스에 알아서 분배되는 테이블스페이스 st.st1을 생성한다.

- 파티션이 자동으로 테이블스페이스에 분배된 것을 확인한다.

 

1-4 추가 실습

기존 st.st 테이블 삭제 후 다음과 같이 재생성한다.

[조건]	
st 테이블 생성
avr 컬럼 기준으로 range 분할

<partition>	<range>
a_1		1점 미만
a_2		2점 미만
a_max		이외 모두
   
   
[실행 문장]
SQL> CREATE TABLE st.st (
 2   sno varchar2(8),
 3   sname varchar2(20),
 4   sex varchar2(4),
 5   syear number,
 6   major varchar2(20),
 7   avr number(4,2),
 8   constraint st_sno_pk primary key(sno)
 9   using index tablespace indx
10   )
11   partition by range (avr)
12   (
13   partition a_1 VALUES LESS THAN (1),
14   partition a_2 VALUES LESS THAN (2),
15   partition a_max VALUES LESS THAN (MAXVALUE));

 

 

1-2 레인지 파티션 테이블 생성과 관리 에서 파티션을 추가하기 위해 MAXVALUE가 설정된 파티션 삭제 이후 새로운 파티션을 추가했으나, 이 경우 해당 파티션의 데이터가 모두 삭제되므로 올바른 방법이라고 할 수 없다.

기존의 데이터를 보존하기 위해 다음과 같은 과정을 통해 파티션을 추가한다.

 

① a_max 파티션의 데이터를 백업한 st_max 테이블 생성

② a_max 파티션 삭제

③ a_3 파티션 추가

④ a_max 파티션 추가

⑤ st 테이블에 st_max 테이블의 데이터 삽입

CREATE TABLE st_max 
as select * from st where avr >= 2;

alter table st
drop partition a_max;

alter table st
add partition a_3 values less than (3);

alter table st
add partition a_max values less than (maxvalue);

ALTER INDEX st_sno_pk REBUILD;

insert into st 
select * from st_max

commit;