인덱스

논리적 인덱스의 종류

• BITMAP INDEX
• B-TREE INDEX

B-TREE INDEX

• 우리가 흔히 아는 -TREE 구조로 이루어진 인덱스
• ROOT, BRANCH, LEAF 구조로 이루어져 있음

BITMAP INDEX

• 생성부분에서 B-TREE와 비슷하지만, Bitmap이라는 형식으로 압축저장하기때문에 중복값이 많을경우에 효과적으로 저장 가능
• Bitmap인덱스는 Online Rebuild작업을 지원하지 않음

---

Root 블록

• 가장 상위이자 가장먼저 액세스 하는 블록
• ROOT 블록을 액세

Branch 블록

• Root와 Leaf 사이에 위치

Leaf 블록

• 인덱스 key 컬럼 값과 해당 테이블의 로우 위치(rowid)가 저장되어 있음

---

인덱스 엔트리 구조

• 인덱스 엔트리 헤더 : 컬럼번호와 lock 정보 포함
• 인덱스 키 컬럼 길이
• 인덱스 키 컬럼 값
• ROWID : 테이블에서의 row 위치 정보

ROWID 제공 정보

• 오브젝트 번호
• 데이터파일 번호
• 블록번호
• ROW 번호

---

INSERT 실행시

• 만약 값이 블록 마지막에 입력되고, PCTFREE값이 채워지지 않으면 그대로 입력되지만 아니라면 새로운 블록 사용 시작
• 만약 중간에 값이 채워지게 된다면 분할 시작 -> 이때 분할을 적절히 함으로써 블록들의 공간을 남기게 되는데, 남기는 이유는 값이 더 채워질수도 있기 때문이다.

DELETE UPDATE 실행시

• 원본테이블에서 값이 삭제되어도 인덱스에서는 삭제작업을 하지않고 삭제되었다는 표시만 해준다
• UPDATE는 인덱스에서는 사용하지 않는 개념이다. -> 삭제하고 INSERT 하는 방식으로 진행

---

primary key 생성 후 unique key 자동 생성

SQL> ALTER TABLE DAMIN.STUDENT ADD CONSTRAINT IDX_STUDNO PRIMARY KEY(STUDNO);

자동으로 인덱스 타는거 확인 가능

SQL> set autotrace on
SQL> select * from damin.student where studno=9715;

    STUDNO NAME       ID                        GRADE JUMIN         BIRTHDAY           TEL                 HEIGHT     WEIGHT    DEPTNO1    DEPTNO2     PROFNO
---------- ---------- -------------------- ---------- ------------- ------------------ --------------- ---------- ---------- ---------- ---------- ----------
      9715 허우       wooya2702                     1 7802232116784 23-FEB-78          02)6122-2345           163         51        103


Execution Plan
----------------------------------------------------------
Plan hash value: 3262245179

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    76 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| STUDENT     |     1 |    76 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007658 |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

일반적인 인덱스를 생성하면 index range scan을 하는데 unique인덱스일 경우 훨씬 효과적임

SQL> CREATE INDEX IDX_GRADE ON DAMIN.STUDENT(GRADE);

SQL> SET AUTOTRACE ON
SQL> SELECT * FROM DAMIN.STUDENT WHERE GRADE='4';

    STUDNO NAME       ID                        GRADE JUMIN         BIRTHDAY           TEL                 HEIGHT     WEIGHT    DEPTNO1    DEPTNO2     PROFNO
---------- ---------- -------------------- ---------- ------------- ------------------ --------------- ---------- ---------- ---------- ---------- ----------
      9411 서진수     75true                        4 7510231901813 23-OCT-75          055)381-2158           180         72        101        201       1001
      9412 서재수     pooh94                        4 7502241128467 24-FEB-75          051)426-1700           172         64        102                  2001
      9413 이미경     angel000                      4 7506152123648 15-JUN-75          053)266-8947           168         52        103        203       3002
      9414 김재수     gunmandu                      4 7512251063421 25-DEC-75          02)6255-9875           177         83        201                  4001
      9415 박동호     pincle1                       4 7503031639826 03-MAR-75          031)740-6388           182         70        202                  4003


Execution Plan
----------------------------------------------------------
Plan hash value: 2474209842

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |     5 |   380 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| STUDENT   |     5 |   380 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_GRADE |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("GRADE"=4)

DROP 시에는 스키마명을 붙이지 않음

DROP INDEX IDX_GRADE;

---

인덱스 REBUILD

• 인덱스 REBUILD 시에는 정렬작업이 발생하지 않음 > 속도가 빠르다
• 그 이전에 존재하던 인덱스는 제거 > 먼저 재생성되고 제거되기때문에 테이블스페이스에는 인덱스 두개의 용량만큼의 공간이 있어야 함
• 인덱스가 재생성되는 동안 기존에 존재하는 인덱스를 사용

인덱스를 재생성하는 이유

• 기존에 DELETE나 UPDATE 문이 많이 사용됐을경우 낭비되는 공간이 많아져서 해주어야 함
• 인덱스를 다른 테이블스페이스로 옮겨주는 경우

SQL> SELECT * FROM DBA_TABLESPACES;

SQL> SELECT  * FROM DBA_INDEXES
WHERE OWNER='DAMIN'
AND TABLE_NAME='STUDENT';

SQL> ALTER INDEX DAMIN.SYS_C007659 REBUILD tablespace DAMIN_IDX_TS;
SQL> ALTER INDEX DAMIN.SYS_C007658 REBUILD tablespace DAMIN_IDX_TS;

SQL> SELECT  INDEX_NAME, TABLESPACE_NAME, STATUS from dba_indexes where owner='DAMIN' AND TABLE_NAME='STUDENT';

INDEX_NAME                                                                                                                       TABLESPACE_NAME                STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ --------
SYS_C007658                                                                                                                      DAMIN_IDX_TS                   VALID
SYS_C007659                                                                                                                      DAMIN_IDX_TS                   VALID

SQL>

ALTER INDEX DAMIN.SYS_C007658 ONLINE;

• 온라인 재생성의 경우 공간을 제거할 수 없음
• 또, 테이블 LOCK이 걸리므로 DML이나 DDL이 많은 경우 조심해야 함

따라서 블록들을 모아줄 경우에는 Coalescing 사용

ALTER INDEX DAMIN.SYS_C007658 COALESCE;

---

인덱스 공간 활용 확인