행위

"파티션 테이블 로컬인덱스 생성"의 두 판 사이의 차이

DB CAFE

(→‎UNUSABLE 파티션 테이블 테스트)
(→‎로컬 파티션 인덱스 초간단 생성 팁)
 
(사용자 2명의 중간 판 39개는 보이지 않습니다)
1번째 줄: 1번째 줄:
 
__TOC__
 
__TOC__
−
== 로컬 인덱스 생성 ==
+
== 로컬 파티션 인덱스 생성 ==
−
 
+
{{틀:타이틀 투명
−
대용량의 파티션된 로컬 인덱스를 한꺼번에 생성할 때 부하도 크고 시간도 오래 걸린다.
+
|제목=1.INDEX 생성(UNUSABLE 옵션 적용)
−
다음과 같이 UNUSABLE 옵션으로 생성한 다음 각 로컬 인덱스들을 REBUILD하면
+
|아이콘=arrow_downward
−
부하도 적고 시간도 절약된다.
+
}}
−
----
+
<source lang=sql>CREATE INDEX IX_TBABC10_L1 ON TBABC10(YM, PROC_CD, PROC_STAT)  
−
+
      LOCAL (
−
1.(필요시) TEMP 테이블스페이스의 크기 늘려줌
 
−
  - 메모리에서 정렬이 모두 이루어지지 않는 경우 TEMP 테이블스페이스의 크기도 늘려줘야 함
 
−
  - 임시 테이블스페이스를 별도의 큰 tempfile(datafile이 아닌)로 구성(시간 단축)
 
−
  - 오라클 사용자의 TEMPORARY TABLESPACE IMSI로 변경
 
−
 
−
2.(필요시) PGA 영역의 크기를 늘려줌
 
−
  - init(spfile)의 Parameter 조정
 
−
  - HASH_AREA_SIZE의 1/2을 SORT_AREA_SIZE로 사용 가능
 
−
 
−
3.UNUSABLE된 인덱스를 ACCESS하지 않도록 설정(9i 이상)
 
−
<source lang=sql>
 
−
  SQL> ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
 
−
</source>
 
−
4.PARALLEL이 가능하도록, SORT영역을 크게, 한번에 읽는 블록의 갯수가 많도록 설정
 
−
<source lang=sql>
 
−
  SQL> ALTER SESSION ENABLE PARALLEL DDL;
 
−
  SQL> ALTER SESSION ENABLE PARALLEL DML;
 
−
  SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;
 
−
  SQL> ALTER SESSION SET SORT_AREA_SIZE=512000000;
 
−
  SQL> ALTER SESSION SET SORT_AREA_RETAINED_SIZE=512000000;
 
−
  SQL> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=256;
 
−
</source>
 
−
5.INDEX 생성(UNUSABLE 옵션 적용)
 
−
<source lang=sql>
 
−
  SQL> CREATE INDEX IX_TBABC10_L1 ON TBABC10(YM, PROC_CD, PROC_STAT)  
 
−
              LOCAL (
 
 
                       PARTITION PT_TBABC10_200711 ... ,
 
                       PARTITION PT_TBABC10_200711 ... ,
 
                       PARTITION PT_TBABC10_200712 ... ,
 
                       PARTITION PT_TBABC10_200712 ... ,
40번째 줄: 14번째 줄:
 
                       PARTITION PT_TBABC10_999999 ...   
 
                       PARTITION PT_TBABC10_999999 ...   
 
                     )
 
                     )
−
                UNUSABLE ;
+
    UNUSABLE  
 +
    ONLINE      -- DML 영향도 없도록     
 +
;
 
</source>  
 
</source>  
−
6.각 파티션별로 REBUILD
 
−
  가장 많이 ACCESS(최근 사용)되는 파티션부터 생성
 
−
<source lang=sql>
 
−
  SQL> ALTER INDEX IX_TBABC10_L1 REBUILD PARTITION PT_TBABC10_999999
 
−
                      TABLESPACE TS_TBABC10_999999 PARALLEL(DEGREE 8) NOLOGGING;
 
−
  SQL> ALTER INDEX IX_TBABC10_L1 REBUILD PARTITION PT_TBABC10_200803
 
−
                      TABLESPACE TS_TBABC10_200803 PARALLEL(DEGREE 8) NOLOGGING;
 
−
                :
 
−
  SQL> ALTER INDEX IX_TBABC10_L1 NOPARALLEL LOGGING;
 
−
</source>
 
−
특히, PK(PRIMARY KEY)를 생성하는 경우 ALTER TABLE ... ADD CONSTRAINTS ... 명령을
 
−
이용하면 위와같이 작업할 수 없다. 그러므로 다음과 같은 순서로 작업하면 된다.
 
