SQL정리 11장 Index

인덱스

데이터를 관리하기 위해서 사용. (부차적으로 원리상 속도가 빨라질수 있다.)

인덱스는 정렬되어 있다.

1. 정의 : data의 주소록(오라클의 주소 = row id)

2. 생성원리/작동원리 : full Scan(Lock 설정) → Sort(정렬)

생성원리 : 전부다 읽고 (읽을때 내용이 바뀌지 않게 Lock 건다.) 정렬한다.

작동원리 :

테이블
ROWID	이름	주소	연락처	급여
A1	홍길동	서울	1111	20
B3	나한얼	부산	1234	500
C2	강감찬	대구	5554	80
D1	전진	광주	5693	30

   

디스크에 저장된 위치
	1	2	3
a	홍길동
b			나한얼
c		강감찬
d	전진
e

idx_급여
Key	RID
20	A1
30	D1
80	C2
500	B3

   

idx_주소
Key	RID
광주	D1
대구	C2
부산	B3
서울	A1

   

idx_이름
Key	RID
강감찬	C2
나한얼	B3
전진	D1
홍길동	A1

위의 상황에서, 아래와 같은 SQL이 들어왔을 때,

select *

from member

where 이름 = ‘홍길동’

※ 해설 : idx_이름에서 ‘홍길동’을 찾아 'A1'에 있음을 알아내고, 바로 ‘A1’ row의 데이터를 출력한다.(= 조건이 이름일 때 ‘이름’ 인덱스를 참고하여 'A1' row임을 알아내 'A1' row의 내용 바로출력)

3. 장/단점 : 안쓰는 인덱스는 없앤다.

단점 :

1. DML에 취약

  ① insert : Index split (새로운 데이터가 들어오면, 기존 인덱스는 정렬되어야 하기 때문에 기존내용중 반을 지우고, 빈 공간 만들어지게 한후 빈공간에 새로운 데이터 내용의 인덱스를 입력한다. 인덱스가 많으면, 각각의 인덱스에 동일 과정을 반복실행하게 되어 오라클 느려짐)

  ② delete : 테이블의 데이터가 delete되더라도 index의 내용은 지워지지 않는다.

  ③ update : 인덱스에는 update가 없다. 인덱스에서는 delete후 insert된다.

2. 타 SQL에 악영향 : 새로운 index를 만들면, 기존의 잘 돌아가던 index 를 버리고 새 index로 덤빈다. 기존에 잘 돌던 index를 참조하던 SQL이, 새로운 index를 참조하게 되어 성능이 저하될수 있다.

※ 인덱스 생성 권장사항

insert / delete / update가 많이 안되는 테이블에 인덱스를 만들어라.

현실은 : 타협, select 가 중요한 테이블에 최소한의 인덱스를 만든다.

         인덱스를 1개를 만들어서 10개를 다 잡을수 있는 인덱스 최고

고유(유니크) 인덱스

인덱스 중 가장 성능 좋다.

조건을 잘 생각해서 만들자(중복값 있는 곳엔 유니크 인덱스로 만들면 안됨.)

유니크 인덱스는 한번만 읽으면 된다.

예제 : 부서 테이블에서 name 칼럼을 고유 인덱스로 생성하여라.

       단, 고유 인덱스이름은 idx_dept_name 로 정의한다.

SQL> CREATE UNIQUE INDEX idx_dept_name

     ON department (dname);

※ 위 테이블에서 만약 dname 칼럼의 값이 중복되는 경우가 있으면 Unique 인덱스는 생성되지 않는다.

비고유 인덱스

중복간 값을 가지는 칼럼에 대해 생성하는 인덱스

 

예제 : 학생 테이블의 birthdate 칼럼을 비고유 인덱스로 생성하여라.

       인덱스 이름은 idx_stud_birthdate 로 정의한다.

SQL> CREATE INDEX idx_stud_birthdate

     ON student(birthdate);

단일 인덱스

하나의 칼럼으로만 구성된 인덱스

결합 인덱스

두 개 이상의 칼럼을 결합하여 생성되는 인덱스. WHERE 절의 조건 비교에서

두 개 이상의 칼럼이 AND 로 연결되어 자주 사용되는 경우에 주로 생성함.

사례1) 아래의 두 조건이 들어 왔을 때,

where deptno=100

and sal>5000;

① 각각 검색 : 너무 많은 검색

