파티션 테이블(partition table) - 인덱스(index)

 파티션 테이블(partition table) - 인덱스(index)

□ 인덱스 유형 선택

<Partition Index Type>

Local	Partition Table의 대부분 속성을 같이하는 Equipartition된 형태  - Partition 단위의 Index 생성/삭제/Rebuild 가능
Gobal	Partition Table과는 달리 별도의 컬럼과 Range로 Partition한 형태  - Partition Table DDL 작업시 모든 Partition을 모두 Rebuild 해야 사용 가능함  - 비트맵 인덱스는 Local에만 적용 가능
Prefixed	Index 컬럼(Leftmost)이 Partition Key (컬럼 set)를 포함
Nonprefixed	Index 컬럼(Leftmost)이 Partition Key (컬럼 set)을 포함하지 않음  - Global Nonprefixed Index는 사용 불가

 

  - 한 파티션에서 조회하는 액세스 패스는 Local 인덱스를 사용하도록 함

  - 운용측면에서 Global Index보다는 Local Index 사용이 권장

  - Table Partition Key를 Index로 설정할 경우  Local Prefixed Index를 사용

  - PK 컬럼은 Table Partition Key를 첫번째 컬럼으로 하는 Local Prefixed Index를 사용

  - Non-partition 컬럼에 대한 빈번한 배치 작업 수행시 Local Nonprefixed Index를 사용

  - Non-partition 컬럼에 대한 Unique Index 설정시 Global Index를 사용 (OLTP)

 

테이블 전체 스캔의 경우 등 여러 파티션을 조회하는 경우는 비분할(Non-partitioned) 인덱스를 생서아여

이 인덱스를 통한 액세스가 가능하도록 한다.

 

 

예제 table create

 

CREATE TABLE ORDER_ITEMS_PT_RG_1
(
    ORDER_ID       VARCHAR2(17),
    PRODUCT_ID     VARCHAR2(7),
    ORDER_DATE     DATE,
    UNIT_PRICE     NUMBER,
    QUANTITY       NUMBER,
    NEW_ORDER_DATE VARCHAR2(10) GENERATED ALWAYS AS(TO_CHAR(ORDER_DATE,'YYYYMMDD'))
)
tablespace users
partition by range(new_order_date)(
partition p2007 values less than('20080101'),
partition p2008 values less than('20090101'),
partition p2009 values less than('20100101'),
partition p2010 values less than('20110101'),
partition p2011 values less than('20120101'),
partition p2012 values less than('20130101'),
partition pmax values less than(maxvalue));

--date insert
ALTER TABLE ORDER_ITEMS_PT_RG_1 NOLOGGING;
ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL (A 4) */
INTO ORDER_ITEMS_PT_RG_2 A
(ORDER_ID, PRODUCT_ID,ORDER_DATE,UNIT_PRICE,QUANTITY)
SELECT /*+ PARALLEL (B 4) */*
FROM TUNER.ORDER_ITEMS B;

commit;

ALTER TABLE ORDER_ITEMS_PT_RG_1 LOGGING;

select * from dba_tab_partitions
where table_name like 'ORDER_ITEMS_PT_RG_1';

 

* local과 global index를 구분하기 위해 ORDER_ITEMS_PT_RG_2 테이블도 똑같이 생성한다.

 

partition index 생성

-- partition global index

create index ORDER_ITEMS_PT_RG_1_date on order_items_pt_rg_1(new_order_date)
parallel 8;

alter index order_items_pt_rg_1_date noparallel;

 

-- partition local index
create index ORDER_ITEMS_PT_RG_2_date on order_items_pt_rg_2(new_order_date)
parallel 8
local;

alter index order_items_pt_rg_2_date noparallel;

-- index 조회해서 생성 확인
select * from dba_indexes
where table_name like 'ORDER_ITEMS_PT_RG%';

 

select * from dba_ind_partitions
where index_name like 'ORDER_ITEMS_PT_RG%';

 

global local index의 성능차이

select sum(QUANTITY)
  from ORDER_ITEMS_PT_RG_1 a
 where new_order_date between '20090601' and '20090610';

 

 

select sum(QUANTITY)
  from ORDER_ITEMS_PT_RG_2 a
 where new_order_date between '20090601' and '20090610';
 

 

 

global local 관리 차이

-- partition drop
alter table ORDER_ITEMS_PT_RG_1 drop partition p2009;  -- global
alter table ORDER_ITEMS_PT_RG_2 drop partition p2009;  -- local

 

-- index상태 조회
select table_name, index_name, status
  from dba_indexes
 where index_name like 'ORDER_ITEMS_PT_RG%';
 

select index_name ,status
  from dba_ind_partitions
 where index_name like 'ORDER_ITEMS_PT_RG%';

 

 

-- index rebuild 스크립트 작성
select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild;' as script
  from dba_indexes
 where status = 'UNUSABLE'
union
select 'ater index '||index_owner||'.'||index_name|| 'rebuild partition;' as script
  from dba_ind_partitions
 where status='UNUSABLE';
  
 -- index rebuild
alter index ORDER_ITEMS_PT_RG_1_DATE rebuild;

 

 

alter index ORDER_ITEMS_PT_RG_2_DATE rebuild;
-- ora-14086 분할영역된 인덱스는 전체를 다시 만들 수 없습니다

* local은 rebuild를 따로 해줄 필요가 없다.