[Oracle] Index 인덱스의 이해 1

 

Index

 

An index is a database structure that provides quick lookup of data in a column or columns of a table.

 

 

어떤 데이터가 어디에 있는지 그 주소를 가지고 있는

주소록과 같은 의미의 데이터베이스 객체 

 

---

 

[ 데이터베이스 시스템에서 데이터를 찾는 과정 ]

 

유저 A가 데이터베이스에서 '홍길동' 이라는 정보를 찾으려고 쿼리를 DB에 보낼 때,

데이터베이스 시스템에서는 자주 사용하는 정보를 캐시 메모리에 저장해두고 사용

'홍길동'이라는 정보가 캐시 메모리에 없을 때, HDD에서 정보를 가져와서 캐시에 저장 후 유저에게 제공

 

HDD에 정보가 너무 많을 때는

블록 형태로 저장 된 데이터를 전체 스캔하면 하나의 데이터를 찾는데 너무 많은 시간이 소요 됨

=> Table Full Scan으로 DBMS 성능 저하

 

'홍길동'이라는 데이터의 HDD 상의 주소값(Rowid)을 이용하면 빠르게 접근 가능 !

데이터 + 데이터 주소 값(rowid) => 인덱스

 

 

 

[ 인덱스 생성 원리 ]

 

1. Table Full Scan

 

2. Sort ( 테이블 내용을 다른 메모리에 가져와 정렬 ) 

pga 공간에서 데이터 정렬 -> pga 모자르면 temporary tablespace 

 

3. Block에 기록

 

-- Unique Index
create unique index [ Index Name ]
on [ Table Name ] ( Column );

-- Non Unique Index
create index [ Index Name ]
on [ Table Name ] ( Column );

 

SELECT rowid, empno, ename FROM emp
WHERE empno = 7788;

 

[ ROWID 구성 ]

Data Object Number (6자리) + File Number (3자리) + File Block Number (6자리) + 테이블 Row 위치 Number (3자리)

 

 

 

• 예제 1 - Unique Index 생성

-- 테스트용 테이블 복사
CREATE TABLE DEPT2 AS SELECT * FROM DEPT d;

-- 유니크 인덱스 생성 
CREATE UNIQUE INDEX idx_dname
ON dept2(dname);

-- 데이터 Ins
INSERT INTO dept2
VALUES (90, '영업부', '서울지점');

-- dname 중복 데이터 Ins
INSERT INTO dept2
VALUES (90, '영업부', '서울지점');

 

• Unique 조건 오류 메세지

 

 

• 예제 2 - Non Unique Index 생성

 

-- Non Unique Index 생성
CREATE INDEX idx_emp2_ename
ON emp2(ename);

-- 중복 ename Insert
INSERT INTO emp2(empno, ename)
values(8000, '홍길동');

INSERT INTO emp2(empno, ename)
values(8000, '홍길동');

 

• Output