Partition Table

Oracle Technical Note Partition Table Troubleshooting 시리즈는필자가한국오라클서버지원팀에서근무하면서실제고객들로부터많은문의를받았던부분들에대해단편적인해결책이아닌보다근본적으로심도있게정리한것이다. 각호에서는각호마다다루는항목에대한기본적인개념과메커니즘을설명한후업무과정에서발생빈도가높은에러를처리해나가는과정을설명한다. 지난호까지는오라클메모리부분, 분산데이타베이스등에대해다루어보았고, 이번호에서는점점사용량이증가하고있는 Partition Table에대해다루어보기로한다. 이 Partition Table의초기개념은 V7.x의 Partition View로거슬러올라갈수있으나, Partition View에비해 DML 부분등의추가부분이상당히진전된개념이다. 이에, 먼저 Partition View 의원리와그문제점등에대해점검해보고, V8.x부터기능이추가된 Partition Table에대해좀더알차게다루어보기로한다. 글 박경희 한국오라클서버지원팀 khpark@kr.oracle.com

Partition View 이부분에서는 Partition Elimination이가능하게된 V7.3. 이상의 Partition View에대해다루어본다. 원래 Partition View는대용량의데이타를다루는 Data Warehousing 환경을지원하고자하는것으로, 커다란테이블의관리보다테이블을분리함으로써손쉽게관리 커다란전체테이블을읽는것 Scan 보다분리된테이블을읽도록유도하기위한개념이다. Partition View의사용 Partition View를사용하기위해서는 1. InitSID.ora 파일에 Partition_view_enabled=TRUE가설정되어야한다 2. Partition View를만들고자하는테이블에시간이나지역으로분리가가능한칼럼을선택하여 Partition Column을결정한다. 3. 테이블생성시 Partition Column에반드시해당범위의 Check Constraint를부여한다. 4. 인덱스생성은반드시 Partition Column에필요한것은아니나, 생성시모든테이블이반드시동일한인덱스를가져야한다. 5. 각테이블에 ANALYZE를실시하여정확한통계를수집한다. 6. 모든테이블을 UNION ALL한 Partition View를생성한다. Partition View 예제 create view sales as select * from jan_sales union all select * from feb_sales... union all select * from dec_sales 이 Partition View는 Jan_sales, Feb_sales, Mar_sales,...,Dec_sales의 12개테이블을이용하여 View를생성한것이다. 이때각테이블은반드시같은칼럼이름, 칼럼데이타타입, 같은인덱스로구성되어있어야한다. 또한반드시파티션되는칼럼에는 CHECK Constraint가있어야한다. 즉, Jan_sales Table은 Date 칼럼에 JAN 1 JAN 31의데이타만입력된다는조건이어야한다는 CHECK Constraint 가있어야한다. 그리고이기본테이블을이용한 UNION VIEW가생성되어야한다. Partition Table 2

관리및사용효율만일전체 Sales 테이블이각월별로나뉘지않고 1년의전체데이타로구성되어있다면, 데이타관리자는해당월의모든데이타를이테이블에올리기위해매달해당인덱스를삭제한후 (Load하는데이타양이크기때문에 ) 데이타를올리고다시인덱스를만드는과정을거쳐야하며, 이작업동안해당테이블은사용하지못하는결과를얻게된다, 반면에, Partition View를사용하게되면, 데이타관리자는매달새로운데이타를새로운테이블에넣고, 새로운인덱스를생성한후새로운테이블을포함한 UNION-ALL View를새롭게구성하면된다. 이경우인덱스크기가상대적으로작기때문에데이타를올리는 (Load) 시간뿐만아니라인덱스를생성하는시간이적게걸리는효과를얻을수있다. Performance 아무리관리기법이뛰어나도, Query 효과가없다면의미가없을것이다. 그러나, UNION -ALL의 Partition View는최소한 1개의테이블을 Query하는것과속도가같거나그보다더빠르다. 이처럼 Performance 효과는 Partition Elimination 기능 Parallel 수행지원때문에가능하다. 예제 select order_id,comp_id,date, revenues from sales where comp_id >20000 and sales_date between '10-july-2000' and '15-july-2000'; 이경우오라클은 1월과 2월의파티션만접근하여데이타를가져오는데, 이를 Partition Elimination 이라하며 ( 이는파티션칼럼에만영향을미치는데, 여기서는 sales_date 칼럼이다 ), Parallel Query Option을사용하여그 Degree만큼의수행속도를높일수있다. <Execution Plan> Rows Execution Plan ----------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 100 VIEW OF 'SALES' 100 UNION-ALL (PARTITION) 0 FILTER 0 TABLE ACCESS OF 'JAN_SALES' 0 INDEX (RANGE SCAN) OF 'JAN_SALES_IDX' 0 FILTER 0 TABLE ACCESS OF ''FEB_SALES' 0 INDEX (RANGE SCAN) OF ''JAN_SALES_IDX' 0 FILTER Partition Table 3

