wif LiNoUz/Oracle,Sql
dw에서는 로컬인덱스가 일반적
크라소니
2014. 6. 16. 03:39
http://wiki.gurubee.net/pages/viewpage.action?pageId=26742034
dw에서는 로컬인덱스가 일반적, 글로벌인덱스는 oltp성에 일반적
파티셔닝 인덱스
- 로컬 인덱스(local index) : 테이블과 같은 파티셔닝. 모든 테이블에 대해 그 테이블 파티션만 인덱싱.
- 글로벌 인덱스(global index) : 범위나 해시로 파티션된 하나의 인덱스 파티션은 모든 테이블 파티션을 가리킨다.
- 리스트나 복합 인덱스를 원할 경우, 로컬인덱스 사용해야 한다.
로컬 인덱스와 글로벌 인덱스
- DW시스템에서는 로컬인덱스를, OLTP 시스템에서는 글로벌인덱스가 일반적이다.
- 가용성
- 로컬인덱스는 더 많은 가용성을 지원한다. (문제 발생 시 해당 파티션에 한정됨.)
- 파티션 유지관리 작업 시 유연성.
- 로컬인덱스의 경우, 파티션 이동 시 관계된 로컬인덱스 재생성만 하면됨.
- 글로벌 인덱스는 모든 인덱스 파티션이 재생성 되어야 한다.
- 특정 시점으로 파티션 복구 작업의 경우.
- 모든 로컬 파티션 인덱스는 같은 시점 복구 가능
- 글로벌인덱스의 경우, 해당 객체에 대해 재생성이 필요
로컬 인덱스
로컬 prefixed 인덱스 : 파티션 키 컬럼이 인덱스의 선두 컬럼인 인덱스
로컬 non-prefixed 인덱스 : 인덱스 선두컬럼으로 파티션 키를 가지지 않는 인덱스. 파티션 키를 포함할수도, 하지않을 수도 있음.
- 둘 다 유일성(uniqueness, non-prefixed 인덱스는 파티션 키를 포함하는 경우) 지원
- 로컬 prefixed 인덱스는 파티션 제외 허용
- 로컬 non-prefixed 인덱스는 파티션 제외를 지원하나, 강제하지 않음. (허용 할 수도, 하지 않을수도 있음)
파티션 제외
인덱스 액세스로 시작하는 쿼리에 대해,
처리 대상에서 파티션 제외 가능한지 쿼리 조건(predicate) 에 따른 차이 테스트
SQL> CREATE TABLE partitioned_table
( a int,
b int,
data char(20)
)
PARTITION BY RANGE (a)
(
PARTITION part_1 VALUES LESS THAN (2) tablespace p1 ,
PARTITION part_2 VALUES LESS THAN (3) tablespace p2
)
/
Table created.
-- 로컬 prefixed 인덱스 생성
SQL> create index local_prefixed on partitioned_table (a,b) local ;
Index created.
-- 로컬 non-prefixed 인덱스 생성
SQL> create index local_nonprefixed on partitioned_table (b) local ;
Index created.
한 파티션에 데이터를 입력하고, 통계정보 수집
SQL> insert into partitioned_table select mod(rownum-1,2)+1, rownum, 'x' from all_objects ; 39868 rows created. SQL> begin dbms_stats.gather_table_stats ( user, 'PARTITIONED_TABLE' , cascade=>TRUE ) ; end; / PL/SQL procedure successfully completed.
PART_2 파티션과 PART_2 파티션을 포함하는 테이블스페이스 P2 오프라인 변경
SQL> alter tablespace p2 offline; Tablespace altered.
Elapsed: 00:00:00.07
11:54:23 hong:ORAPIKA>
select * from partitioned_table where a=1 and b=1 ; A B DATA ---------- ---------- -------------------- 1 1 x -- 쿼리 실행계획 확인 SQL> explain plan for select * from partitioned_table where a =1 and b=1 ; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------- Plan hash value: 1622054381 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1 | 29 | 2 (0)| 00:00:01 | 1 | 1 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 29 | 2 (0)| 00:00:01 | 1 | 1 | |* 3 | INDEX RANGE SCAN | LOCAL_PREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"=1 AND "B"=1) 15 rows selected.
위에서 Pstart, Pstop 으로, 실제 사용한 파티션을 확인한다.
LOCAL_PREFIXED 인덱스를 사용한 쿼리는 성공하였음.
SQL> select * from partitioned_table where b=1; ERROR: ORA-00376: file 13 cannot be read at this time ORA-01110: data file 13: '/data/data02.dbf' no rows selected
select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 440752652 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 29 | 4 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ALL | | 1 | 29 | 4 (0)| 00:00:01 | 1 | 2 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 29 | 4 (0)| 00:00:01 | 1 | 2 | |* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 3 (0)| 00:00:01 | 1 | 2 | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"=1)
SQL> drop index local_prefixed ; Index dropped. SQL> select * from partitioned_table where a = 1 and b =1 ; A B DATA ---------- ---------- -------------------- 1 1 x -- 실행계획 확인 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 904532382 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1 | 29 | 2 (0)| 00:00:01 | 1 | 1 | |* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 29 | 2 (0)| 00:00:01 | 1 | 1 | |* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("A"=1) 3 - access("B"=1) - non-prefix index 도 파티션 제외가 가능하다 ( Pstart, Pstop 이 모두 1 ) - 쿼리에 따라서 prefix, non-prefix 인덱스 생성을 고려해야 함. - prefix 인덱스의 경우 파티션 제외를 허용하도록 쿼리에 포함해야 한다. *로컬 인덱스와 유일성 제약조건* - 파티션 키 자체가 제약조건에 포함되어야 한다. ( 파티션 내에서의 제약조건 성립 ) - 전체 테이블 데이터에 대한 제약조건은 글로벌인덱스를 사용. {code:sql} SQL> CREATE TABLE partitioned ( load_date date, id int, constraint partitioned_pk primary key(id) ) PARTITION BY RANGE (load_date) ( PARTITION part_1 VALUES LESS THAN (to_date( '01/01/2000', 'dd/mm/yyyy' )) , PARTITION part_2 VALUES LESS THAN (to_date( '01/01/2001','dd/mm/yyyy')) ) / Table created. SQL> select segment_name, partition_name, segment_type from user_segments ; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE -------------------------------- -------------------- ------------------ PARTITIONED_PK INDEX PARTITIONED_TABLE PART_1 TABLE PARTITION PARTITIONED_TABLE PART_2 TABLE PARTITION
==> PK 인덱스는 파티셔닝 되지 않음.
SQL> CREATE TABLE partitioned
( timestamp date,
id int
)
PARTITION BY RANGE (timestamp)
(
PARTITION part_1 VALUES LESS THAN
( to_date ('01-jan-2000','dd-mon-yyyy') ),
PARTITION part_2 VALUES LESS THAN
( to_date ('01-jan-2001','dd-mon-yyyy') )
)
;
Table created.
SQL> create index partition_idx on partitioned (id) local ;
cndex created.
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
-------------------------------- -------------------- ------------------
PARTITIONED PART_1 TABLE PARTITION
PARTITIONED PART_2 TABLE PARTITION
PARTITION_IDX PART_2 INDEX PARTITION
PARTITION_IDX PART_1 INDEX PARTITION
-- 위 상태에서 primary key 제약조건을 추가하면 이미 인덱스 존재하여 에러발생.
SQL> alter table partitioned add constraint partitioned_pk primary key (id ) ;
alter table partitioned add constraint partitioned_pk primary key (id )
*
ERROR at line 1:
ORA-01408: such column list already indexed
글로벌 인덱스
- prefixed 글로벌 인덱스 유형
- 파티셔닝 키가 인덱스 컬럼의 가장 선두에 위치
- 유일성을 강제하기 위해 사용됨
SQL> CREATE TABLE partitioned ( timestamp date, id int ) PARTITION BY RANGE ( timestamp ) ( PARTITION part_1 VALUES LESS THAN ( to_date('01-jan-2000','dd-mon-yyyy') ), PARTITION part_2 VALUES LESS THAN ( to_date('01-jan-2001','dd-mon-yyyy') ) ) ; Table created. SQL> CREATE INDEX partitioned_index on partitioned (id) global PARTITION BY RANGE(id) ( partition part_1 values less than (1000), partition part_2 values less than (MAXVALUE) ); Index created. -- 테이블에 기본키를 추가 SQL> alter table partitioned add constraint partitioned_pk primary key (id) ; Table altered. -- 제약조건을 걸어두면 index drop이 되지 않는다. SQL> drop index partitioned_index ; drop index partitioned_index * ERROR at line 1: ORA-02429: cannot drop index used for enforcement of unique/primary key
글로벌인덱스는 언제 사용하는가 ?
- 데이터 웨어하우스와 글로벌 인덱스
- 슬라이딩 윈도우와 인덱스
- 가장 오래된 데이터 분리, 빈 테이블과 교환
- 신규 데이터 테이블로 로딩과 인덱싱
- 신규 데이터 테이블 추가 후 파티션 테이블과 빈 파티션 exchange
- 슬라이딩 윈도우와 인덱스
SQL> CREATE TABLE partitioned ( timestamp date, id int ) PARTITION BY RANGE (timestamp) ( PARTITION fy_2004 VALUES LESS THAN ( to_date('01-jan-2005','dd-mon-yyyy') ), PARTITION fy_2005 VALUES LESS THAN ( to_date('01-jan-2006','dd-mon-yyyy') ) ) ; Table created. SQL> insert into partitioned partition (fy_2004) select to_date('31-dec-2004','dd-mon-yyyy')-mod(rownum,360), object_id from all_objects ; 39885 rows created. SQL> insert into partitioned partition (fy_2005) select to_date('31-dec-2005','dd-mon-yyyy')-mod(rownum,360), object_id from all_objects ; 39885 rows created. SQL> create index partitioned_idx_local on partitioned (id) LOCAL ; Index created. SQL> create index partition_idx_global on partitioned (timestamp) GLOBAL ; Index created. SQL> create table fy_2004 (timestamp date, id int ); Table created. SQL> create index fy_2004_idx on fy_2004 (id) ; Index created. SQL> create table fy_2006 (timestamp date, id int ); Table created. SQL> insert into fy_2006 select to_date('31-dec-2006','dd-mon-yyyy')-mod(rownum,360), object_id from all_objects ; 39892 rows created. SQL> create index fy_2006_idx on fy_2006(id) nologging ; Index created.
- 파티션 exchange
SQL> alter table partitioned exchange partition fy_2004
with table fy_2004
including indexes
without validation ;
Table altered.
SQL> alter table partitioned drop partition fy_2004 ;
Table altered.
-- 오래된 파티션 삭제 완료
-- 신규 파티션 추가
SQL> alter table partitioned add partition fy_2006 values less than (to_date('01-jan-2007','dd-mon-yyyy'));
Table altered.
SQL> alter table partitioned exchange partition fy_2006
with table fy_2006
including indexes
without validation
;
Table altered.
WITHOUT VALIDATION : 파티션에 위치할 데이터가, 파티션에 유효하다는 것을 명시.
- 인덱스 조회
select index_name, status from user_indexes ; INDEX_NAME STATUS ------------------------------ -------- FY_2004_IDX VALID PARTITION_IDX_GLOBAL UNUSABLE PARTITIONED_IDX_LOCAL N/A FY_2006_IDX VALID
SQL> select /*+ index (partitioned PARTITION_IDX_GLOBAL) */ count(*) from partitioned where timestamp between to_date ('01-mar-2006','dd-mon-yyyy') and to_date('31-mar-2006','dd-mon-yyyy') ; * ERROR at line 1: ORA-01502: index 'HONG.PARTITION_IDX_GLOBAL' or partition of such index is in unusable state
- 글로벌 인덱스 UNUSABLE 시 처리 방안
- 해당 인덱스를 사용하지 않도록 SKIP_UNUSABLE_INDEXES = TRUE 로 설정.
- 쿼리에러 발생되므로, 인덱스 재생성 진행
#Live 글로벌 인덱스 유지관리
- live환경에서 글로벌 인덱스를 유지하면서 파티션 작업을 하는 방법
- UPDATE GLOBAL INDEXES 사용
SQL> alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation UPDATE GLOBAL INDEXES ; Table altered. SQL> alter table partitioned drop partition fy_2004 UPDATE GLOBAL INDEXES ; Table altered. SQL> alter table partitioned add partition fy_2006 values less than ( to_date('01-jan-2007','dd-mon-yyyy') ) ; Table altered. SQL> alter table partitioned exchange partition fy_2006 with table fy_2006 including indexes with validation UPDATE GLOBAL INDEXES ; Table altered. SQL> select index_name, status from user_indexes; INDEX_NAME STATUS ------------------------------ -------- PARTITION_IDX_GLOBAL VALID FY_2004_IDX VALID FY_2006_IDX VALID PARTITIONED_IDX_LOCAL N/A select /*+ INDEX(PARTITIONED PARTITION_IDX_GLOBAL) */ count(*) from partitioned where timestamp between to_date ('01-mar-2006','dd-mon-yyyy') and to_date('31-mar-2006','dd-mon-yyyy') ; Execution Plan ---------------------------------------------------------- Plan hash value: 1223142522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 9 | | | |* 2 | INDEX RANGE SCAN| PARTITION_IDX_GLOBAL | 1 | 9 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TIMESTAMP">=TO_DATE(' 2006-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIMESTAMP"<=TO_DATE(' 2006-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
UPDATE GLOBAL INDEX 옵션을 사용하면 Partition exchange 작업 시 Global index를 유지할 수 있으나
글로벌인덱스 엔트리를 모두 지운 뒤 새로 추가해야 하는 작업으로, 대량의 redo 발생 및 resource 사용량 증가.
OLTP와 글로벌 인덱스
- OLTP환경에서 글로벌 인덱스를 생성하는 조건
- 빠른 액세스
- 데이터 무결성
- 가용성
SQL> create table emp
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL,
LOC VARCHAR2(13) NOT NULL
)
PARTITION BY RANGE (LOC)
(
PARTITION P1 VALUES LESS THAN ('C') TABLESPACE P1,
PARTITION P2 VALUES LESS THAN ('D') TABLESPACE P2,
PARTITION P3 VALUES LESS THAN ('N') TABLESPACE P3,
PARTITION P4 VALUES LESS THAN ('Z') TABLESPACE P4
)
Table created.
empno 컬럼에 primary key 제약조건 추가, unique index 생성 (데이터 무결성)
SQL> alter table emp add constraint emp_pk primary key (empno) ; Table altered.
DEPTNO, JOB에 GLOBAL INDEX 생성
SQL> create index emp_job_idx on emp(job) global ;
Index created.
SQL> create index emp_dept_idx on emp(deptno) global ;
Index created.
SQL> insert into emp
select e.* , d.loc
from scott.emp e, scott.dept d
where e.deptno = d.deptno
;
14 rows created.
- 데이터 분포 확인
SQL> break on pname skip 1 SQL> select 'p1' pname, empno, job, loc from emp partition(p1) union all select 'p2' pname, empno, job, loc from emp partition(p2) union all select 'p3' pname, empno, job, loc from emp partition(p3) union all select 'p4' pname, empno, job, loc from emp partition(p4) ; PN EMPNO JOB LOC -- ---------- --------- ------------- p2 7521 SALESMAN CHICAGO 7844 SALESMAN CHICAGO 7499 SALESMAN CHICAGO 7900 CLERK CHICAGO 7698 MANAGER CHICAGO 7654 SALESMAN CHICAGO p3 7566 MANAGER DALLAS 7902 ANALYST DALLAS 7876 CLERK DALLAS 7369 CLERK DALLAS 7788 ANALYST DALLAS p4 7782 MANAGER NEW YORK 7839 PRESIDENT NEW YORK 7934 CLERK NEW YORK 14 rows selected. --> LOC 컬럼으로 개별 파티션됨
- 실행계획 생성하여, 빠른 액세스에 대해 확인한다.
- UNIQUE SCAN 확인
variable x varchar2(30); begin dbms_stats.set_table_stats ( user, 'EMP', numrows=>100000, numblks=>10000 ); end; / PL/SQL procedure successfully completed. SQL> delete from plan_table ; SQL> explain plan for select empno, job, loc from emp where empno = :x ; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3656192650 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 | | | | 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| EMP | 1 | 27 | 2 (0)| 00:00:01 | ROWID | ROWID | |* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 | | | ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=TO_NUMBER(:X))
- JOB 조건으로 SELECT 시 RANGE SCAN확인
SQL> delete from plan_table ; 3 rows deleted. SQL> explain plan for select empno, job, loc from emp where job = :x ; Explained. SQL> select * from table(dbms_xplan.display); ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1000 | 27000 | 5 (0)| 00:00:01 | | | | 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| EMP | 1000 | 27000 | 5 (0)| 00:00:01 | ROWID | ROWID | |* 2 | INDEX RANGE SCAN | EMP_JOB_IDX | 400 | | 1 (0)| 00:00:01 | | | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("JOB"=:X)
- 가용성 측면에 대한 확인
SQL> alter tablespace p1 offline ; Tablespace altered. SQL> alter tablespace p2 offline ; Tablespace altered. SQL> alter tablespace p3 offline ; Tablespace altered. -- empno로 생성된 global index를 사용하여 TABLE ACCESS BY ROWID 로 데이터 조회 가능 SQL> select empno, job, loc from emp where empno = 7782 ; EMPNO JOB LOC ---------- --------- ------------- 7782 MANAGER NEW YORK -- job 으로 파티셔닝 되지 않은 상태에서, empno 를 조회하여 데이터 조회 불가 SQL> select empno, job, loc from emp where job = 'CLERK'; select empno, job, loc from emp where job = 'CLERK' * ERROR at line 1: ORA-00376: file 12 cannot be read at this time ORA-01110: data file 12: '/data/data01.dbf' -- 쿼리가 인덱스만 사용하여 TABLE ACCESS BY ROWID 가 발생하지 않는다면 데이터 조회가 가능 SQL> select count(*) from emp where job = 'CLERK' ; COUNT(*) ---------- 4