ORACLE 9i PARTITIONED TABLE
from OTN http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm#23472
from OCMKOREA ( 네이버카페 )
from DBMS_ROWID http://www.psoug.org/reference/dbms_rowid.html
1. PARTITIONED TABLE
A.
필요성
i.
VLDB (
VERY LARGE DATABASE ) 의 대두로 유지보수의 용이성을 위해 TABLE 의 몸집을 줄일 필요가 있었다. 물리적으로 나뉘어진 이
파트를 파티션이라 한다.
ii.
파티션은 패러렐 쿼리에 대해 병렬처리의
기반을 제공한다.
B.
특징
i.
테이블 혹은 인덱스의 (서브) 파티션은 논리적인 특징을 공유한다. 이를 테면 테이블을 구성하고 있는 컬럼이라던가 이에대한 제약키 등이 있고 인덱스의 경우 생성시 사용하는 각종
옵션 등등을 공유한다.
ii.
파티션간에는 물리적으로 다른 특징을
가진다. 이는 PARTITIONED TABLE 생성시 사용하는 TABLESPACE 구문에서 단적으로 나타난다.
C.
장점
i.
다중의 파티션을 사용해 데이터 훼손
가능성을 줄인다.
ii.
BACKUP
& RECOVER 를 각 파티션별로 독립적으로 실시 가능하다.
iii.
파티션에서 디스크로의 맵핑 ( BALANCING I/O )
iv.
가용성과 성능향상 ( 이 기법으로 핫블록 문제를 해결할 수도 있다. )
2. PARTITIONED TABLE 기법
A.
RANGE
PARTITIONING
CREATE TABLE sales
PARTITION BY RANGE (sale_year, sale_month,
sale_day)
( PARTITION sales_q1 VALUES LESS THAN
(1999, 04, 01)
… (중략) …
값을 가지고 범위를 확실히 나눌수 있는 경우 사용한다. 대표적인
예로 1년 12달을 분기별로 나누는 방법이 이에 해당한다.
(Q1 : 1~3월 , Q2 : 4~6월, Q3 : 7~9월, Q4 : 10~12월)
B.
HASH
PARTITIONING
CREATE TABLE scubagear(id NUMBER,
name VARCHAR2 (60))
PARTITION BY HASH (id)
PARTITIONS 4
STORE IN (gear1, gear2, gear3, gear4);
HASH PARTITIONING 은 RANGE 와 달리 KEY로 사용하는 컬럼이 범위로 나누기에 부적합하며 성능이나 관리상 파티셔닝이 필요한 경우 사용된다. 특징으로 데이터를 정의된 파티션 저장공간에 동등히 분할배치한다는 것이다. 이러한 점 때문에 STRIPING에 같이 활용된다.
C.
LIST
PARTITIONING
CREATE TABLE q1_sales_by_region
PARTITION BY LIST (state)
(PARTITION q1_northwest VALUES ('OR', 'WA'),
… (중략) …
이 메소드는 KEY로 사용할 컬럼 값이 불연속적 ( ex : 문자열 )인 경우 사용한다.
D.
COMPOSITE
RANGE-HASH PARTITIONING
CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER)PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)
SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
RANGE 와 HASH 방식을 같이 접목한 방법이다. /*+ 특징에 대해 조사 */
E.
COMPOSITE
RANGE-LIST PARTITIONING
CREATE TABLE quarterly_regional_sales(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
TABLESPACE ts4
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
… ( 중략 ) …
),PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
… ( 중략 ) …
두가지 레벨로 파티셔닝한다. 처음은 RANGE로 두번째는 LIST로 시행한다. 이러한 방법을 사용하는 예로 historical data가 있다. ( 연대(range) + 분류 (list) ) 3. 예제
A.
PARTITIONED
TABLE 생성
i.
저장할 tablespace 생성
SQL> get cre_tab
1 accept tab prompt "insert
a tablespace name you want"
2 create tablespace &tab
3 datafile
'/opt/oracle/oradata/PROD/data3/&tab.dbf' size 10m
4 autoextend on next 1m maxsize
100m
5 extent management local uniform
size 20k
6*
segment space management auto
7
위에서 만든 스크립트로 tb1 ~ tb4 4개의 테이블 스페이스를
생성하였다.
ii.
PARTITIONED
TABLE을 생성 ( 우편번호를 사용했다.)
샘플로 사용한 우편번호는 우측과 같은 범주의 값을 가졌다. 20만 ~ 90만
두값의 간격은 약 70만 정도이며 4개의 테이블 스페이스에 20만 ~
40만, 41만 ~ 60만, 61만 ~ 80만, 81만 ~ 100만 을 넣기로 하였다.
SQL> ED
file afiedt.buf(이)가 기록되었습니다
1
CREATE TABLE ADDRESS
2 ( addr number, num number)
3 PARTITION BY RANGE ( addr )
4 ( PARTITION TB1 VALUES LESS
THAN ( 400000 )
5 TABLESPACE TB1,
6 PARTITION TB2 VALUES LESS THAN
( 600000 )
7 TABLESPACE TB2,
8 PARTITION TB3 VALUES LESS THAN
( 800000 )
9 TABLESPACE TB3,
10
PARTITION TB4 VALUES LESS THAN ( 1000000 )
11
TABLESPACE TB4)
12*
ENABLE ROW MOVEMENT
테이블이 생성되었습니다.
B.
CONTROL
FILE 생성 / 실행
i.
CONTROL
FILE의 생성
<< addr.par 의 내용>>
LOAD DATA
INFILE 'C:\ADDR.csv'
APPEND
INTO TABLE ADDRESS
FIELDS TERMINATED BY ','
(ADDR,NUM)
ii.
CONTROL
FILE 의 실행
C:\>sqlldr userid=example/example@prod
control='c:\addr.par'
SQL*Loader: Release 10.2.0.1.0 - Production
on 수 1월 28 09:28:47 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 64
커밋 시점에 도달 - 논리 레코드 개수 128
… ( 중략 ) …
커밋 시점에 도달 - 논리 레코드 개수 50383
커밋 시점에 도달 - 논리 레코드 개수 50416
C.
저장공간 확인
1 select
dbms_rowid.rowid_to_absolute_fno(rowid,'EXAMPLE','ADDRESS') ABSOLUTE_FNO,
2
addr,num
3 from
address
4* where addr in (210600,627854)
ABSOLUTE_FNO ADDR NUM
------------ ---------- ----------
7 210600 6
9 627854 1
9 627854 11
9 627854 21
CREATE TABLE 에서 파티션 조건을 보면 40만, 60만, 80만, 100만으로 사용하고 있다. 위 쿼리에서 조건으로 사용한 것이 21만 62만 정도의 위치로 TB1, TB3를 사용할 것이다. DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO ( ROWID를 물리적인 파일과 매칭시켜준다. )를 사용해 이를 실지로 확인해보면 아래와 같다.
FILE_NAME FILE_ID
TABLESPACE_NAME
---------------------------------------------
-------- ---------------
/opt/oracle/oradata/PROD/data1/system01.dbf 1 SYSTEM
/opt/oracle/oradata/PROD/data2/undo01.dbf 2 UNDO
/opt/oracle/oradata/PROD/data3/user01.dbf 3 USERS
/opt/oracle/oradata/PROD/data3/tools01.dbf 4 TOOLS
/opt/oracle/oradata/PROD/data3/show01.dbf 5 SHOW
/opt/oracle/oradata/PROD/data3/example01.dbf 6 EXAMPLE
/opt/oracle/oradata/PROD/data3/tb1dbf 7 TB1
/opt/oracle/oradata/PROD/data3/tb2dbf 8 TB2
/opt/oracle/oradata/PROD/data3/tb3dbf 9 TB3
/opt/oracle/oradata/PROD/data3/tb4dbf 10 TB4
10 개의 행이
선택되었습니다.
7번이 TB1 9번이 TB3로 파티셔닝 조건으로 사용했던 것과 일치한다. 이로써 조건으로 사용한 컬럼의 값에 따라 값이 나뉘는 것을 확인하였다.
/*+ ROWID 를 읽을 수 있는 값으로 변환하는 방법과 그 결과들 */