0 TABLE ACCESS OF 'MAR_SALES' 0 INDEX (RANGE SCAN) OF 'MAR_SALES_IDX' 0 FILTER 0 TABLE ACCESS OF 'APR_SALES' 0 INDEX (RANGE SCAN) OF 'APR_SALES_IDX' 0 FILTER 0 TABLE ACCESS OF 'MAY_SALES' 0 INDEX (RANGE SCAN) OF 'MAY_SALES_IDX' 0 FILTER 0 TABLE ACCESS OF 'JUN_SALES' 0 INDEX (RANGE SCAN) OF 'JUN_SALES_IDX' 100 TABLE ACCESS OF 'JULY_SALES' 150 INDEX (RANGE SCAN) OF 'JULY_SALES_IDX' 0 FILTER 0 TABLE ACCESS OF 'AUG_SALES' 0 INDEX (RANGE SCAN) OF 'AUG_SALES_IDX' 0 FILTER 0 TABLE ACCESS OF 'SEP_SALES' 0 INDEX (RANGE SCAN) OF 'SEP_SALES_IDX' 0 FILTER 0 TABLE ACCESS OF 'OCT_SALES' 0 INDEX (RANGE SCAN) OF 'OCT_SALES_IDX' 0 FILTER 0 TABLE ACCESS OF 'NOV_SALES' 0 INDEX (RANGE SCAN) OF 'NOV_SALES_IDX' 0 FILTER 0 TABLE ACCESS OF 'DEC_SALES' 0 INDEX (RANGE SCAN) OF 'DEC_SALES_IDX' 이처럼 Partition Column이 BETWEEN~ AND~ 로비교되었을때는접근이필요없는테이블은 Scan하지않는 Filter Operation이사용되며, 만일 Where 조건에 Partition Column이나오지않는경우는 Filter 기능이사용되지않는 UNION-ALL View 형태로 Access Plan이사용되어 Performance 저하가발생된다. 이처럼부득이한경우는 ROWNUM 등의 Function 사용으로 TABLE ACCESS 를제한한다. Partition Table 4

제약사항 Partition View의제약사항은다음과같다. 수작업을통한파티션관리데이타를파티션테이블과인덱스에분류해야하는작업을사용자가하여야하며, 파티션간의데이타이동 (split, move...) 을위한작업수행시에는 Export/Import 또는 SQL 문으로하여야한다. DML문의사용제한 UNION ALL View에 DML(Insert,Update, Insert) 문장을사용할수없어, 이를위해서는파티션을이루는기본테이블에하여야한다. 이를손쉽게하기위해 DYNAMIC SQL을이용하거나일어날수있는모든경우를 IF-ELSE를이용한 Logic으로해결하는방안등을사용하나, 이경우도 Parsing Overhead나 Partition을위한테이블이추가될때마다애플리케이션프로그램이변경되어야하는단점이있다. Performance 문제 Optimizer가충분히인덱스를활용할수없으며, UNION ALL View에대한 SQL 문장수행시동일한구조의 Table Definition이중복되어 SGA 에올라오게되어 SQL Parsing의낭비가따른다. DDL 문 (Create, Alter..) 의제한 UNION ALL View에대해서는 Global Index나 Referential Integrity Constraint를정의할수없다. Data Load시의제한 UNION ALL View에대해서는 Direct Load를수행할수없다. Partition Table 기존 Partition View의제한사항을해소하는개념인파티션오브젝트가 V8.0 이상부터는지난호에소개했던 Distributed Database Option처럼한부분의 Option으로분류되어제공되고있다. 이는오라클제품을처음설치시선택하여야만사용이가능함을일컫는다. 한테이블당가능한파티션은이론적으로 65535이며, 실제적으로는 1000개까지테스트가이루어진상태이다. 이파티션테이블을만들면서생성되는각파티션은다른 Segment(Tablespace) 에저장하여아래와같은이점을활용할수있다. 시스템장애발생시데이타의손상정도를완화할수있다. 각 Partition별로독립적으로 Back Up과 Recovery가가능하다. Disk Drive에따라 Partition을 Mapping함으로써 I/O Load Balancing이가능하다. 그리고 Partition Table의제한사항은다음과같다. Partitioning 지원제한오라클은 V8.0 이상부터테이블과인덱스에대해 Partitioning을지원하고있으며, Clustered Table이나 Clustered Index, Snapshot에대해서는 Partitioning을지원하지않는다. Partition Table 5

