HOME   로그인   회원가입 비밀번호 찾기 자동로긴 이전게시판    free_board    건강게시판    오라클DB    Linux    HTML/javascript    질문과 답 호서기    이미지게시판       건강게시판       음악자료       성준이 게시판    [일반] 오라클 테이블 파티션 사용하기   호석 님께서 쓴 글입니다 - 211.♡.74.31 읽음:5443   ** Oracle provides the following partitioning methods **                                                    1. Range Partitioning                                 2. Hash Partitioning                                   3. List Partitioning (9i~)                                 4. Composite Partitioning       <파티셔닝의 장점 및 필요성> --------------------------- - full scan시 효과를 볼수 있다 - 관리 데이터 크기가 커짐에 따라 관리, 조작의 문제점 발생 (VLDB, DW) - 파티션별로 독립적인 백업 및 복구 가능 - 시스템 장애 발생시 데이터 손상 정도를 최소화 - 불필요한 데이터 액세스 방지(파티션 푸루닝) - I/O분산에 의한 성능 향상 - 독립적인 DML문 수행 - divide-and-conquer 솔루션을 제공 ================================================================================================ 1. Range Partitioning(8~) - CREATE TABLE table_name( ... )   PARTITION BY RANGE (key_column)   (PARTITION partition_name VALUES LESS THAN (condition) TABLESPACE tablespace_name,    PARTITION partition_name VALUES LESS THAN (condition) TABLESPACE tablespace_name,    PARTITION partition_name VALUES LESS THAN (condition) TABLESPACE tablespace_name,    ...    PARTITION partition_name VALUE LESS THAN (MAXVALUE) TABLESPACE tablespace_name); - 분할키는 최대 16개 컬럼으로 생성가능. - long, LONGRAW 분할 컬럼 설정 불가능. - 컬럼값이 NULL인 것은 MAXVALUE값에 해당. ex) create table jeon (idate date,  no char(2),  qty number) partition by range(idate) (partition t1 values less than (to_date('2000/01/01', 'yyyy/mm/dd')) tablespace test01,  partition t2 values less than (to_date('2001/01/01', 'yyyy/mm/dd')) tablespace test02,  partition t3 values less than (maxvalue) tablespace test03); SQL> alter session set nls_date_format = 'yyyy-mm-dd'; SQL> insert into jeon values(to_date('1999-12-01'), '01',120); SQL> insert into jeon values(to_date('2000-06-12'), '02', 30); SQL> insert into jeon values(to_date('2001-02-01'), '03', 25); SQL> insert into jeon values(to_date('2002-07-03'), '04', 43); SQL> commit; - UPDATE문을 사용하여 분할키 컬럼의 값을 변경 SQL> ALTER TABLE jeon ENABLE ROW MOVEMENT;  ---> DBMS가 자동으로 이동시켜준다. SQL> UPDATE jeon SET idate = to_date('2000-12-01')      WHERE idate = to_date('1999-12-01'); SQL> commit;       ** Range 파티션테이블 add ** ALTER TABLE PARTRANGE ADD PARTITION P1 VALUES LESS THAN (2003,4,1) TABLESPACE TEST01; ALTER TABLE PARTRANGE ADD PARTITION P2 VALUES LESS THAN (2003,7,1) TABLESPACE TEST02; ALTER TABLE PARTRANGE ADD PARTITION P3 VALUES LESS THAN (2003,10,1) TABLESPACE TEST03; ALTER TABLE PARTRANGE ADD PARTITION P4 VALUES LESS THAN (2004,1,1) TABLESPACE TEST04;  ALTER TABLE PARTRANGE ADD PARTITION OVER VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) TABLESPACE TEST01; ALTER TABLE PARTRANGE ADD PARTITION P5 VALUES LESS THAN (2004,4,1) TABLESPACE TEST01; ** Range 파티션테이블 drop ** ALTER TABLE PARTRANGE DROP PARTITION OVER; ** Range 파티션테이블 rename ** ALTER TABLE partrange RENAME PARTITION p4 TO p5; ** Range 파티션테이블 move ** ALTER TABLE emp_pt MOVE PARTITION emp_p3 TABLESPACE p4 nologging; ** Range 파티션테이블 truncate ** ALTER TABLE emp_pt TRUNCATE PARTITION emp_p3; insert into partrange values(1, 1, 2004, 373);         ** View ** DBA_PART_KEY_COLUMNS   DBA_PART_COL_STATISTICS   DBA_PART_HISTOGRAMS   DBA_PART_INDEXES   DBA_IND_PARTITIONS   DBA_PART_TABLES   DBA_TAB_PARTITIONS   DBA_PART_LOBS   DBA_LOB_PARTITIONS   ** Version 8.1 offers new views to administer this level of granularity:    DBA_SUBPART_COL_STATISTICS   DBA_SUBPART_HISTOGRAMS   DBA_SUBPART_KEY_COLUMNS   DBA_IND_SUBPARTITIONS   DBA_LOB_SUBPARTITIONS   DBA_TAB_SUBPARTITIONS      ============================================================================================ 2. Hash Partitioning(8i~)  - 오라클 서버가 지정된 데이터 파일에 적절히 나누어서 저장한다. - 해시 알고리즘에 의해 처리되므로 범위 분할보다 분포도가 훨씬 좋을수 있다. - 문법   - CREATE TABLE table_name( ... )     PARTITION BY HASH (key_column) PARTITIONS [분할수]     STORE IN (tablespace, ...);   또는   - CREATE TABLE table_name( ... )     PARTITION BY HASH (key_column)     (PARTITION partition_name TABLESPACE tablespace_name,      PARTITION partition_name TABLESPACE tablespace_name,      ... );     ex) - create table jeon1   (id date,    name char(2))   partiton by hash (id) partitions 3   store in (test01, test02, test03);  or - create table jeon1   (id date,    name char(2))   partiton by hash (id)   (partition p1 tablespace test01,    partition p2 tablespace test02,    partition p3 tablespace test03);   ============================================================================================= 3. List Partitioning(9i~) - 특정 리스트에 의해 분할해야 할 경우. - 문법   - CREATE TABLE table_name( ... )     PARTITION BY LIST(key_column)     (PARTITION partition_name VALUES ( ... ) STORAGE( ... ) TABLESPACE tablespace_name,      PARTITION partition_name VALUES ( ... ) STORAGE( ... ) TABLESPACE tablespace_name,      PARTITION partition_name VALUES ( ... ) STORAGE( ... ) TABLESPACE tablespace_name,      ... ); - 분할 사이에는 어떠한 관계도 존재하지 않는다. - 데이터의 분할 및 성능 향상 효과를 기대할 수 있다.           =====================================  PARTITION TABLE의 관리를 위한 COMMAND ===================================== Purpose ------- Oracle8 부터 사용가능한 Partition table을 handling 하기 위한 다양한 command들을 확인해 보자. SCOPE ----- 8~10g Standard Edition 에서는 Partitioning Option 은 지원하지 않는다. Explanation ----------- [예제] 아래와 같이 partition table을 생성한다.  SQL> create table part_tbl         ( in_date number primary key ,           empno   number,           ename   varchar2(20),          job   varchar2(20) )        partition by range (in_date)       (partition part_tbl_03  values less than (20000331)   tablespace pts_03,    partition part_tbl_04  values less than (20000430)   tablespace pts_04,   partition part_tbl_05  values less than (20000531)   tablespace pts_05,   partition part_tbl_06  values less than (20000630)   tablespace pts_06,   partition part_tbl_07  values less than (20000731)   tablespace pts_07,   partition part_tbl_08  values less than (20000831)   tablespace pts_08,   partition part_tbl_09  values less than (20000930)   tablespace pts_09,   partition part_tbl_10  values less than (20001031)   tablespace pts_10 ); 1. partition 을 add하는 방법  11월과 12월에 대해 partition을 add하고 싶은 경우 다음과 같이 할 수 있다.  SQL> alter table part_tbl add partition part_tbl_11             values less than (20001130) tablespace pts_11;  SQL> alter table part_tbl add partition part_tbl_12             values less than (20001231) tablespace pts_12; 2. 특정 partition 을 삭제하는 방법  8월에 해당하는 partition을 없애고 싶은 경우는 다음과 같이 실행한다.  SQL> alter table part_tbl drop partition part_tbl_08;  drop된 후에 새로 8월에 해당하는 데이타가 입력되면 9월의 partition이 less then (20000930) 으로 되어 있으므로 9월에 해당하는 partition에 저장된다. 3. partition을 나누는 방법  1월, 2월에 해당하는 partition을 생성하려면 partition을 add하는 것으로는 불가능하고 기존의 partition에서 split 해야 한다.  SQL> alter table part_tbl split partition part_tbl_03         at (20000229)         into (partition part_tbl_02 tablespace pts_02,               partition part_tbl_03_1 tablespace pts_03);  위와 같이 하면 기존의 partition에서 2월29일을 기준으로 2월과 3월로 partition이 나눈다. 그리고 나서 다시 split 해야한다.  SQL> alter table part_tbl split partition part_tbl_02         at (20000131)         into (partition part_tbl_01 tablespace pts_01,               partition part_tbl_02_1 tablespace pts_02); 4. partition name을 변경하는 방법  partition name 을 바꾸고 싶다면 다음과 같이 실행한다.  SQL> alter table part_tbl rename partition part_tbl_02_1 to part_tbl_02;  SQL> alter table part_tbl rename partition part_tbl_03_1 to part_tbl_03; 5. partition의 tablespace를 옮기는 방법  partition part_tbl_10을 저장하는 tablespace를 pts_10 에서 pts_10_1로 바꾸고 싶은 경우 아래와 같은 command를 사용한다.  SQL> alter table part_tbl move partition part_tbl_10                     tablespace pts_10_1 nologging; 6. 특정 partition의 data를 truncate하는 방법  partition의 data를 모두 삭제하려면 truncate하는 방법을 사용할 수가 있는 데, truncate는 rollback 이 불가능하며 특정 partition 전체를 삭제하므로 주의하여 사용하여야 한다.  SQL> alter table part_tbl truncate partition part_tbl_02; 7. Partition table의 물리적인 속성 변경  partition table은 특정 partition의 속성만 변경할 수 있고, table의 속성을 변경하여 전체 partition에 대해 동일한 변경을 할 수 있다.  SQL> alter table part_tbl storage( next 10M);         -> part_tbl 의 모든 partition의 next 값이 변경된다.  SQL> alter table part_tbl modify partition part_tbl_05               storage ( maxextents 1000 );         -> part_tbl_05 partition의 maxextents 값만 변경한다. 8. Index의 관리   위와 같이 partition table 관련 작업을 한 후에는 table에 걸려 있는 local(partitioned) index 나 global index를 반드시 rebuild해 주어야 한다.  특정 partition의 index를 rebuild 하려면  SQL> alter index ind_part_tbl rebuild partition i_part_tbl_02;    그리고 global index를 rebuild하려면  SQL> alter index part_tbl_pk rebuild; Reference Document ------------------  partition table에서의 index 관리에 대해서는 <Bul:11672> 를 참고.        ** 파티션 인덱스 **   1. 분할 구조적 기준    - global index : index coverage scope이 partitioned table 전체.    - local index : index coverage scope이 해당 partitioned table segment이다.    2. 분할키의 사용 기준    - prefix index : partition key가 인덱스의 선두 컬럼으로 구성.    - non-prefix index : partition key가 인덱스의 선두 컬럼으로 시작하지 않는 경우.       3. Partitoned index vs Non-partitoned index    - index partition은 table partition과는 별개이므로 index partition만의 storage와      partition key가 정의된다. 즉, Local index는 단지 table partition과 Equi-Partitioning      이므로 table partition key가 index partition key와 동일할 뿐이다.            ============================================================================================ 1. 글로벌 분할 인덱스  SQL> create table jeon      (idate date,       no char(2),       qty number)      partition by range(idate)      (partition t1 values less than (to_date('2000/01/01', 'yyyy/mm/dd')) tablespace test01,       partition t2 values less than (to_date('2001/01/01', 'yyyy/mm/dd')) tablespace test02,       partition t3 values less than (maxvalue) tablespace test03); SQL> CREATE INDEX i_jeon ON jeon(idate)      GLOBAL      PARTITION BY RANGE(idate)      (PARTITION t1 VALUES LESS THAN (to_date('1999/01/01', 'yyyy/mm/dd')) TABLESPACE test01,        PARTITION t2 VALUES LESS THAN (MAXVALUE) TABLESPACE test02); - 테이블과 인덱스의 파티션 범위와 개수가 틀리다.    =============================================================================================== 2. 로컬 분할 인덱스  SQL> CREATE INDEX i_jeon ON jeon(idate)                                                           LOCAL                                                                                      PARTITION BY RANGE(idate)                                                                   (PARTITION t1 VALUES LESS THAN (to_date('2000/01/01', 'yyyy/mm/dd')) TABLESPACE test01,       PARTITION t2 VALUES LESS THAN (to_date('2001/01/01', 'yyyy/mm/dd')) TABLESPACE test02,       PARTITION t3 VALUES LESS THAN (MAXVALUE) TABLESPACE test03);        - 테이블과 인덱스의 파티션 범위와 개수가 같다.   =============================================================================================== 3. prefix index                           - 분할 테이블과 분할 인덱스의 분할 구조와는 상관없이 같은 분할 컬럼으로 생성될때. - 글로벌 분할 인덱스, 로컬 분할 인덱스 기법과 함께 사용된다. SQL> CREATE INDEX i_jeon ON jeon(idate)                                                                                    GLOBAL                                                                                      PARTITION BY RANGE(idate)                                                                   (PARTITION t1 VALUES LESS THAN (to_date('1999/01/01', 'yyyy/mm/dd')) TABLESPACE test01,       PARTITION t2 VALUES LESS THAN (MAXVALUE) TABLESPACE test02);                                ================================================================================================      4. non-prefix index      - 분할 테이블과 분할 인덱스의 분할 컬럼이 서로 다른 경우. SQL> CREATE INDEX i_jeon ON jeon(idate)                                                           LOCAL                                                                                      PARTITION BY RANGE(no)                                                                   (PARTITION t1 VALUES LESS THAN ('M') TABLESPACE test01,       PARTITION t2 VALUES LESS THAN (MAXVALUE) TABLESPACE test02);        ================================================================================================= 1. local prefix index: OLTP환경에서 효과적. 2. global prefix index: Unique Key인 경우 효과적. 3. local non-prefix index: DDS환경에서 효과적. ================================================================================================= <<house 서버 partrange 테이블에 인덱스 생성 테스트>> CREATE INDEX PARTRANGE_IND ON PARTRANGE(ORD_YEAR)                                                      LOCAL                                                                                 (PARTITION P1 TABLESPACE TEST01 STORAGE(INITIAL 1M NEXT 1M),  PARTITION P2 TABLESPACE TEST02 STORAGE(INITIAL 1M NEXT 1M),  PARTITION P3 TABLESPACE TEST03 STORAGE(INITIAL 1M NEXT 1M),  PARTITION OVER TABLESPACE TEST04 STORAGE(INITIAL 1M NEXT 1M));     <<참고>> ========================================================================== 서브 파티션 테이블 추가시 파티션 인덱스는 추가되는  파티션 테이블에 지정된 테이블스페이스에  디폴트 initial_extent 크기만큼 할당되어 생성된다. ========================================================================== 분류 선택 전체 일반 질문 답변 알림 94 일반 오라클 진수변환 패키지 소스 ( 2 진수 ~ 36 진수 ) 호석 08-02-27 4892 93 일반 오라클 암호화 구현 호석 08-02-20 5666 92 일반 sqlplus에서 변수 사용하는 방법 호석 07-09-14 6459 91 일반 오라클 10g의 Flashback 기술 [recycle 기능] 호석 07-08-10 5679 90 일반 ENTERPRISE EDITION 데이터베이스를 STANDARD EDITION으로 변경하는 법 호석 07-07-25 4765 일반 오라클 테이블 파티션 사용하기 호석 07-07-25 5444 88 일반 설치실수로 인한 오라클 재설치 관련 호석 07-07-24 4294 87 일반 오라클 파티션 테이블 (Partitioned Table) 호석 07-07-10 9289 86 일반 오라클 테이블 파티셔닝 호석 07-07-10 6169 85 일반 동적 SQL(Dynamic SQL)의 사용방법 호석 07-06-28 5864 84 일반 오라클 10G 토드에서 DEBUG 기능사용하기 호석 07-06-25 5098 83 일반 개발자가 범하기 쉬운 튜닝 이슈 10가지 유형 분석 호석 07-06-22 5021 82 일반 오라클 8i ORA-00604 ,ORA-04031 에러 처리 호석 07-06-12 6234 81 일반 오라클 8i DB 재시작 호석 07-06-12 4103 80 일반 mysql DB 및 사용자 생성 (1) 호석 07-05-18 4078   [1] [2] [3] 4 [5] [6] [7] [8] [9] [10]   제목+내용 이름 번호 날짜 제목 내용 읽음 또는 그리고   Copyright © zenos.pe.kr. All rights reserved.