티스토리 뷰

목적

  • Table Partition 뿐만 아니라 Index에 대한 Partition도 Oracle 8부터 가능하다.
    Index Partition에는 Local Prefixed Index, Local Non-prefixed Index,
    Global (Prefixed) Index 이렇게 세 가지 유형이 있다.
    이 자료는 Oracle 9i의 new feature인 UPDATE GLOBAL INDEXES에 대해 소개한다.

주의사항

  • Oracle Partitioning Option은 8~10g Standard Edition에서는 지원하지 않는다.

설명

  • Oracle 8i의 Global Index의 경우 base table의 partition이 수정된다면 (특정 partition drop 등)
    global index의 모든 partition이 영향을 받게되고 그 결과 해당 index의 모든 partition은 UNUSABLE 상태가 되어 버린다.
    즉, 각각의 index partition들에 대해 manual하게 rebuild를 시켜주거나 index 자체를 drop하고 재생성할 수 밖에 없었다.
    이러한 문제점은 global index에 대한 관리/운영을 어렵게 만들며 index rebuild/recreate를 하기 전까지
    해당 index를 사용하지 못하고 base table에 대한 DML도 수행할 수 없게 되므로 결과적으로 가용성을 떨어뜨리게 된다
  • Oracle 9i 이상에서는 base table에 대한 DDL 수행 시 UPDATE GLOBAL INDEXES라는 option을 사용해서
    global index에 대해서도 ONLINE으로 update할 수 있는 기능을 제공한다. 이를 통한 잇점은 다음과 같다.

     Usability : Global index의 모든 partition 이름을 찾아내고 각각에 대 
       해 rebuild하는 과정을 없애준다. 즉, 이전에는 두 단계 
       (step 1:Partition DDL, step 2:Rebuild Index)를 
       거쳐야 했지만 이제는 한 단계만 필요하다.  
     
     Availability : ONLINE이 가능하므로 데이터베이스가 보다 더 available 하다. 
     
    ­ Manageability : Global index가 base table과 동시에 변경이 된다.  
      Global index에 대한 rebuild를 신경쓰지 않아도 된다. 
    

    이 문서에서는 Oracle 9i 이상에서의 'Maintaining Global Indexes During Partition DDL'을
    사용하는 방법, 그리고 사용 시 고려해야 할 점들을 기술하고자 한다.

  • Maintaining Global Indexes During Partition DDL
    • 아래와 같이 UPDATE GLOBAL INDEXES라는 option을 partition
      specification 바로 뒤, 그리고 PARALLEL clause 바로 앞에 기술해 두
      면 ONLINE으로 base table에 대한 DDL을 수행하면서 global index를 그
      에 맞춰 변경시킬 수 있다.
         SQL> ALTER TABLE EMPLOYEES 
              DROP PARTITION EMP1 
              UPDATE GLOBAL INDEXES 
              PARALLEL (DEGREE 4); 
      
      Valid하고 USABLE한 상태의 global index가 변경되어지며, 다음과 같은 partition DDL에 적용될 수 있다.
      예 : SPLIT, MERGE, ADD, MOVE, COALESCE, DROP, TRUNCATE, EXCHANGE

  • Performance Considerations When Choosing to Update vs Rebuild
    • Global Index에 대해 ONLINE으로 update를 할지 아니면
      rebuild/recreate를 할지, 각각의 효과 및 영향은 무엇인지 등을 고려해야 할 것이다.
    ­
    • ONLINE update를 하게 되면 partition DDL 문장이 완료되기까지 더 많은 시간이 소요된다.
      (그리고 서비스 중인 테이블이면 심각한 오류도 겪게 될것이다 -_-)
    • ONLINE update인 경우 DROP, TRUNCATE, EXCHANGE 등의 작업을 수행할 때 data dictionary에 대한 변경만으로는
      불가능하다. Partition의 모든 row에 대한 scan이 필요하기 때문에 시간이 많이 걸린다.
    ­
    • Global index에 대한 update는 logging되기 때문에 redo와 rollback이 생성된다.
      (해당 partition DDL 작업을 rollback할 수 있는 것은 아니다.)
    • ONLINE update는 처리해야 할 row의 양이 적을 때 유용하다.
      하지만 ONLINE update가 애플리케이션의 성능을 떨어뜨리는 것은 아니다.
    • Index를 rebuild 하면 해당 index는 efficient하게 된다.
      Index rebuild를 통해 해당 index에 대한 reorganization 작업을 수행할 수 있다.

  • 파티션 키 데이터의 변경
    • 파티션을 운영 중에 키 컬럼의 데이터가 변경되어 파티션을 이동 시에 다음과 같은 에러를 발생시킨다.
      "ORA-14402: 분할영역 키 열을 수정하는 것은 분할영역 변경이 생깁니다."

      이 문제를 해결하기 위해 잘못하면 프로그램 상에서 Delete & Insert 방식으로 코딩을 하기도 하지만
      Oracle8i부터 SQL의 Syntax에 이 문제에 대한 옵션이 존재한다.

      ENABLE ROW MOVEMENT 또는 DISABLE ROW MOVEMENT

      디폴트로 DISABLE ROW MOVEMENT 가 셋팅 되어 있다. 즉 파티션간 로우의 이동이 금지되어 있었기 때문에
      위와 같은 에러를 만나게 된 것이다.

      CREATE TABLE시 이 옵션을 주거나, ALTER TABLE 명령어로 설정 값을 바꿀 수 있다.

      ALTER TABLE SALES ENABLE ROW MOVEMENT;

  • Limitations
    • UPDATE GLOBAL INDEXES 구문은 다음과 같은 인덱스 유형에는 사용할 수 없다.

      ­ Index Organized Table (IOT) 
      ­ Domain Index 
      

  • 테스트 환경 설정
    • 테스트를 위해서 먼저 partition table을 다음과 같이 생성한다.

       CREATE TABLE sales    
            (prod_id NUMBER(6)       CONSTRAINT sales_product_nn NOT NULL,  
            cust_id NUMBER          CONSTRAINT sales_customer_nn NOT NULL,  
            time_id DATE            CONSTRAINT sales_time_nn NOT NULL,  
            channel_id CHAR(1)      CONSTRAINT sales_channel_nn NOT NULL,  
            promo_id NUMBER(6),  
            quantity_sold NUMBER(3) CONSTRAINT sales_quantity_nn NOT NULL,  
            amount NUMBER(10,2)     CONSTRAINT sales_amount_nn NOT NULL,  
            cost NUMBER(10,2)       CONSTRAINT sales_cost_nn NOT NULL)         
            PARTITION BY RANGE (time_id)         
            (PARTITION Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),          
            PARTITION Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),          
            PARTITION Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),          
            PARTITION Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),          
            PARTITION Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),          
            PARTITION Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),          
            PARTITION Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),          
            PARTITION Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),          
            PARTITION Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),          
            PARTITION Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),          
            PARTITION Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),          
            PARTITION Q4_2000 VALUES LESS THAN (MAXVALUE)); 
      

      위 테이블은 time_id를 partition key로 한 range partition table이다.
      다음엔 이 sales table에 SQL*Loader를 이용하여 데이터를 loading한다.
      해당 control 화일 및 데이터 화일은 다음 디렉토리에 있다.
      이 디렉토리로 이동해서 작업을 하면 된다.

       $ORACLE_HOME/demo/schema/sales_history 
      

    Note

      Oracle 9i부터는 $ORACLE_HOME/demo/schema 서브 디렉토리에 HR(human resource), OE(order entry),
      PM(product media), SH(sales history), QS(Queued Shipping) 등의 샘플 스키마 및 데이터들이 있다.

      데이터 화일인 sh_sales.dat에 있는 time_id column에 대응되는 값은
      DATE 타입으로 실제 데이터의 포맷이 DD-MON-YYYY이므로 먼저 환경변수를 이에 맞추고 loading한다.
      (아래의 예는 csh을 사용한 경우다.)

            setenv NLS_DATE_FORMAT DD-MON-YYYY 
            sqlldr hykwon/hykwon control=sh_sales direct=true 
       

      이번 테스트에서는 partition DDL 중 EXCHANGE를 예로 들 것이다.
      이를 위해 다음과 같은 별도의 테이블을 생성한다.

            SQL> CREATE TABLE sales_exchange 
            AS 
            SELECT * FROM sales 
            WHERE 1 = 2; 
      

      ALTER TABLE EXCHANGE PARTITION은 특정 partition을 nonpartitioned
      table로, 혹은 table을 partitioned table의 한 partition으로 변환시켜 주는 역할을 한다.
      내부적으로는 data(index) segment를 교환(data dictionary의 변경)하는 것이지
      실제로 각각의 row들을 옮기는 것은 아니다.

    Example

      먼저 global index를 생성하고, UPDATE GLOBAL INDEXES option을 사용
      하지 않고 EXCHANGE partition DDL을 수행하고 문제점을 파악한다.
      그 다음엔 UPDATE GLOBAL INDEXES option을 사용한 EXCHANGE
        partition DDL을 수행하고 먼저의 문제점이 어떻게 해결되었는지를 살펴본다.
    댓글
    댓글쓰기 폼