데이타타입제한파티션테이블은 LONG, LONGRAW 데아타타입은불가능하나, 이를대신하는 LOB(BLOB, CLOB, NCLOB, BFILE) 데이타타입은파티션이가능하다. 비트맵인덱스의제한파티션테이블에대해비트맵 (Bitmap Index) 의생성이가능하나, 이는 Local Index로만생성되어야하며, Global Index로는생성할수없다. 인덱스 Oracle8의새로운기능인파티션은인덱스에도적용되며, Partition Table에서의인덱스는 Local Index, Global Index로나눌수있다. 우선 Local Index는기본테이블중하나의파티션만을참조하는경우이며, Global Index는기본테이블의모든파티션을참조하는인덱스이다. 이파티션되는인덱스는생성되는형태에따라몇가지로나눌수있다. LOCAL INDEX 로컬인덱스란인덱스를생성한테이블과파티션된인덱스가동일하게파티션된 (Equi-Partition) 경우를나타낸다. 즉, 인덱스와테이블은같은칼럼에의해파티션되며, 하나의인덱스파티션이테이블파티션하나와대응되며, 대응되는인덱스파티션과테이블파티션은각각같은범위를갖게된다. 결국특정한하나의인덱스에포함된모든 Key 들은하나의테이블파티션내의데이타만을가리키게된다. 파티션되는인덱스는다시다음과같이 Prefixed와 Non-Prefixed로나누어진다. Local Prefixed Index 인덱스의맨앞에위치한칼럼에의해파티션되는것이며, 뒤에언급될 Non- Prefixed Index는인덱스에서맨앞의칼럼을제외한다른칼럼에의해파티션된경우이다. Local Prefixed Index는 Unique/Non-Unique를모두허용한다. Local Prefixed Index는다음과같이생성할수있다. CREATE TABLE dept (deptno NUMBER NOT NULL, dname VARCHAR2(10) NOT NULL, loc VARCHAR2(14)) PARTITION BY RANGE (deptno) (PARTITION part1 VALUES LESS THAN (30), PARTITION part2 VALUES LESS THAN (MAXVALUE)); CREATE INDEX deptloc1_idx ON dept(deptno) LOCAL; Local non-prefixed Index 인덱스의맨앞에있는칼럼이테이블의파티션을이루는키가아닌경우 Non- Prefixed Index가된다. 또한 Local Non-Prefixed Index가 Partitioning Key 의 Subset이아닌경우에는 Unique Local Non-Prefixed Index를생성할수없 Partition Table 6

으며, 이는다음처럼생성할수있다. CREATE INDEX deptloc2_idx ON dept(loc) LOCAL; 이러한 Non-Prefixed Index는 Historical한데이타를보관하는테이블의경우유용하다. 즉, 날짜에따라테이블과인덱스의파티션은이루어지고, 인덱스는별도의사원번호나제품번호와같은칼럼을이용하여생성하는경우이다. 또한만일테이블과인덱스가같은칼럼에대해같은값으로파티션되어 (Equi- Partitioning) 있다면아래와같은이점을가질수있다. 기초테이블에 SPLIT PARTITION을제외한 Add, Drop의명령이수행되는경우하나의인덱스파티션만이영향을받게된다. 파티션된테이블이 Local Index만을가지고있다면, 이러한명령들이수행되는중에는해당파티션만을사용하지못하고다른파티션은사용이가능하다. 즉, Local Index는각파티션이독립적으로수행이가능하다는것을보장한다. Local Index를사용하여 Equi-Partitioned된경우에는보다나은 Query Access Plan을생성할수있다. Local Index는 Tablespace Incomplete Recovery가손쉽게이루어지도록한다. 시간단위로 Recovery를수행하는경우테이블과인덱스는동시에 Recover되어야하며, 이를완벽하게지원하기위해서는 Local Index 를사용하여야한다. GLOBAL INDEX Global Prefixed Index Global Index는테이블과다르게파티션되는경우이다. 즉테이블과같은칼럼으로파티션되지만, 그범위가틀리거나혹은다른칼럼으로파티션이이루어지는경우이다. 하나의인덱스파티션에있는모든인덱스는모두하나의테이블파티션에속하게되지않고, 두개이상의파티션에나누어있을수있다. 일반적으로 Global Index는기초테이블과 Equi-Partitioned되도록생성하지는않지만, Equi-Partitioned Global Index를생성한경우에오라클은이를이용하여 Query Plan을생성하거나파티션관리를위한명령이실행되는경우에도 Equi-Partitioning의이점을활용하지않는다. 따라서 Equi-Partitioned로인덱스를생성하고자하는경우에는반드시 LOCAL 로생성하여야한다. 또는 Global Index의가장높은파티션은파티션을분리하는범위를나타내는부분에 MAXVALUE를가져야한다. 이렇게해서기초가되는테이블의모든데이타가인덱스에모두반영되는것을보장하게된다. CREATE INDEX dept_idx ON dept(dname) GLOBAL PARTITION BY RANGE (dname) (PARTITION p1 VALUES LESS THAN ('N'), PARTITION p2 VALUES LESS THAN (MAXVALUE)); Partition Table 7