② deptno 선검색 sal 후검색 : 100번부서중에 sal>5000인사람 검색

③ sal 선검색 deptno 후검색 : 전체부서들중에 sal>5000인 사람을 검색하고 그중 100번 부서를 검색(검색많음)

사례2) 한반에 30명중에 빨간펜을 들고있는 사람 검색

현재상황

30명중  남자 - 25명 - 빨간펜-1명

        여-5명

아래의 두 조건이 들어 왔을 때,

where 성별='남'

and   빨간펜='Y';

① 성별 선검색, 빨간펜 후검색 : 30번검색해서 25명 남자찾고, 25번 검색해서 빨간펜1명 찾음 = 총 30+25번 검색

② 빨간펜 선검색, 성별 후검색 : 30번 검색해서 1명 빨간펜 찾고, 1번 검색해서 남자인 것 찾음 = 총 30+1번 검색

※ 결론 : 결합인덱스 - 좋은 성능은 딱1개다. 찾아내라!!

3! 3x2x1=6

5! 5x4x3x2x1=120개의 경우의수

예제 : 학생 테이블의 deptno, grade 칼럼을 결합 인덱스로 생성하여라.

       결합 인덱스 이름은 idx_stud_dno_grade 로 정의한다.

SQL> CRAETE INDEX idx_stud_dno_grade

     ON student (deptno, grade);

DESCENDING INDEX

큰값을 먼저 조회하는 쿼리일때 만든다.

 사례) - 인터넷뱅킹(계좌조회, 최근날짜를 먼저 검색),

       - 회사매출테이블(매출이 많은 대리점부터),

       - 메일(최근메일부터 맨위에 보여줌) = 일반적으로 날짜컬럼에 만든다.

예제 : 학생 테이블에서 deptno와 name 칼럼으로 결합 인덱스를 생성하여라.

       deptno 칼럼은 내림차순으로, name 칼럼은 오름차순으로 생성하여라.

SQL> CREATE INDEX fidx_stud_no_name

     ON student (deptno DESC, name ASC);

FBI(함수기반 인덱스)

권장 : 쓰지말자

원래 없는 컬럼인데, 연산을 걸어 만들어줌 (ex. sal=100)

성능은 좋음

계산(함수)이 절대로 안바뀔때 FBI 만든다. 쿼리가 바뀌면 FBI못씀.

※ where sal+100>500;    조건일 때 →  where sal>400;    으로.

   sal*10>500;    조건일 때 →  sal>50    조건으로 쓰도록 한다.

※ on emp(sal+100)으로 인덱스 생성 할 수 있지만, 연산이 바뀌면 해당 인덱스는 못쓰게 됨.

예제 : 학생 테이블에서 학생들의 비만도 측정을 위해 표준 체중을 구하고자 한다.

       표준 체중에 대한 함수 기반 인덱스를 생성하여라.

       표준 체중을 구하는 공식은  '신장 -100 * 0.9' 이다

SQL> CREATE INDEX idx_standard_height

        ON student ((height-100)*0.9);

인덱스 정보 조회

인덱스에 대한 정보는 user_indexes , user_ind_columns 로 조회 가능함

예제 : 학생 테이블에 생성된 인덱스를 조회하여라.

SQL> SELECT index_name, uniqueness

     FROM user_indexes

     WHERE table_name= 'STUDENT'

     

SQL> SELECT index_name, column_name

     FROM user_ind_columns

     WHERE table_name= 'STUDENT'

인덱스 삭제

인덱스의 사용 여부 신중히 검색 후 사용 (사용여부 확인법은 후에 배움)

 

예제 : 학생 테이블에 생성한 fidx_stud_no_name 인덱스를 삭제하여라.

SQL> DROP INDEX fidx_stud_no_name

인덱스 재구성(Rebuild)

테이블에 정의된 칼럼 값에 대해 변경 사항이 자주 발생할 경우 인덱스 키의 정렬 순서를 유지하기 위하여 노드값을 조정해 주는 작업을 의미.

적용 기준은 애매함 (경험에 의해 결정함)

많이 망가지면 지우고 새로 만들기, 적게 망가져 있으면 리빌드

오라클 공식 : 리빌드 권장

예제 : 학생 테이블에 생성된 stud_no_pk 인덱스를 재구성하세요.

SQL> ALTER INDEX stud_no_pk REBUILD;