−
<source lang=sql>
 
−
1) CREATE UNIQUE INDEX ... UNUSABLE;
 
−
2) ALTER INDEX ... REBUILD ... ;
 
−
3) ALTER INDEX ... NOPARALLEL LOGGING;
 
−
4) ALTER TABLE ... ADD CONSTRAINTS ... PRIMARY KEY (...) ;
 
−
</source>
 
  
−
== UNUSABLE 파티션 테이블 테스트 ==
 
−
* 테스트목적 : 파티션테이블에  UNIQUE 인덱스가 UNUSABLE 일때 데이터를 입력할수 있는지?
 
−
* 결론      : '''UNIQUE 인덱스가 UNUSABLE 일때는 데이터를 입력할수 없다.'''
 
−
              '''일반 인덱스는 UNUSABLE 일때는 데이터를 입력 할수 있다.'''
 
  
−
-- 파티션 테이블 UNUSABLE 생성/변경 테스트
+
{{틀:타이틀 투명
 +
|제목=2.각 파티션별로 REBUILD + NOLOGGING 모드
 +
- 가장 많이 ACCESS(최근 사용)되는 파티션부터 생성
 +
|아이콘=arrow_downward
 +
}}
 
<source lang=sql>
 
<source lang=sql>
−
-- 1.테이블 생성
+
ALTER INDEX IX_TBABC10_L1 REBUILD PARTITION PT_TBABC10_999999
 +
TABLESPACE TS_TBABC10_999999 PARALLEL(DEGREE 8) NOLOGGING;
  
−
CREATE TABLE KCY_UNIQUE_IX_TEST
+
ALTER INDEX IX_TBABC10_L1 REBUILD PARTITION PT_TBABC10_200803
−
(
+
TABLESPACE TS_TBABC10_200803 PARALLEL(DEGREE 8) NOLOGGING;
−
  STR_CD              VARCHAR2(5 BYTE)          NOT NULL,
+
....
−
  AGG_DT              VARCHAR2(8 BYTE)          NOT NULL,
+
....
−
  ITEM_CD            VARCHAR2(14 BYTE)        NOT NULL
+
</source>
−
)
 
−
TABLESPACE TS_MIG
 
−
NOLOGGING
 
−
PARTITION BY RANGE (AGG_DT)
 
−
(
 
−
  PARTITION PT_1804 VALUES LESS THAN ('20180499') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG,
 
−
  PARTITION PT_1805 VALUES LESS THAN ('20180599') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG,
 
−
  PARTITION PT_1806 VALUES LESS THAN ('20180699') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG,
 
−
  PARTITION PT_1807 VALUES LESS THAN ('20180799') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG,
 
−
  PARTITION PT_1808 VALUES LESS THAN ('20180899') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG,
 
−
  PARTITION PT_1809 VALUES LESS THAN ('20180999') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG,
 
−
  PARTITION PT_1810 VALUES LESS THAN ('20181099') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG,
 
−
  PARTITION PT_1811 VALUES LESS THAN ('20181199') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG,
 
−
  PARTITION PT_1812 VALUES LESS THAN ('20181299') NOLOGGING NOCOMPRESS TABLESPACE TS_MIG,
 
−
  PARTITION PT_MAX VALUES LESS THAN (MAXVALUE)
 
−
    NOLOGGING
 
−
    NOCOMPRESS
 
−
    TABLESPACE TS_MIG
 
−
)
 
−
NOCOMPRESS
 
−
NOCACHE
 
−
PARALLEL 8
 
−
MONITORING;
 
  
−
-- 2. UNIQUE 인덱스 생성
 
  
−
CREATE UNIQUE INDEX UX_KCY_UNIQUE_IX_01 ON KCY_UNIQUE_IX_TEST(ITEM_CD,AGG_DT)
+
{{틀:타이틀 투명
 +
|제목=3.LOGGING 모드로 변경
 +
|아이콘=arrow_downward
 +
}}
 +
<source lang=sql>
 +
ALTER INDEX IX_TBABC10_L1 NOPARALLEL LOGGING;
 +
</source>
  
−
            LOCAL (
+
----
−
                    PARTITION PT_1804,
+
== 로컬 파티션 인덱스 초간단 생성 팁 ==
−
                    PARTITION PT_1805,
 
−
                    PARTITION PT_1806,
 
−
                    PARTITION PT_1807,
 
−
                    PARTITION PT_1808,
 
−
                    PARTITION PT_1809,
 
−
                    PARTITION PT_1810,
 
−
                    PARTITION PT_1811,
 
−
                    PARTITION PT_1812,
 
−
                    PARTITION PT_MAX 
 
−
                )
 