Global Index 생성시인덱스칼럼의맨처음칼럼을사용하여파티션되면, 생성된인덱스는 Global Prefixed Index이며, 다른칼럼을사용하여파티션되는경우에는 Global Non-Prefixed Index이지만, 오라클에서는 Global Prefixed Index만을지원한다. Global Index는항상인덱스의맨앞칼럼값만을이용하여파티션된다. Global Prefixed Index는 Unique/Non-Unique로생성될수있으며, Non- Partitioned Index는 Global Index로취급된다. 그러면, Global Index가어느때유용한지다음의예를살펴보자. 만일 DATE 칼럼으로파티션된커다란테이블이있다고가정한다. 그러나주로 VCOL이란칼럼으로자주결과를얻고자하는경우, 우리는 VCOL을이용한 Local Non-Prefixed Index와 VCOL을이용한 Global Prefixed Index를만들수있으며, 해당결과를얻기위해 VCOL로파티션된 Unique Global Index를사용하는것이훨씬좋은 Performance를얻을수있다. 만일 Unique하지않는경우는 Parallel Degree의정도에따라 Local과 Global의 Performance가다를수있다 ( 이는 Global Index의관리비용이비싸기때문이다 ). Primary Key 를설정하는경우파티션테이블에 Primary Key Constraint를지정하여사용하고자추가하면, 이때내부적으로생성되는인덱스는비록테이블의데이타가파티션되어있다하더라도, 파티션되지않고, 하나의세그먼트로생성되어 Global Index로취급되어불편하다. 이러한경우는 Local Index를사용하도록하기위해 Local Index를생성후 ALTER TABLE 명령어를이용하여 Constraint를추가하도록한다이때미리생성하는인덱스는 Local이든 Global이든상관없으나반드시 Prefixed Index로생성되어야한다 (Non-prefixed Index는하나의 Index Key 값에해당하는데이타가여러테이블파티션에존재할수있다는것을의미한다 ). 1. Partitioned Table 생성 create table test_a (col1 number, col2 number, col3 varchar2(20)) Partition by range (col1, col2) (Partition part_test_a_1 values less than (10, 100) tablespace ts0, Partition part_test_a_2 values less than (20, 200) tablespace ts1, Partition part_test_a_3 values less than (30, 300) tablespace ts2, Partition part_test_a_4 values less than (40, 400) tablespace ts3); column Table_name format a12 column Partition_name format a13 column High_Value format a15 column PP format 99 column TBSN format a5 Partition Table 8

select table_name, Partition_name, high_value, Partition_position pp, tablespace_name tbsn, initial_extent Ini_Extent, next_extent NEXT from user_tab_partitions order by pn Table_name Partition_name HighValue PP TBSN Ini_Extent NEXT TEST_A PART_TEST_A_1 10, 100 1 TS0 20480 20480 TEST_A PART_TEST_A_2 20, 200 2 TS1 20480 20480 TEST_A PART_TEST_A_3 30, 300 3 TS2 20480 20480 TEST_A PART_TEST_A_4 40, 400 4 TS3 20480 20480 2. 먼저 Local Prefixed Index 생성 create Index ix_test_a on test_a(col1, col2) Local (Partition in_test_a_1 tablespace ts0, Partition in_test_a_2 tablespace ts1, Partition in_test_a_3 tablespace ts2, Partition in_test_a_4 tablespace ts3); column Index_name format a12 column Partition_name format a13 column hv format a10 column pp format 99 column Status format a8 column Initial_ex format 999999 column NE format 999999 column tbsn format a5 select Index_name, Partition_name, high_value hv, Partition_position pp, status Status, tablespace_name tbsn, initial_extent initial_ex, next_extent NE from user_ind_partitions order by idxn, pn Index_name Partition_name HV PP Status TBSN Initial_ex NE IX_TEST_A IN_TEST_A_1 10, 100 1 USABLE TS0 20480 20480 IX_TEST_A IN_TEST_A_2 20, 200 2 USABLE TS1 20480 20480 IX_TEST_A IN_TEST_A_3 30, 300 3 USABLE TS2 20480 20480 IX_TEST_A IN_TEST_A_4 40, 400 4 USABLE TS3 20480 20480 column Index_name format a12 column Partitioning_type format a10 column pc format 999 Partition Table 9

