오라클 인덱스 밸런싱,결합인덱스 생성지침
인덱스라고 하는 것은 검색시의 빠른 응답을 위해서 마련해 놓은 자료구조입니다.
인덱스는?
1. 검색속도 향상을 위한 것이다.
2. 내부적으로 B+ 트리를 이용한다.
3. 데이터의 성향에 따라서 다양한 인덱스가 존재한다.
4. INSERT/UPDATE/DELETE시는 인덱스가 성능을 약화시킨다.
5. 인덱스가 다수 존재하는 경우 조건절에 따라서 타는 인덱스가 달라진다.
CREATE [ UNIQUE | BITMAP ] INDEX index_name ON table_name(column_name)
[TABLESPACE tablespace_name];
UNIQUE : UNIQUE Index를 생성한다.
BITMAP : BITMAP Index를 생성한다.
index_name : 생성하고자 하는 인덱스 이름
table_name : 인덱스를 생성하고자 하는 테이블 이름
column_name : 인덱스로 생성하고자 하는 컬럼 이름
tablespace_name : 인덱스가 위치할(생성될) 테이블 스페이스 이름예) CREATE INDEX idx_emp ON tb_emp (empno);
tb_emp 테이블에 empno 컬럼을 이용하여 idx_emp를 생성한다.
범례)
대문자 : Reserved Word
소문자 : User Define
[ ] : Option, 지정하지 않아도 되거나 생략시 기본 설정값으로 대체됨.
INDEX
CREATE UNIQUE INDEX "I_ANSWER_ANS" ON "ANSWER"("ANS_CODE", "MEM_CODE", "QST_CODE", "CATE_CODE")
TABLESPACE SPACENAME
INITRANS 2
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
-------------------------
SELECT /*+ INDEX_DESC(ANSWER I_ANSWER_ANS) */결합인덱스의 생성지침
– 단 하나의 테이블에서 컬럼들을 참조해야 한다
– 최대 16개 컬럼을 생성할 수 있다
– 결합 인덱스의 전체 컬럼 길이는 db_block_size 파라미터의 값을 1/2을 초과하면 안된다.
– 결합 인덱스를 생성할 때는 선택도가 좋은 컬럼을 선행컬럼으로 결정해야한다(즉 앞에 둔다)
– 선행 컬럼을 결정하기 힘든 경우에는 자주 사용되는 컬럼을 선행 컬럼으로 결정한다.
– 결합인덱스의 수가 많으면 많을수록 데이터의 검색속도는 향상될 수 있으나 오히려
DML문의 수행성능은 저하될 수 있다
index의 분석
-최초로 인덱스를 생성한 이후 테이블에 DML문이 자주 발생하게 되면 밸런스 구조는 깨지고
좌,우의 구조도 달라지게 된다
이러한 현상이 발생하면 좋은 성능을 보장해 줄 수 없기 때문에 데이터 베이스 관리자는
주기적으로 또는 비 주기적으로 밸런싱이 깨진 인덱스를 분석하고 인덱스를 재구성하는 작업을
해야만 성능을 기대할 수 있다. 인덱스의 각 블록에 저장되어 있는 인덱스 키의 개수가 블록
마다 틀리게 되면 밸런싱이 깨지게 된다.
SQL> create table big_emp_cp
2 as
3 select * from big_emp;
테이블이 생성되었습니다.
SQL> create index i_big_emp_cp_empno
2 on big_emp_cp(empno);
인덱스가 생성되었습니다.
*인덱스의 밸런싱 구조상태를 확인하는 방법——————-
SQL> analyze index i_big_emp_cp_empno validate structure;
인덱스가 분석되었습니다.
SQL> select (del_lf_rows_len/ lf_rows_len) * 100 as “Balancing”
2 from index_stats;
Balancing
———-
0 <–완벽한 밸런싱을 유지함
————————————————————-
-밸런싱을 깨기위해 행 삭제
SQL> delete big_emp_cp
2 where empno > 1 and empno < 3000;
2879 행이 삭제되었습니다.
SQL> analyze index i_big_emp_cp_empno validate structure;
인덱스가 분석되었습니다.
SQL> select (del_lf_rows_len/ lf_rows_len) * 100 as “Balancing”
2 from index_stats;
Balancing
———-
9.49721286 <–인덱스의 밸런스 정도가 9.49%정도 깨짐
*인덱스의 재구성
-일반적으로 인덱스 밸런싱이 20%정도를 초과하면 성능이 저하될 수 있다고 본다
좋은 성능을 기대하기 위해서는 반드시 인덱스를 재구성해야 한다.
SQL> alter index i_big_emp_cp_empno rebuild nologging;
인덱스가 변경되었습니다.
SQL> analyze index i_big_emp_cp_empno validate structure;
인덱스가 분석되었습니다.
SQL> select (del_lf_rows_len/ lf_rows_len) * 100 as “Balancing”
2 from index_stats;
Balancing
———-
0
SQL>
불필요한 인덱스 제거
SQL> create index i_big_emp_cp_ename
2 on big_emp_cp(ename);
인덱스가 생성되었습니다.
SQL> alter index i_big_emp_cp_ename monitoring usage;
인덱스가 변경되었습니다.
SQL> select index_name,used from v$object_usage;
INDEX_NAME USE
—————————— —
I_BIG_EMP_CP_ENAME NO
SQL> alter index i_big_emp_cp_ename nomonitoring usage;
인덱스가 변경되었습니다.
SQL> drop index i_big_emp_cp_ename;
인덱스가 삭제되었습니다.
* v$object_usage 자료사전의 컬럼에 대한 설명
index_name : 인덱스명
table_name : 관련 테이블명
monitoring : 모니터링 기능 설정 여부(on/off)
used : 인덱스의 사용 여부
start_monitoring : 모니터링 설정 시작 시간
end_monitoring : 모니터링 해체 시간
SQL> desc v$object_usage;
이름 널? 유형
————————————————————————————-
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)
* primary key에 의해 만들어지는 인덱스는 테이블이 위치하는 테이블스페이스와 분리한다.
SQL> create table empcp
2 (empno number(3)
3 ,ename varchar2(10)
4 ,sal number
5 ,constraint empcp_empno_pk primary key(empno) using index tablespace indx
6 )tablespace users;
테이블이 생성되었습니다.
SQL> select index_name,index_type,tablespace_name
2 from user_indexes
3 where table_name = ‘EMPCP’;
INDEX_NAME INDEX_TYPE TABLESPACE_NAME
—————————— ————————— ——————————
EMPCP_EMPNO_PK NORMAL INDX
