디비는 SQL문의 응답을 빠른 시간안에 내야하죠...
대부분의 SQL문은 select입니다.
검색이 가장 빨라야 하는 거죠.
인덱스라고 하는 것은 검색시의 빠른 응답을 위해서 마련해 놓은 자료구조입니다.
정확히는 B+트리라는 자료구조를 이용하는데요.
테이블의 데이터들은 데이터파일내에 위치하게 되고, 이를 검색하기 위해서는 많은 수의 레코드들을 비교해야 합니다.
그래서 특정 필드들을 이용해서 B+트리를 구성해 놓는 것을 인덱스하고 보시면 되겠습니다.
자료구조에서 트리의 경우는 검색이 빠르다는 장점을 가지고 있죠.
결국 테이블의 데이터를 가지고 인덱스를 만들어서 검색속도를 향상시키는 것입니다.
대부분의 DB에서 기본적으로 primary key는 인덱스를 자동으로 생성하는 경우가 많습니다.
물론 이 인덱스의 종류는 다양합니다. 데이터의 종류나 성향에 따라서 검색속도를 향상시키는 몇가지의 방법이 존재하는 것이죠.
이 인덱스는 검색 쿼리문의 조건절에 명시된 조건절을 따르게 됩니다.
조건이 많은 경우는 디비내의 옵티마이져가 선택한 인덱스를 이용해서 검색을 하게 됩니다.(이는 디비마다의 알고리즘이 다릅니다.)
그래서 오라클의 경우는 옵티마이져가 정한 인덱스가 아닌, 개발자/DBA가 정한 인덱스를 타도록 지정하는 힌트라는 옵션도 있습니다.
결론적으로 인덱스는
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, 지정하지 않아도 되거나 생략시 기본 설정값으로 대체됨.
[출처] [오라클]인덱스 생성 [CREATE INDEX]|작성자 이지
----------------------------------------------------------------------------------------------------------------------
-----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