column pkc format 999 select Index_name, Partitioning_type pt, Partition_count pc, Partitioning_key_count pkc, Locality, alignment from user_part_indexes INDEX_NAME PT PC PKC LOCAL ALIGN IX_TEST_A RANGE 4 2 LOCAL PREFIXED PT : Partition Type, PC : Partition Count, PKC : Partition Key Count Local : Locality, ALIGN : Alignment 3. Primary Key Constraint 생성 SQL> alter table test_a add constraint pk_test_a 2 Primary key ( col1, col2 ); Table altered. 4. 생성되어있던 IX_TEST_A Index를 Drop하여본다. SQL> drop Index ix_test_a; drop Index ix_test_a * ERROR at line 1: ORA-02429: cannot drop Index used for enforcement of unique/primary key Ora-2429 Error에서볼수있듯이기존에생성되어있던 IX_TEST_A Index가 Primary Key Constraint와연계되어사용되는것을확인할수있다. 이처럼 Primary Key Constraint를생성할때 Primary Key로사용되는 Column들로이루어진인덱스가이미존재하는경우, 기존에존재하는인덱스를사용하게된다. 이때사용되는인덱스는 Unique/Non-unique, Local/Global Partitioned Index 여부에관계없이사용하게된다. 위의예에서 IX_TEST_A Index는 Non-unique Index로생성되었으며, Primary Key Constraint를생성할때이인덱스가사용되었다. 그러나이경우데이타가 Unique한경우에만적용되며, 그렇지않은경우에는 ora-2437 : Primary Key Violated Error가발생하게된다. Primary Key Constraint를 Drop할때, 생성되어있던인덱스가 Unique Index 였다면함께 Drop되게되며, 이는해당인덱스가 Local이든 Global이든동일하게적용된다. Partition Table 10

PARTITION TABLE의관리를위한 COMMAND 일반테이블 Partition하기이를위해서는 Export/import Method, Subquery를이용해데이타를입력하는방법그리고 Partition Exchange 명령어를이용하는방법이있다. Export/import 방법 1. 테이블을 Export한다. exp usr/pswd tables=numbers file=exp.dmp 2. 백업받은테이블을제거한다. drop table numbers; 3. 파티션테이블을생성한다. create table numbers (qty number(3), name varchar2(15)) Partition by range (qty) (Partition p1 values less than (501), Partition p2 values less than (maxvalue)); 4. ignore=y 를사용하여데이타를 Import 한다. imp usr/pswd file=exp.dmp ignore=y Subquery를이용한방법 1. 파티션테이블을생성한다. create table partbl (qty number(3), name varchar2(15)) Partition by range (qty) (Partition p1 values less than (501), Partition p2 values less than (maxvalue)); 2. Subquery 를이용하여파티션테이블에데이타를입력한다. insert into partbl (qty, name) select * from origtbl; Partition Exchange 명령어를사용하는방법 ALTER TABLE EXCHANGE PARTITION은파티션테이블을일반테이블로, 또파티션되어있지않는테이블을파티션테이블로도변경시킬때사용가능하다. 이를위해서는중간의 Dummy Table을이용한다. SQL> CREATE TABLE p_emp 2 (sal NUMBER(7,2)) 3 PARTITION BY RANGE(sal) 4 (Partition emp_p1 VALUES LESS THAN (2000), Partition Table 11

5 Partition emp_p2 VALUES LESS THAN (4000)); Table created. SQL> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL 7369 SMITH CLERK 7902 17-DEC-80 800 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 7521 WARD SALESMAN 7698 22-FEB-81 1250 7566 JONES MANAGER 7839 02-APR-81 2975 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 7698 BLAKE MANAGER 7839 01-MAY-81 2850 7782 CLARK MANAGER 7839 09-JUN-81 2450 7788 SCOTT ANALYST 7566 19-APR-87 3000 7839 KING PRESIDENT 17-NOV-81 5000 7844 TURNER SALESMAN 7698 08-SEP-81 1500 7876 ADAMS CLERK 7788 23-MAY-87 1100 7900 JAMES CLERK 7698 03-DEC-81 950 7902 FORD ANALYST 7566 03-DEC-81 3000 7934 MILLER CLERK 7782 23-JAN-82 1300 14 rows selected. SQL> CREATE TABLE dummy_y as SELECT sal FROM emp WHERE sal<2000; Table created. SQL> CREATE TABLE dummy_z as SELECT sal FROM emp WHERE sal BETWEEN 2000 AND 3999; Table created. SQL> alter table p_emp exchange Partition emp_p1 with table dummy_y; Table altered. SQL> alter table p_emp exchange Partition emp_p2 with table dummy_z; Table altered. Partition Table 12