−
            UNUSABLE ;
 
  
−
-- 3.데이터 입력
+
{{틀:타이틀 투명
−
INSERT INTO KCY_UNIQUE_IX_TEST (STR_CD,AGG_DT,ITEM_CD) VALUES ('A','20180401','2222');            
+
|제목=1.파티션된 로컬 인덱스를 UNUSABLE 옵션으로 생성
 +
|아이콘=arrow_downward
 +
}}
 +
<source lang=sql>
 +
create index ix_sales_01
 +
    on sales(col1,col2,....)
 +
local unusable
 +
online
 +
tablespace ts_idx_01;
 +
</source>
  
−
-- 4.UNUSABLE 조회
+
{{틀:타이틀 투명
−
SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS
+
|제목=2.로컬 인덱스 REBUILD 용 SQL생성 후 실행
−
  FROM DBA_IND_PARTITIONS
+
|아이콘=arrow_downward
−
WHERE INDEX_OWNER = 'MIG_ADM'
+
}}
−
  AND STATUS='UNUSABLE';
+
<source lang=sql>
−
+
select 'alter index emp.'|| index_name ||' rebuild partition ' || partition_name || ' parallel 32;'
−
+
   from dba_ind_partitions
−
-- 5.인덱스 리빌드 
+
where index_name = 'ix_sales_01';
−
ALTER INDEX MIG_ADM.UX_KCY_UNIQUE_IX_01 REBUILD PARTITION PT_1804;
 
−
 
 
−
-- 5. 인덱스 REBUILD 처리DDL 생성 
 
−
SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD PARTITION '||PARTITION_NAME||';'
 
−
  FROM DBA_IND_PARTITIONS WHERE STATUS='UNUSABLE';
 
−
 
 
−
-- 6. 인덱스 UNUSABLE 파티션 삭제 
 
−
SELECT 'DROP INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD PARTITION '||PARTITION_NAME||';'  
 
−
   FROM DBA_IND_PARTITIONS WHERE STATUS='UNUSABLE';
 
−
 
 
−
SELECT *
 
−
FROM KCY_UNIQUE_IX_TEST;
 
−
 
 
−
-- 6. 테이블 UNUSABLE 상태로 변경 
 
−
ALTER INDEX MIG_ADM.UX_KCY_UNIQUE_IX_01 MODIFY PARTITION PT_1804 UNUSABLE;
 
 
</source>
 
</source>
 +
----
 +
[[Category:oracle]]

2023년 6월 21일 (수) 00:06 기준 최신판

thumb_up 추천메뉴 바로가기

1 로컬 파티션 인덱스 생성[편집]

 arrow_downward 1.INDEX 생성(UNUSABLE 옵션 적용) 

CREATE INDEX IX_TBABC10_L1 ON TBABC10(YM, PROC_CD, PROC_STAT) 
       LOCAL (
                      PARTITION PT_TBABC10_200711 ... ,
                      PARTITION PT_TBABC10_200712 ... ,
                      PARTITION PT_TBABC10_200801 ... ,
                      PARTITION PT_TBABC10_200802 ... ,
                      PARTITION PT_TBABC10_200803 ... ,
                      PARTITION PT_TBABC10_999999 ...  
                    )
     UNUSABLE 
     ONLINE       -- DML 영향도 없도록      
;


 arrow_downward 2.각 파티션별로 REBUILD + NOLOGGING 모드 - 가장 많이 ACCESS(최근 사용)되는 파티션부터 생성 

ALTER INDEX IX_TBABC10_L1 REBUILD PARTITION PT_TBABC10_999999
 TABLESPACE TS_TBABC10_999999 PARALLEL(DEGREE 8) NOLOGGING;

ALTER INDEX IX_TBABC10_L1 REBUILD PARTITION PT_TBABC10_200803 
 TABLESPACE TS_TBABC10_200803 PARALLEL(DEGREE 8) NOLOGGING;
.... 
....


 arrow_downward 3.LOGGING 모드로 변경 

ALTER INDEX IX_TBABC10_L1 NOPARALLEL LOGGING;

2 로컬 파티션 인덱스 초간단 생성 팁[편집]

 arrow_downward 1.파티션된 로컬 인덱스를 UNUSABLE 옵션으로 생성 

create index ix_sales_01
    on sales(col1,col2,....)
 local unusable 
online
tablespace ts_idx_01;

 arrow_downward 2.로컬 인덱스 REBUILD 용 SQL생성 후 실행 

select 'alter index emp.'|| index_name ||' rebuild partition ' || partition_name || ' parallel 32;'
  from dba_ind_partitions
 where index_name = 'ix_sales_01';
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=파티션_테이블_로컬인덱스_생성&oldid=8714"