#title 분할기능 [[TableOfContents]] ==== 개요 ==== 오라클 8 버전이상에서는 분할기능을 제공합니다. 분할기능은 테이블 또는 인덱스를 여러개의 테이블스페이스에 나누어 저장하는 기능입니다. 분할의 장점은 당근 성능에 있습니다. 여러 테이블스페이스로 분할하여 저장하게 되면 그만큼 처리의 범위가 줄어 들기 때문에 성능이 올라가는 것은 당연합니다. 테이블스페이스에 속한 데이타 파일들이 여러 디스크로 분산되어 있다면 더할나위 없이 좋겠지요. 책 1000페이지 짜리에서 A라는 것을 찾는 것과 1000페이지중 500페이지로 반 나누어서 1~500페이지중 A를 찾아라 하는 것은 놀라운 효과를 가지지요. 물론 A라는 것은 1페이지에 있다면 1000페이지나 500페이지나 같겠지만 이것은 일부의 경우지요. 암튼 처리 대상의 범위를 줄여준다는 것은 아주 중요한 메커니즘이지요. 저는 첨에 분할기능을 접하고 엄청난 감동을 먹었슴다. 아주 멋진 기능이지요. 부가적으로 분할을 하게되면 문제의 발생에 대비해서도 좋습니다. 여러 테이블스페이스에 분할 하여 저장하였는데 특정 테이블스페이스가 문제가 생기면 문제가 발생한 테이블스페이스의 정보만 사용할 수 없고 나머지는 사용할 수 있다는 것입니다. 또한 나누어져 있기때문에 관리자가 관리하기도 다소 쉽겠지요. 분할은 최대 64000개 까지 분할 할 수 있으며, 분할이 안됀 테이블에 대해서 인덱스를 생성할 때 인덱스만 따로 분할 할 수 있습니다. 단, 분할이 안됀 테이블의 비트맵 인덱스는 분할할 수 없습니다. 테이블이 커지면 인덱스의 크기도 커지기 때문에 분할은 장점은 너무도 많습니다. 분할은 크게 단순분할과 복합분할로 나뉩니다. 단순분할은 다시 범위분할과 해쉬분할로 나뉩니다. 먼저 단순분할부터 알아보도록 하겠습니다...역쉬 문법은 메뉴얼을..^^; ==== 단순분할 ==== 바로 실습에 들어가도록 하지여. 먼저 5개의 분할을 하도록 결정을 하고 각각의 분할에 들어갈 테이블스페이스를 생성합니다. 물론 5개로 분할하기로 했다면 어떤 기준이 정했겠지여. {{{ SQL> connect system/manager 연결되었습니다. SQL> create tablespace data_199901 2 datafile 'K:\oracle_tablespace\data_199901.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create tablespace data_199902 2 datafile 'K:\oracle_tablespace\data_199902.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create tablespace data_199903 2 datafile 'K:\oracle_tablespace\data_199903.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create tablespace data_199904 2 datafile 'K:\oracle_tablespace\data_199904.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create tablespace data_maxvalues -------------> 기준에 맞지 않는 것을 모두 이 테이블스페이스에 때려 넣는다.. 2 datafile 'K:\oracle_tablespace\data_maxvalues.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create table psales( 2 year char(4), 3 mon char(2), 4 dd char(2), 5 empno char(3), 6 count number(5), 7 amount number(8)) 8 partition by range (year, mon) ---------------> 여기에서 분할 기준이 되는 컬럼을 명시해 준다. 9 (partition p1 values less than ('1999', '01') 10 tablespace data_199902, 11 partition p2 values less than ('1999', '02') 12 tablespace data_199903, 13 partition p3 values less than ('1999', '03') 14 tablespace data_199904, 15 partition p4 values less than ('1999','04') 16 tablespace data_199905, 17 partition p5 values less than (maxvalue, maxvalue) ------------> 분할 기준에서 벗어나는 것은 이 테이블스페이스에 저장한다...에러방지.. 18 tablespace data_maxvalues); 테이블이 생성되었습니다. SQL> insert into psales values ('1999', '01', '21', '111', 1111, 12345678); 1 개의 행이 만들어졌습니다. SQL> insert into psales values ('1999', '02', '1', '222', 22222, 12345678); 1 개의 행이 만들어졌습니다. SQL> insert into psales values ('1999', '03', '30', '333', 33333, 12345678); 1 개의 행이 만들어졌습니다. SQL> insert into psales values ('1999', '04', '22', '444', 44444, 12345678); 1 개의 행이 만들어졌습니다. SQL> insert into psales values ('1999', '12', '31', '012', 01212, 45678901); 1 개의 행이 만들어졌습니다. SQL> select table_name, partition_name, partition_position, tablespace_name -----------> 생성한 범위 분할에 대한 데이타 사전 검색... 2 from user_tab_partitions 3 where table_name = 'PSALES'; TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME ------------------------------ ------------------------------ ------------------ ------------------- PSALES P1 1 DATA_199901 PSALES P2 2 DATA_199902 PSALES P3 3 DATA_199903 PSALES P4 4 DATA_199904 PSALES P5 5 DATA_MAXVALUES }}} 분할에서 8.x 버전에서는 갱신할때 분할키 컬럼의 데이타를 변경할 수 없습니다. 그러나 8i 버전에는 변경이 가능합니다....데이타를 변경할때는 alter table 문을 먼저 수행해야 합니다. 다음은 그 예입니다. {{{ SQL> alter table psales enable row movement; ---------------> 분할키에 대한 데이타를 변경할 수 있게 테이블을 변경한다. 테이블이 변경되었습니다. SQL> update psales 2 set year = '2001' 3 where empno = '012'; 1 행이 갱신되었습니다. SQL> select * from psales; YEAR MO DD EMP COUNT AMOUNT ---- -- -- --- ---------- ---------- 1999 01 21 111 1111 12345678 1999 02 1 222 22222 12345678 1999 03 30 333 33333 12345678 1999 04 22 444 44444 12345678 2001 12 31 012 1212 45678901 ------------> 분할키가 '1999'에서 '2001'로 변경되었다.. SQL> create table gsales 2 (no number(5), 3 person varchar2(30), 4 amount number(8), 5 week number(2)) 6 partition by range (week) 7 (partition p1 values less than (4), 8 partition p2 values less than (8), 9 partition p3 values less than (12)); 테이블이 생성되었습니다. SQL> create index gsales_gidx on gsales (week, no) 2 global 3 partition by range (week) 4 (partition g_p1 values less than (8), 5 partition g_p2 values less than (maxvalue)); 인덱스가 생성되었습니다. }}} 다음은 범위분할을 사용할때 고려해야 될 사항들임돠.. * 분할키는 최대 16개까지 * long, long raw 데이타 타입에는 분할기능을 사용할 수 없다. * values less than 절에 정의된 분할 기준값은 정의된 값보다 적은 값을 저장하게 된다. * 분할 기준 컬럼의 널값은 values less than 절에 maxvalue에 해당된다. ==== 해쉬분할 ==== 단순분할의 두번째인 해쉬분할입니다...해쉬분할은 오라클의 해쉬알고리즘에 의해서 오라클에서 자동적으로 나누어지게 하는 방법입니다. 물론 오라클이 판단하기에 최적이라고 판단되는 분할을 하게됩니다. 그것이 실제로 최적이 될지는 모르는 것입니다..이것은 관리자가 성능에 대한 주기적인 모니터링을 통해서 알아야 겠지여. 해쉬 알고리즘에 의해서 분할을 하게 되면 훨씬 분포도가 좋을 수 있습니다. 보통은 2,4,8, 16, 32.......... 단위로 분할을 하게됩니다. 해쉬분할의 문법은 역쉬..메뉴얼을 참고하시구여. 예를 보겠습니다. {{{ SQL> create tablespace tbs1 2 datafile 'k:\oracle_tablespace\tbs1.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create tablespace tbs2 2 datafile 'k:\oracle_tablespace\tbs2.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create tablespace tbs3 2 datafile 'k:\oracle_tablespace\tbs3.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create table product1( -----------> 해쉬분할 방법1 2 id number(3), 3 name varchar2(30) 4 ) 5 partition by hash(id) partitions 8 6 store in (tbs1, tbs2, tbs3); 테이블이 생성되었습니다. SQL> create table product2( -------------> 해쉬분할 방법2 2 id number(3), 3 name varchar2(30) 4 ) 5 partition by hash(id) 6 (partition p1 tablespace tbs1, 7 partition p2 tablespace tbs2, 8 partition p3 tablespace tbs3); 테이블이 생성되었습니다. }}} 일반적인 SQL문(select, insert, update, delete)을 사용해서 분할 테이블을 사용할 때는 다음과 같이 분할명을 명시해 줍니다. {{{ SQL> select * from psales partition(p5); YEAR MO DD EMP COUNT AMOUNT ---- -- -- --- ---------- ---------- 1999 04 22 444 44444 12345678 2001 12 31 012 1212 45678901 SQL> insert into product2 values (123, 'yasi'); 1 개의 행이 만들어졌습니다. SQL> select * from product2 partition(p2); 선택된 레코드가 없습니다. SQL> select * from product2 partition(p1); 선택된 레코드가 없습니다. SQL> select * from product2 partition(p3); ID NAME ---------- ------------------------------ 123 yasi }}} 관련 자료사전. * DBA_PART_TABLES * DBA_TAB_PARTITIONS * DBA_PART_KEY_COLUMNS * DBA_TABLES * DBA_OBJECTS * DBA_SEGMENTS 분할 방법1은 8개의 분할 구조를 갖게되고, 분할 방법2 는 오라클이 알아서 최적의 분할 구조를 만듭니다.. ==== 인덱스 분할 ==== 범위분할중...인덱스를 분할하는 방법에 대해서 살펴보겠습니다. 오라클은 8.0.X 버전부터 4가지 종류의 분할 인덱스를 제공합니다. 구조적 측면과 분할키 사용을 기준으로 나눕니다. 구조적 측면 * 글로벌 인덱스 : 테이블과 인덱스의 분할구조가 서로 다른 유형의 인덱스 * 로컬 인덱스 : 테이블과 인덱스의 분할구조가 서로 같은 유형의 인덱스 분할키 사용 기준 * 프리픽스드 인덱스 : 인덱스를 생성할 때 기준으로 하는 컬럼을 분할키로 사용한 인덱스 * 논-프리픽스드 인덱스 : 테이블과 인덱스의 분할구조가 서로 같으 유형의 인덱스 그럼 생성해 보겠습니다. 먼저 인덱스를 위한 테이블스페이스를 생성후 앞서서 생성한 테이블에 대한 인덱스를 생성하겠습니다. {{{ SQL> create tablespace idx_t1 2 datafile 'K:\oracle_tablespace\idx_t1.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create tablespace idx_t2 2 datafile 'K:\oracle_tablespace\idx_t2.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create tablespace idx_t3 2 datafile 'K:\oracle_tablespace\idx_t3.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create tablespace idx_t4 2 datafile 'K:\oracle_tablespace\idx_t4.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create tablespace idx_t5 2 datafile 'K:\oracle_tablespace\idx_t5.dbf' size 500k; 테이블 영역이 생성되었습니다. SQL> create index idx_psales_year_mon -----------------------> psales 테이블에 대한 인덱스 생성... 2 on psales(year, mon) 3 global 4 partition by range (year, mon) 5 (partition idx_p1 values less than ('1999', '02') tablespace idx_t1, 6 partition idx_p2 values less than ('1999', '03') tablespace idx_t2, 7 partition idx_p3 values less than ('1999', '04') tablespace idx_t3, 8 partition idx_p4 values less than ('1999', '05') tablespace idx_t4, 9 partition idx_p5 values less than (maxvalue, maxvalue) tablespace idx_t5); 인덱스가 생성되었습니다. SQL> drop index idx_psales_year_mon; 인덱스가 삭제되었습니다. SQL> create index idx_psales_year_mon 2 on psales(year, mon) 3 local 4 (partition p1 tablespace idx_t1, ------> psales 테이블에서 나눈 파티션 명과 인덱스 파티션 테이블스페이스의 분할 구조가 같다.. 5 partition p2 tablespace idx_t2, 6 partition p3 tablespace idx_t3, 7 partition p4 tablespace idx_t4, 8 partition p5 tablespace idx_t5); 인덱스가 생성되었습니다. }}} 다음은 글러벌 프리픽스 인덱스 분할 방법에 대해서 살펴보겠습니다. 글로벌 프리픽스 인덱스는 테이블 분할구조와 인덱스 분할구조가 서로 다르지여. 인덱스 생성시 기준이 되는 컬럼이 분할키로 사용되는 인덱스를 의미합니다. {{{ SQL> create table gpsales 2 (no number(5), 3 person varchar2(30), 4 amount number(8), 5 week number(2)) 6 partition by range (week) 7 (partition p1 values less than (4), 8 partition p2 values less than (8), 9 partition p3 values less than (12)); 테이블이 생성되었습니다. SQL> create index gpsales_idx 2 on gpsales(week, no) 3 global 4 partition by range (week) 5 (partition g_p1 values less than(8), 6 partition g_p2 values less than(maxvalue)); 인덱스가 생성되었습니다. }}} 다음은 로컬 프리픽스 인덱스에 대해서 살펴봅니다. 로컬 프리픽스 인덱스는 테이블과 인덱스가 같은 구조를 가지고 있으며 인덱스 생성시 기준이되는 컬럼이 분할키로 사용되는 인덱스 분할 방식입니다. {{{ SQL> create table lssales 2 (no number(5), 3 person varchar2(30), 4 amount number(8), 5 week number(2)) 6 partition by range (week) 7 (partition p1 values less than (4), 8 partition p2 values less than (8)); 테이블이 생성되었습니다. SQL> create index lssales_idx on lssales(week, no) 2 local 3 (partition g_p1 tablespace idx_t1, 4 partition g_p2 tablespace idx_t2); 인덱스가 생성되었습니다. }}} 다음은 인덱스 사용에 대한 로드맵입니다... * 로컬 프리픽스 인덱스 * 인덱스 생성시 사용되는 컬럼이 분할 테이블 생성시 기준이 되는 컬럼 * 글러벌 프리픽스 인덱스 * 컬럼이 분할에 기준이 되지 않고, 유일한 인덱스 일 경우 사용 * 의사 결정 업무(DSS)에 적용될 때 * 로컬 논-프리픽스 인덱스 * 검색시 실행속도가 매우 중요할 때 사용 * OLTP업무에 사용 다음은 분할 객체에 대한 관리상의 방법을 알아보겠습니다. 즉, alter table문을 이용해서 분할 객체를 관리합니다. 문법은 메뉴얼을 참고하시구여. {{{ SQL> alter table psales 2 drop partition p1; --------> psales 테이블에 p1 파티션을 삭제합니다.. 테이블이 변경되었습니다. SQL> alter index idx_psales_year_mon 2 drop partition p1; alter index idx_psales_year_mon --------> 앞에서 우리는 psales 테이블에 대한 글로벌 인덱스를 drop 했었슴다...즉, 이런 방식은 글로벌 인덱스에만 적용되는 것입니다.. * 1행에 오류: ORA-14076: DROP/SPLIT PARTITION 은 LOCAL 인덱스 분할영역으로 사용될 수 없습니다 SQL> alter table psales 2 rename partition p2 to g2; -------------> 테이블의 분할영역에 대한 이름 변경방법 테이블이 변경되었습니다. SQL> alter index idx_psales_year_mon 2 rename partition p2 to g2; ---------------> 인덱스의 분할영역에 대한 이름 변경방법 인덱스가 변경되었습니다. SQL> alter table gsales add partition p4 -------------> 테이블의 새로운 범위추가 2 values less than (16) tablespace data_199903; 테이블이 변경되었습니다. SQL> alter table product1 add partition; ----------> 해쉬분할 테이블에 대한 새로운 범위 추가 테이블이 변경되었습니다. SQL> alter table product2 add partition p4 tablespace tbs3; ----------> 해쉬분할 테이블에 대한 새로운 범위 추가 테이블이 변경되었습니다. SQL> alter table psales 2 merge partitions g2, p3 into partition p2_3; -----------> 분할영역 g2와 p3를 하나의 파티션으로 합쳐 p2_3로 만든다.. 테이블이 변경되었습니다. SQL> alter table product1 coalesce partition; -----------> 분할의 개수가 홀수개이면 검색속도가 나빠질수 있는데...짝수로 만들어 주는 문장.. 테이블이 변경되었습니다. SQL> alter table gsales 2 modify partition p3 storage (maxextents 50); ---------> 파티션에 대한 변경 테이블이 변경되었습니다. SQL> alter table psales 2 modify partition p4 unusable local indexes; ------------> 로컬 인덱스를 사용하지 못하게 합니다.. 테이블이 변경되었습니다. ################################################################################ # unusable된 인덱스는 alter session set skip_unusable_indexes = true; 문장을 # # 실행해야지만 insert, update, delete문을 실행 할수 있슴다. # ################################################################################ SQL> alter index idx_psales_year_mon 2 rebuild partition p5 tablespace tbs3; ---------------> 인덱스의 재생성 인덱스가 변경되었습니다. SQL> select table_name, partition_name, partition_position, tablespace_name 2 from user_tab_partitions 3 where table_name = 'PSALES'; TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME ------------------------------ ------------------------------ ------------------ ------------------- PSALES P4 2 DATA_199904 PSALES P5 3 DATA_MAXVALUES PSALES P2_3 1 SYSTEM SQL> alter table sales 2 merge partitions p1, p2 into partition p12; 테이블이 변경되었습니다. SQL> alter table sales split partition p12 2 at ('1999', '02') ---------------> 분할기준이 되는 정보가 너무 큰값이다..즉, 1999년 02 년도까지의 정보를 저장하느데 1999년 02월을 기준으로 나누려 하기때문에 에러.. 3 into (partition p21 tablespace tbs2, 4 partition p22 tablespace tbs3); alter table sales split partition p12 * 1행에 오류: ORA-14080: 분할영역은 지정된 높은 바운드에 따라 나누어질 수 없습니다 SQL> alter table sales split partition p12 2 at ('1999', '01') 3 into (partition p21 tablespace tbs2, 4 partition p22 tablespace tbs3); 테이블이 변경되었습니다. }}} ==== 분할와이즈 조인 ==== 분할 와이즈 조인방식은 조인되는 테이블이 분할되어 있다면 분할된 영영만 읽어들여 검색하는 방식을 말합니다. 두가지 방식이 있습니다. * 전체 분할 와이즈 조인 : 조인할 때 테이블이 모두 분할되어 있는 경우의 조인 * 부분 분할 와이즈 조인 : 조인할 때 테이블의 일부가 분할되어 있고 일부는 분할이 없는 조인 방법 {{{ SQL> alter session set nls_date_format = 'DD-MON-YYYY'; 세션이 변경되었습니다. SQL> create table orders 2 (ordid number, 3 orderdate date, 4 prductid number, 5 quant number) 6 partition by range (orderdate) 7 subpartition by hash(prductid) subpartitions 8 8 store in (tbs1, tbs2, tbs3) 9 (partition q1 values less than ('01-APR-1999'), 10 partition q2 values less than ('02-APR-1999'), 11 partition q3 values less than ('03-APR-1999'), 12 partition q4 values less than ('04-APR-1999')); create table orders * 1행에 오류: ORA-01658: TBS3 테이블스페이스에 세그먼트에 대한 INITIAL 확장 영역을 작성할 수 없습니다 --------->테이블스페이스 영역의 크기를 늘려줘야 합니다.. SQL> alter tablespace tbs3 2 add datafile 'k:\oracle_tablespace/tbs3_2.dbf' size 500k; 테이블 영역이 변경되었습니다. SQL> create table orders 2 (ordid number, 3 orderdate date, 4 prductid number, 5 quant number) 6 partition by range (orderdate) 7 subpartition by hash(prductid) subpartitions 8 8 store in (tbs1, tbs2, tbs3) 9 (partition q1 values less than ('01-APR-1999'), 10 partition q2 values less than ('02-APR-1999'), 11 partition q3 values less than ('03-APR-1999'), 12 partition q4 values less than ('04-APR-1999')); create table orders * 1행에 오류: ORA-01658: TBS2 테이블스페이스에 세그먼트에 대한 INITIAL 확장 영역을 작성할 수 없습니다 SQL> alter tablespace tbs2 2 add datafile 'k:\oracle_tablespace/tbs2_2.dbf' size 500k; 테이블 영역이 변경되었습니다. SQL> alter tablespace tbs1 2 add datafile 'k:\oracle_tablespace/tbs1_2.dbf' size 500k; 테이블 영역이 변경되었습니다. SQL> create table orders 2 (ordid number, 3 orderdate date, 4 prductid number, 5 quant number) 6 partition by range (orderdate) 7 subpartition by hash(prductid) subpartitions 8 8 store in (tbs1, tbs2, tbs3) 9 (partition q1 values less than ('01-APR-1999'), 10 partition q2 values less than ('02-APR-1999'), 11 partition q3 values less than ('03-APR-1999'), 12 partition q4 values less than ('04-APR-1999')); create table orders * 1행에 오류: ORA-01658: TBS2 테이블스페이스에 세그먼트에 대한 INITIAL 확장 영역을 작성할 수 없습니다 SQL> alter tablespace tbs2 2 add datafile 'k:\oracle_tablespace/tbs2_3.dbf' size 1M; 테이블 영역이 변경되었습니다. SQL> create table orders 2 (ordid number, 3 orderdate date, 4 prductid number, 5 quant number) 6 partition by range (orderdate) 7 subpartition by hash(prductid) subpartitions 8 8 store in (tbs1, tbs2, tbs3) 9 (partition q1 values less than ('01-APR-1999'), 10 partition q2 values less than ('02-APR-1999'), 11 partition q3 values less than ('03-APR-1999'), 12 partition q4 values less than ('04-APR-1999')); create table orders * 1행에 오류: ORA-01658: TBS1 테이블스페이스에 세그먼트에 대한 INITIAL 확장 영역을 작성할 수 없습니다 ---------------> ㅡㅡ; 짜증남니다... SQL> alter tablespace tbs1 2 add datafile 'k:\oracle_tablespace/tbs1_3.dbf' size 1M; 테이블 영역이 변경되었습니다. SQL> create table orders 2 (ordid number, 3 orderdate date, 4 prductid number, 5 quant number) 6 partition by range (orderdate) 7 subpartition by hash(prductid) subpartitions 8 8 store in (tbs1, tbs2, tbs3) 9 (partition q1 values less than ('01-APR-1999'), 10 partition q2 values less than ('02-APR-1999'), 11 partition q3 values less than ('03-APR-1999'), 12 partition q4 values less than ('04-APR-1999')); create table orders * 1행에 오류: ORA-01658: TBS3 테이블스페이스에 세그먼트에 대한 INITIAL 확장 영역을 작성할 수 없습니다 SQL> alter tablespace tbs3 2 add datafile 'k:\oracle_tablespace/tbs3_3.dbf' size 1M; 테이블 영역이 변경되었습니다. SQL> create table orders 2 (ordid number, 3 orderdate date, 4 productid number, 5 quant number) 6 partition by range (orderdate) 7 subpartition by hash(productid) subpartitions 8 8 store in (tbs1, tbs2, tbs3) 9 (partition q1 values less than ('01-APR-1999'), 10 partition q2 values less than ('02-APR-1999'), 11 partition q3 values less than ('03-APR-1999'), 12 partition q4 values less than ('04-APR-1999')); 테이블이 생성되었습니다. SQL> create table product 2 (productid number, 3 name varchar2(60)) 4 partition by hash (productid) partitions 8 5 store in (tbs1, tbs2, tbs3); 테이블이 생성되었습니다. SQL> @E:\oracle\ora90\rdbms\admin\utlxplan.sql 테이블이 생성되었습니다. SQL> set autotrace on SQL> select * from orders o, product p 2 where o.productid = p.productid; ----------------> 전체 분할 와이즈 조인... 선택된 레코드가 없습니다. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=93) 1 0 PARTITION HASH (ALL) -----------> 전체를 뜻하는 ALL 2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=93) 3 2 PARTITION RANGE (ALL) 4 3 TABLE ACCESS (FULL) OF 'ORDERS' (Cost=1 Card=1 Bytes=48) 5 2 TABLE ACCESS (FULL) OF 'PRODUCT' (Cost=1 Card=328 Bytes=14760) }}} 실행 계획설명을 하자면. 1. orders테이블을 full 스캔합니다.(물론 분할된 범위입니다.) 2. product테이블을 full 스캔합니다.(물론 여기도 당연히 분할된 범위안에서 입니다.) 3. 조인합니다. {{{ Statistics ---------------------------------------------------------- 122 recursive calls 96 db block gets 12 consistent gets 0 physical reads 0 redo size 501 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed }}} ==== 분할된 객체의 익스포트/임포트 ==== exp 사용자명/패스워드 table = (테이블명:분할명) imp 사용자명/패스워드 table = (테이블명:분할명) (예) exp system/manager tables=(emp, psales:p1, psales:p2) imp system/manager file=expdat.dmp tables=(psales:p1, psales:p2) ==== 복합분할 ==== 복합분할은 앞에서 살펴봤던 범위분할과 해쉬분할의 결합된 형태를 말합니다. 2가지 분할방법의 장점을 모두사용할 수 있어 매우 효과적이라는데 저는 좀 어렵네여..헐헐~ 바로 예로 들어가지여. 제일 먼저 볼 예제는 범위분할을 하고 그 범위분할 내에서 또다시 해쉬분할을 하는 예제입니다. {{{ SQL> alter session set nls_date_format = 'dd-mon-yyyy'; 세션이 변경되었습니다. SQL> create table orders 2 (ordid number, 3 orderdate date, 4 productid number, 5 quant number) 6 partition by range (orderdate) 7 subpartition by hash(productid) subpartitions 4 8 store in (tbs1, tbs2, tbs3) 9 (partition q1 values less than ('01-APR-1999'), 10 partition q2 values less than ('02-APR-1999'), 11 partition q3 values less than ('03-APR-1999'), 12 partition q4 values less than ('04-APR-1999')); 테이블이 생성되었습니다. SQL> select table_name,partition_name,subpartition_count, partition_position, tablespace_name 2 from user_tab_partitions 3 where table_name = 'ORDERS'; TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT PARTITION_POSITION TABLESPACE_NAME ------------------------------ ------------------------------ ------------------ ------------------ --------------- ORDERS Q1 4 1 USERS ORDERS Q2 4 2 USERS ORDERS Q3 4 3 USERS ORDERS Q4 4 4 USERS SQL> select rownum,partition_name, segment_type, tablespace_name 2 from dba_segments 3 where owner = 'YASI' and segment_name = 'ORDERS'; ROWNUM PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME ---------- ------------------------------ ------------------ ------------------------------ 1 SYS_SUBP369 TABLE SUBPARTITION TBS1 2 SYS_SUBP370 TABLE SUBPARTITION TBS2 3 SYS_SUBP371 TABLE SUBPARTITION TBS3 4 SYS_SUBP372 TABLE SUBPARTITION TBS1 5 SYS_SUBP373 TABLE SUBPARTITION TBS1 6 SYS_SUBP374 TABLE SUBPARTITION TBS2 7 SYS_SUBP375 TABLE SUBPARTITION TBS3 8 SYS_SUBP376 TABLE SUBPARTITION TBS1 9 SYS_SUBP377 TABLE SUBPARTITION TBS1 10 SYS_SUBP378 TABLE SUBPARTITION TBS2 11 SYS_SUBP379 TABLE SUBPARTITION TBS3 12 SYS_SUBP380 TABLE SUBPARTITION TBS1 13 SYS_SUBP381 TABLE SUBPARTITION TBS1 14 SYS_SUBP382 TABLE SUBPARTITION TBS2 15 SYS_SUBP383 TABLE SUBPARTITION TBS3 16 SYS_SUBP384 TABLE SUBPARTITION TBS1 16 개의 행이 선택되었습니다. }}} 즉, 범위분할의 기준을 명시한데로 테이블을 분할을 하기는 하는데 저장할때는 오라클의 해쉬 알고리즘에 의존해서 각각의 테이블스페이스에 저장한다는 뜻입니다. 그런데 이 예제는 책에서 먼저 범위분할을 4개 생성한후 테이블스페이스는 tbs1, tbs2, tbs3 이렇게 3개를 사용합니다. 총12개로 테이블을 분할 했습니다라고 합니다. 저로써는 도저히 이해가 안가는 부분입니다. 제가 보기엔 데이타사전을 조회해 본 결과 20개로 분할했거덩요. 제가 데이타 사전을 잘못 분할 했나여??? 몰겠슴다. 에구.. 좀더 복잡한 복합분할을 해봅시다. {{{ SQL> alter session set nls_date_format = 'dd-mon-yyyy'; 세션이 변경되었습니다. SQL> create table order3 2 (ordid number, 3 orderdate date, 4 productid number, 5 quantity number) 6 partition by range(orderdate) 7 subpartition by hash(productid) subpartitions 8 8 store in (tbs1, tbs2, tbs3) 9 (partition q1 values less than ('01-JAN-1999') 10 (subpartition q1_1 tablespace tbs1, 11 subpartition q1_2 tablespace tbs2, 12 subpartition q1_3 tablespace tbs3), 13 partition q2 values less than ('01-FEB-1999') 14 (subpartition q2_1 tablespace tbs1, 15 subpartition q2_2 tablespace tbs2, 16 subpartition q2_3 tablespace tbs3), 17 partition q3 values less than ('01-MAR-1999') 18 (subpartition q3_1 tablespace tbs1, 19 subpartition q3_2 tablespace tbs2, 20 subpartition q3_3 tablespace tbs3), 21 partition q4 values less than (maxvalue) 22 ); 테이블이 생성되었습니다. SQL> select table_name,partition_name,subpartition_count, partition_position, tablespace_name 2 from user_tab_partitions 3 where table_name = 'ORDER3'; TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT PARTITION_POSITION TABLESPACE_NAME ------------------------------ ------------------------------ ------------------ ------------------ --------------- ORDER3 Q1 3 1 USERS ORDER3 Q2 3 2 USERS ORDER3 Q3 3 3 USERS ORDER3 Q4 8 4 USERS 4 개의 행이 선택되었습니다. SQL> select rownum,partition_name, segment_type, tablespace_name 2 from dba_segments 3 where owner = 'YASI' and segment_name = 'ORDER3'; ROWNUM PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME ---------- ------------------------------ ------------------ ------------------------------ 1 Q1_2 TABLE SUBPARTITION TBS2 2 Q1_3 TABLE SUBPARTITION TBS3 3 Q2_1 TABLE SUBPARTITION TBS1 4 Q2_2 TABLE SUBPARTITION TBS2 5 Q2_3 TABLE SUBPARTITION TBS3 6 Q3_1 TABLE SUBPARTITION TBS1 7 Q3_2 TABLE SUBPARTITION TBS2 8 Q3_3 TABLE SUBPARTITION TBS3 9 SYS_SUBP297 TABLE SUBPARTITION TBS1 10 SYS_SUBP298 TABLE SUBPARTITION TBS2 11 SYS_SUBP299 TABLE SUBPARTITION TBS3 12 SYS_SUBP300 TABLE SUBPARTITION TBS1 13 SYS_SUBP301 TABLE SUBPARTITION TBS2 14 SYS_SUBP302 TABLE SUBPARTITION TBS3 15 SYS_SUBP303 TABLE SUBPARTITION TBS1 16 SYS_SUBP304 TABLE SUBPARTITION TBS2 17 Q1_1 TABLE SUBPARTITION TBS1 17 개의 행이 선택되었습니다. SQL> select table_name, partition_name, subpartition_name, tablespace_name 2 from dba_tab_subpartitions 3 where table_owner = 'YASI'; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ ------- ORDER3 Q1 Q1_1 TBS1 ORDER3 Q1 Q1_2 TBS2 ORDER3 Q1 Q1_3 TBS3 ORDER3 Q2 Q2_1 TBS1 ORDER3 Q2 Q2_2 TBS2 ORDER3 Q2 Q2_3 TBS3 ORDER3 Q3 Q3_1 TBS1 ORDER3 Q3 Q3_2 TBS2 ORDER3 Q3 Q3_3 TBS3 ORDER3 Q4 SYS_SUBP393 TBS1 ORDER3 Q4 SYS_SUBP394 TBS2 ORDER3 Q4 SYS_SUBP395 TBS3 ORDER3 Q4 SYS_SUBP396 TBS1 ORDER3 Q4 SYS_SUBP397 TBS2 ORDER3 Q4 SYS_SUBP398 TBS3 ORDER3 Q4 SYS_SUBP399 TBS1 ORDER3 Q4 SYS_SUBP400 TBS2 17 개의 행이 선택되었습니다. }}} 책에는 이 예제가 3개의 범위 분할과 8개의 해수분할로 먼저 분할되고 q2, q3는 다시 3개의 해쉬 분할로 3 * 8 + 3 + 3 = 30개의 분할이 생성되었다고 나오네여. 이상함돠. 제가 데이타 사전을 조회 해본결과 yasi 사용자는 먼저 users 테이블스페이스를 4개로 범위분할하여 사용하고 있습니다. 그리고 나머지 서브파티션으로 17개가 있습니다. 그러니까 21개로 분할해서 사용하는 것이지여. users 테이블스페이스는 명시하지도 않았는데 조회가 됐다구 이상하게 생각하실지 모르겠지만 이것은 당연한 것입니다. 제가 yasi 사용자의 기본 테이블스페이스를 users로 설정했거덩요. 헤깔립니다. 책이 논리적으로 설명을 해놨으면 몰겠는데 계산법이 어떻게 되는 것인지 잘 몰겠슴다. 아래는 제가 위의 예제를 조금 수정해서 해본 결과입니다. {{{ SQL> create tablespace data1 2 datafile 'k:\oracle_tablespace\data1_1.dbf' size 5M; 테이블 영역이 생성되었습니다. SQL> create tablespace data2 2 datafile 'k:\oracle_tablespace\data2_1.dbf' size 5M; 테이블 영역이 생성되었습니다. SQL> create tablespace data3 2 datafile 'k:\oracle_tablespace\data3_1.dbf' size 5M; 테이블 영역이 생성되었습니다. SQL> create tablespace data4 2 datafile 'k:\oracle_tablespace\dat4_1.dbf' size 5M; 테이블 영역이 생성되었습니다. SQL> create table order2 2 (ordid number, 3 orderdate date, 4 productid number, 5 quantity number) 6 partition by range(orderdate) 7 subpartition by hash(productid) subpartitions 8 8 store in (tbs1, tbs2, tbs3) 9 (partition q1 values less than ('01-JAN-1999') tablespace data1 10 (subpartition q1_1 tablespace tbs1, 11 subpartition q1_2 tablespace tbs2, 12 subpartition q1_3 tablespace tbs3), 13 partition q2 values less than ('01-FEB-1999') tablespace data2 14 (subpartition q2_1 tablespace tbs1, 15 subpartition q2_2 tablespace tbs2, 16 subpartition q2_3 tablespace tbs3), 17 partition q3 values less than ('01-MAR-1999') tablespace data3 18 (subpartition q3_1 tablespace tbs1, 19 subpartition q3_2 tablespace tbs2, 20 subpartition q3_3 tablespace tbs3), 21 partition q4 values less than (maxvalue) tablespace data4 22 ); 테이블이 생성되었습니다. SQL> select table_name,partition_name,subpartition_count, partition_position, tablespace_name 2 from user_tab_partitions 3 where table_name = 'ORDER2'; TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT PARTITION_POSITION TABLESPACE_NAME ------------------------------ ------------------------------ ------------------ ------------------ --------------- ORDER2 Q1 3 1 DATA1 ORDER2 Q2 3 2 DATA2 ORDER2 Q3 3 3 DATA3 ORDER2 Q4 8 4 DATA4 4 개의 행이 선택되었습니다. SQL> select rownum,partition_name, segment_type, tablespace_name 2 from dba_segments 3 where owner = 'YASI' and segment_name = 'ORDER2'; ROWNUM PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME ---------- ------------------------------ ------------------ ------------------------------ 1 Q1_1 TABLE SUBPARTITION TBS1 2 Q1_2 TABLE SUBPARTITION TBS2 3 Q1_3 TABLE SUBPARTITION TBS3 4 Q2_1 TABLE SUBPARTITION TBS1 5 Q2_2 TABLE SUBPARTITION TBS2 6 Q2_3 TABLE SUBPARTITION TBS3 7 Q3_1 TABLE SUBPARTITION TBS1 8 Q3_2 TABLE SUBPARTITION TBS2 9 Q3_3 TABLE SUBPARTITION TBS3 10 SYS_SUBP289 TABLE SUBPARTITION DATA4 11 SYS_SUBP290 TABLE SUBPARTITION DATA4 12 SYS_SUBP291 TABLE SUBPARTITION DATA4 13 SYS_SUBP292 TABLE SUBPARTITION DATA4 14 SYS_SUBP293 TABLE SUBPARTITION DATA4 15 SYS_SUBP294 TABLE SUBPARTITION DATA4 16 SYS_SUBP295 TABLE SUBPARTITION DATA4 17 SYS_SUBP296 TABLE SUBPARTITION DATA4 17 개의 행이 선택되었습니다. }}} 그래도 17 + 4 = 21 개의 분할인것으로 보입니다. 우찌된 것인지... ㅡㅡ; 성질 날랄구 함돠. 혹시 이 문서를 보시는 분 중에 알고계시거나 아니면 알게 되시는 분은 제게 좀 알려주시면 감사하겠습니다. 아무래도 저자에게 메일을 날려야 겠슴다. 암튼 관련 자료 사전은 다음과 같습니다. * DBA_TAB_SUBPARTITIONS * DBA_IND_SUBPARTITIONS * DBA_SUBPART_KEY_COLUMNS * DBA_TAB_PARTITIONS * DBA_IND_PARTITIONS * DBA_PART_TABLES * DBA_PART_INDEXES * DBA_OBJECTS * DBA_SEGMENTS 다음은 복합분할을 사용하는 방법입니다. {{{ SQL> insert into order3 subpartition(q2_3) 2 values (1, '12-JAN-1999', 143, 12000); 1 개의 행이 만들어졌습니다. SQL> select * from order3 subpartition(q2_3); ORDID ORDERDATE PRODUCTID QUANTITY ---------- ----------- ---------- ---------- 1 12-jan-1999 143 12000 SQL> update order3 subpartition(q2_3) 2 set quantity = 13000; 1 행이 갱신되었습니다. SQL> delete order3 2 subpartition(q2_3); 1 행이 삭제되었습니다. }}} ==== L0B 타입을 가진 컬럼의 분할 ==== LOB 타입을 가진 테이블의 분할은 쉽습니다. {{{ SQL> create table ord_lob 2 (orderdate date, 3 pic BLOB) 4 LOB(pic) store as (tablespace data1) --------------> LOB 타입을 따로 분할해서 저장합니다.. 5 partition by range(orderdate) 6 (partition p1 values less than ('01-JAN-1999') 7 tablespace data2, 8 partition p2 values less than ('01-FEB-1999') 9 tablespace data3); 테이블이 생성되었습니다. SQL> alter table ord_lob ---------------> 새로운 파티션을 추가합니다.. 2 add partition p3 3 values less than (maxvalue) 4 lob (pic) store as (tablespace tbs3); 테이블이 변경되었습니다. SQL> alter table ord_lob merge partitions p1, p2 into partition p5 -----------> 파티션 p1과 파티션 p2를 합치는 것.. 2 lob(pic) store as (tablespace tbs3); 테이블이 변경되었습니다. }}} 휴~ 이정도로 분할을 마칠라고 합니다. 오라클의 분할 기능은 입/출력을 줄여주는 멋있는(?) 오라클의 메카니즘입니다. 이 분할기능은 데이타웨어하우징 업무에 적합하다고 합니다. 물론 갱신, 삭제, 입력이 거의 조회업무이기 때문이라고 하지만 다른 큰 데이타베이스에서 사용한다면 너무나도 빠른 성능을 낼수 있는 메카니즘이라고 생각합니다. 부족한 부분이 있을겁니다. 이 부족한 것들은 다은 웹문서나 책, 또는 메뉴얼을 참고하시기 바랍니다. ---- 잘 보고 갑니다 고맙습니다. -- 공부중 2011-05-01 11:38:25