PARTITION TABLE의관리를위한 COMMAND 여러개의파티션으로분리된테이블중일부의파티션만가진테이블생성하기데이타를 Export한후필요한파티션으로이루어진테이블을생성한다. create table year (col1 date) Partition by range (col1) (Partition october values less than ('01-NOV-1999) tablespace october, Partition november values less than ('01-DEC-1999) tablespace november, Partition december values less than (maxvalue) tablespace december); 데이타를 Import 한다 >imp username/password file=expdat.dmp fromuser=<owner> touser=<owner> tables=(year:october,year:november,year:december) Partition을 Add 하는방법아래와같이 Partition Table이생성되어있다고가정한다. SQL> create table part_tbl ( in_date char(8) Primary key, empno number, ename varchar2(20), job varchar2(20) ) Partition by range (in_date) (Partition part_tbl_03 value less then ('20000331') tablespace pts_03, Partition part_tbl_04 value less then ('20000430') tablespace pts_04, Partition part_tbl_05 value less then ('20000531') tablespace pts_05, Partition part_tbl_06 value less then ('20000630') tablespace pts_06, Partition part_tbl_07 value less then ('20000731') tablespace pts_07, Partition part_tbl_08 value less then ('20000831') tablespace pts_08, Partition part_tbl_09 value less then ('20000930') tablespace pts_09, Partition part_tbl_10 value less then ('20001031') tablespace pts_10 ); 11 월과 12 월에대해 Partition 을 Add 하고싶은경우다음과같이할수있다. Partition Table 13

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; PARTITION TABLE의관리를위한 COMMAND 특정 Partition을삭제하는방법 8월에해당하는 Partition을없애고싶은경우는다음과같이실행한다. SQL> alter table part_tbl drop Partition part_tbl_08; Drop된후에새로 8월에해당하는데이타가입력되면, 9월의 Partition이 less then ( 20000930 ) 으로되어있으므로 9월에해당하는 Partition에저장된다. 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); 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; 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; Partition Table 14

특정 Partition의 Data를 Truncate하는방법 Partition의 Data를모두삭제하려면 Truncate하는방법을사용할수가있는데, Truncate는 Rollback이불가능하며특정 Partition 전체를삭제하므로주의하여사용하여야한다. SQL> alter table part_tbl truncate Partition part_tbl_02; 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 값만변경한다. 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; 으로처리한다. Backup과 Recovery 기능여기서는 Partitioned Table에대한 Backup(Export/Iimport) 내용을예를통해서살펴보도록한다. Export Partitioned Table을위한 Export는다음과같이분류할수있다. Table-level Export 기존의 Table Export처럼 Table 전체를 Export하는경우이다. 이는 Emp Table(Partitioned 또는 Non-Partitioned) 전체를 Export하는경우이다. 예 ) $ exp scott/tiger tables=emp file=emp.dmp Partition Table 15

Partition-level Export 이는 Partition Table의일부 Partition만을 Export하는것으로, Full Mode의 Export시에는사용하지못하고, Table 단위의 Export시에만가능하다. 예 ) $ exp scott/tiger tables=emp:px file=exp_par.dmp ':' 을이용하여Partition 이름을지정하며이경우 Emp Table의 px Partition만을 Export. 또한다음과같이두가지경우를 Level 을혼용하여사용하는것도가능하다. 예 ) $ exp scott/tiger tables=emp:px file=exp_par.dmp ':' 을이용하여Partition 이름을지정하며이경우 Emp Table의 px Partition만을 Export. 또한다음과같이두가지경우를 Level 을혼용하여사용하는것도가능하다. 예 ) $ exp scott/tiger tables=(emp:px, sales) file=both.dmp Sales Table은전부를, Emp Table에서는 px Partition만을 Export. Import Export와마찬가지로 Import도다음의두가지로분류할수있다. Table-level Import a. Partitioned 또는 Non-Partitioned Table 전체를 Import한다. b. 모든 Import Mode (full, user, table) 에서사용된다. 예 ) $ imp scott/tiger file=wookpark.dmp tables=emp emp table(partitioned 또는 non-partitioned) 전체를 Import. Partition-level Import a. Export Dump File에서 (full, user, table 중어떠한 Mode를이용하여 Export했건간에 ) Partitioned Table의일부 Partition만을 Import한다. b. Table Import Mode에서만사용가능하다. 예 ) $ imp scott/tiger file=wookpark.dmp tables=emp:px emp table의 px Partition만을 Import. ':' 을이용하여 Partition을지정. 테이블단위의 Import시우선 Table Creation 문장을수행하고 Row Insert문을수행하는것과마찬가지로, Partition-level Import도우선 Partitioned Table의생성문장을수행하고 Row Insert문을수행하게된다. 따라서 ignore=y option 등을적절히사용하면, Non-Partitioned Table과 Partitioned Table간의변경, Partitioned Table의구조변경등을수행할수있게된다. 다음에는그중몇가지예이다. Partition Table 16

파티션되어있지않는테이블을 Exp, Imp를이용하여파티션하는예 a. 파티션되어있지않은테이블을 Export한다. $ exp scott/tiger file=wookpark.dmp tables=emp b. 해당 Table 을 Drop 한다. SQL> drop table emp c. 파티션테이블을생성한다 SQL> create table emp ( empno number(4) not null,... ) Partition by range (empno) (Partition emp1 values less than (1000) tablespace ts1, Partition emp2 values less than (2000) tablespace ts2, Partition emp3 values less than (3000) tablespace ts3) ; d. Import 한다. $ imp scott/tiger file=wookpark.dmp tables=emp ignore=y Partitioned Table의 Partition들을 exp, imp를이용하여 Merge하는예 a. Merge의대상이되는 Partition을 Export한다. $ exp scott/tiger file=wookpark.dmp tables=emp:emp2 b. Merge 의대상이되는 Partition 을 alter table... 문장으로 Drop 한다. SQL> alter table emp drop Partition emp2 ; c. Import 한다. $ imp scott/tiger file=wookpark.dmp tables=emp:emp2 ignore=y 이후 emp Table을확인하면, emp2 Partition에있던 Data가 emp3 Partition 에 Merge되어있음을확인할수있다. Partition Table 17

Toubleshooting ORA-14097 ALTER TABLE EXCHANGE PARTITION 명령어를실행할때두테이블간의데이타타입등이같지않기때문에발생하는에러이다. ORA-14097 : Clumn type or size mismatch in ALTER TABLE EXCHANGE PARTITION. * Cause : The corresponding columns in the tables specified in the ALTER TABLE EXCHANGE PARTITION are of different type or size. * Action : Ensure that the two tables have the same number of columns with the same type and size. on ALTER TABLE EXCHANGE PARTITION. SQL> create table ptab ( 2 c1 integer, 3 c2 varchar2(20)) 4 Partition by range (c1) 5 (Partition ptab_10 values less than (10), 6 Partition ptab_20 values less than (20), 7 Partition ptab_max values less than (maxvalue)); Table created. SQL> create table etab ( 2 c1 integer, 3 c2 varchar2(20), 4 c3 integer); Table created. 이있다고가정할때다음을실행하고자하는경우 SQL> alter table ptab 2 exchange Partition ptab_20 with table etab; exchange Partition ptab_20 with table etab * ERROR at line 2: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION 이처럼 ora-14097 오류가발생한다. 이경우는 SQL> alter table etab set unused column c3; Table altered. SQL> alter table etab drop unused columns; Table altered. 로두테이블간의일치하지않는칼럼을 Drop 후실행한다. Partition Table 18

SQL> alter table ptab 2 exchange Partition ptab_20 with table etab; Table altered. ORA-14038 ORA-14038 : GLOBAL Partitioned Index must be Prefixed. Global non-prefixed Indexes는생성할수없다. 이는인덱스생성시항상인덱스맨앞의칼럼으로파티션됨을의미한다. ORA-376 ERROR, ORA-14404 ERROR Partitioned Table의 Partition이포함되어있는데이타파일이 OS Level에서삭제된경우, 해당데이타파일을 Offline Drop하고 Open 한후해당 Table을 Access하고자하는경우다음과같은 Error가유발된다. ORA-00376 : File cannot be read at this time * Cause : An attempt was made to read from a file that is not readable. The most likely cause is that the file is off line. * Action : Check the state of the file. Bring the file online, if necessary. 그리고, 해당 Tablespace를 Drop하고자하면다음과같은 Error가유발된다. ORA-14404 : Partitioned table contains Partitions in a different tablespace. * Cause: An attempt was made to drop a tablespace which contains tables whose Partitions are not completely contained in this tablespace. * Action: Find tables with Partitions which span the tablespace being dropped and some other tablespace(s). Drop these tables or move Partitions to a different tablespace. 이경우다음과같은절차로조치가능하다. 예를들어, DEPT라는 Partitioned Table 중에서 PART2라는 Partition이존재하는 TS_PART2의 Datafile이유실되었으며, 해당 Datafile을 Offline Drop한후 Open 하였다고가정한다. Partition Table 19

Toubleshooting 1. 가장간단하게해당 Partitioned Table 전체를 Drop한후 Recreate한다. a. drop table DEPT ; b. drop tablespace TS_PART2 including contents ; c. tablespace recreate. d. table rebuild. 이과정의문제점은전체 Partitioned Table을복구해야하므로시간이오래소요된다는것이다. 2. 해당 Partition만을 Drop후재생성한다. a. alter table DEPT drop Partition PART2 ; b. drop tablespace TS_PART2 including contents ; c. tablespace recreate. d. add Partition or split Partition. e. 해당 Partition만의 Data를 Reload. Partitioned Table 전체 Data를 Reload하는것보다짧은시간이소요된다. 3.Temporary Table을생성하여 Exchange한다. a. DEPT 테이블과동일한구조의 Dummy Table을다른 Tablespace에생성한다. create table DEPT_TEMP as select * from DEPT where 1=2; b. 이 Table을문제의 Partition과 Exchange한다. alter table DEPT exchange Partition PART2 with table DEPT_TEMP without validation ; c. drop tablespace TS_PART2 including contents ; d. 새로운 Partition에필요한 Data를 Load한다. 이때, 제3의테이블에 Load한후동일한방법으로 Exchange를하여도무방하다. Index Unusable되는경우 Non-Partitioned Index나 Partitioned Index의파티션은일부오퍼레이션에의해인덱스가 Unusable 상태가될수있다. 이처럼Unusable 상태가된인덱스나인덱스파티션을 SELECT하거나 DML 을시도하면오류가발생하게된다. 그러나인덱스가 Unusable에빠진인덱스를읽어야하는파티션을제외한다른파티션만을읽거나 DML 을수행하는작업은오류가발생하지않는다. 어떤파티션이 Unusable 상태가되면그파티션을사용하기전에 Rebuild하여야한다. 단, Unusable 상태가된인덱스를다시만들기전에해당파티션을 Split이나 Rename 이가능하며, Unusable 상태인 Global Index를 Drop하는것도가능하다. 파티션을 Index Unusable 상태로만들수있는작업은어떠한것이있는지살펴보자. Partition Table 20

Direct path Load의경우 Direct path SQL*Loader 수행후인덱스가테이블의해당데이타보다이전것이면, Unusable 상태가된다 (Oracle7에서는인덱스가 Direct Load State가되었다고표현한다 ). 인덱스가테이블의데이타보다이전상태라는것은데이타를 Load한후인덱스를생성하는중에 Space 부족등의원인으로오류가발생하였거나, SKIP_INDEX_ MAINTENANCE Option을사용한경우이다. ROWID 가변경되는경우 ALTER TABLE MOVE PARTITION과같이 ROWID를변화시키는작업은영향받는 Local Index와전체 Global Index를 Unusable 상태가되게한다. ROWID를지우는작업 ALTER TABLE TRUNCATE PARTITION이나 DROP PARTITION과같이테이블의 Row 를지우는경우영향받는 Local Index Partition과모든 Global Index Partition을 Unusable 상태로만든다. 테이블 Partition 정의를변경하는경우 ALTER TABLE SPLIT PARTITION은 Local Index의 Partition Definition 은변경시키지만, 자동으로인덱스를새로운 Definition에맞게 Rebuild하지않기때문에영향받는 Local Index Partition을 Unusable 상태로만든다. 또한이것은 ROWID를변경시키기때문에모든 Global Index Partition을 Unusable 상태로만든다. 인덱스 Partition 정의를변경하는경우 ALTER INDEX SPLIT PARTITION은 Index의 Definition은변경시키지만, 영향받은 Partition은 Rebuild시키지않는다. 이작업은영향받는인덱스파티션부분을 Unusable 상태로만든다. 그러나 Global Index의경우는그대로 Usable 상태로된다. Partition Table 21

한국오라클 ( 주 ) 서울특별시강남구삼성동 144-17 삼화빌딩대표전화 : 2194-8000 FAX : 2194-8001 한국오라클교육센타서울특별시영등포구여의도동 28-1 전경련회관 5 층, 7 층대표전화 : 3779-4242~4 FAX : 3779-4100~1 대전사무소대전광역시서구둔산동 929 번지대전둔산사학연금회관 18 층대표전화 : (042)483-4131~2 FAX : (042)483-4133 대구사무소대구광역시동구신천동 111 번지영남타워빌딩 9 층대표전화 : (053)741-4513~4 FAX : (053)741-4515 부산사무소부산광역시동구초량동 1211~7 정암빌딩 8 층대표전화 : (051)465-9996 FAX : (051)465-9958 울산사무소울산광역시남구달동 1319-15 번지정우빌딩 3 층대표전화 : (052)267-4262 FAX : (052)267-4267 광주사무소광주광역시서구양동 60-37 금호생명빌딩 8 층대표전화 : (062)350-0131 FAX : (062)350-0130 고객에게완전하고효과적인정보관리솔루션을제공하기위하여오라클사는전세계 145 개국에서제품, 기술지원, 교육및컨설팅서비스를제공하고있습니다. http://www.oracle.com/ http://www.oracle.com/kr