INDEX - 인덱스

INDEX

1. 정의

어떤 데이터가 어디에 들어있다, 주소록 같은 개념

주소를 ROWID 라고 함

인덱스는 칼럼이 두개밖에 없음 하나는 KEY | ROWID

KEY --> 인덱스를 생성하라고 지정한 칼럼값

데이터들의 ROWID 정보를 별도의 세그먼트에 넣어서 저장하고 관리하는데 이 세그먼트가 바로 인덱스라는것!

2. 생성원리 + 작동원리

생성원리

인덱스는 WHERE절에 오는 조건 칼럼이나 JOIN절에 오는 칼럼에 만들어짐

LOCK --> TABLE FULL SCAN스캔 --> SORT정렬 --> 기록

인덱스 만드는 동안 데이터가 변경되면 문제가 되므로 데이터들이 변경되지 못하게 조치를 취함 이게 LOCK

해당 데이터가 어떤 블록에 들어있는지 알기 위해서 모든 블록을 읽는것이 TABLE FULL SCAN

시간이 가장 많이 걸리는 단계가 SORT 정렬단계

작동원리

1. 조건에 맞는 검색을 요청

2. 딕셔너리 dictionary에서 조건에 맞는 칼럼의 인덱스가 있는지 없는지 확인

3. 인덱스가 존재 할 경우 인덱스에서 조건에 맞는 데이터가 들어있는 ROWID를 검색해 그 블록만 가져와서 작업

4. 인덱스가 존재하지 않을 경우 하드디스크 파일에서 조건에 맞는 정보를 가진 블록 전체를 복사해 메모리로 갖고 와서 작업

--> 인덱스가 유무에 따라 처리 속도가 다르다는 것을 알 수있다.

3. 종류

1. B-TREE INDEX

그림

여기서 B는 binary, balance

주로 balance tree 라고 함

실제 테이블에 데이터들의 주소는 Leaf Block들에 전부 들어있다.

Root Block --> Branch Block --> Leaf Block

이 순서대로 정보를 찾아가서 해당 데이터의 ROWID를 찾은 후 데이터가 들어있는 블록을 메모리로 복사해옴

데이터를 처리 하는 방법은 OLPT(Online Transaction Processing) 

대부분이 실시간으로 데이터가 입력되고 수정되는 환경임 (eg. 회원테이블, 팬매테이블 등 식시간으로 데이터가 입력되고 변경되는 것들)

특징: Root Block을 기준으로 했을 때 양쪽에 들어있는 데이터의 밸런스가 맞을때 성능이 가장 좋다.

종류

1) UNIQUE INDEX

인덱스안의 KEY 값에 중복 데이터가 없다.

= 해당 테이블의 칼럼에 중복된 값이 없다. + 아페로도 계속

향후에 중복된 값이 입력될 가능성이 조금이라도 있는 칼럼이면 절대로 이 인덱스를 써선 안됨.

문법: CREATE UNIQUE INDEX 인덱스이름 ON 테이블명 (칼럼명1 ASC/DESC, 칼럼명2 ---) ;

역시 인덱스이름도 제약조건이름이랑 같은거임

보통은 IDX_테이블명_칼럼명 이렇게 씀

인덱스 정렬방식을 오름차순/내림차순으로 정할 수 있음 기본은 당근 ASC

UNIQUE INDEX가 설정된 칼럼에 데이터가 중복으로 들어갈 수 없음!

2) NON UNIQUE INDEX

UNIQUE INDEX와 반대로, 중복되는 데이터가 들어가야하는 칼럼일 경우에 생성

문법: CREATE INDEX 인덱스이름 ON 테이블명 (칼럼명1 ASC/DESC, 칼럼명2 ---) ;

3) FUNCTION BASED INDEX (FBI, 함수기반 인덱스)

인덱스는 WHERE절에 오는 조건 칼럼이나 조인칼럼에 만들어짐

WHERE SAL=100

이라고 할때 인덱스는 SAL 칼럼에 만들어져야 한다는 말씀

근데 정작 WHERE SAL+100=200 이라는 조건을 줬다고 하면

SAL 칼럼의 인덱스는 생성해놓고도 쓰지 못하게 됨 

왜냐면 SAL+100을 칼럼으로 인식하기 때문에.

이 에러를 INDEX SUPPRESSING ERROR 라고 함

인덱스는 잘 생성해놓고 SQL을 잘못 작성해 인덱스를 사용할 수 없는 경우

즉, WHERE 절의 조건을 절대로 다른 형태로 가공해서 사용하면 안됨!!

그럼 방법이 없나요? ㅜㅜ

방법은 있음 SAL+100 의 형태로 인덱스를 생성하면 됨

이게 바로 FBI, 함수기반 인덱스라고 하는거임

문법: CREATE INDEX 인덱스이름 ON 테이블명 (SAL+100) ;

WHERE 조건절에 써놓은 것 자체를 그냥 칼럼으로 생각해서 쓰면 됨

그럼 오라클이 알아서 인덱스를 만들때 저 연산을 수행해서 인덱스를 만들어.

저렇게 함수기반 인덱스를 생성했는데 조건이 변경된다면.. (+100이 아니라 +50으로 바꾼다던지..) 인덱스 다시 만들어야함

함수기반 인덱스는 기존 인덱스를 활용할 수 없는 단점도 있음

그래서 좀 비추.. 

4) DESCENDING INDEX = DESC INDEX

DESC INDEX는 인덱스를 생성할 때 큰 값이 먼저 오도록=내림차순으로 인덱스를 형성

예를들면, 계좌조회에서 최근 날짜부터 먼저 나오게 하는 것

문법: CREATE INDEX 인덱스이름 ON 테이블명 (칼럼 DESC) ;

5) 결합 인덱스 - Compisite Index

두 개 이상의 칼럼을 합쳐서 인덱스를 만드는 것

WHERE절에서 조건 칼럼이 2개 이상일때 (WHERE 조건1 AND 조건2) 사용이 됨

문법: CREATE INDEX 인덱스이름 ON 테이블명 (칼럼1, 칼럼2) ;

어느 컬럼을 앞에 두든 일량에는 차이가 없다.

2. BITMAP INDEX

데이터 처리방법: OLAP (Online Analytical Processing) 

대량의 데이터를 한꺼번에 입력한 후 주로 분석이나 통계 정보등을 출력할 때 사용하는 환경

3. 장단점

단점! 은 DML에 취약하다는 점 DML=INSERT / DELETE / UPDATE

어떻게 취약하냐?

1) INSERT

INDEX SPLIT이 발생함 

새 데이터가 들어오면 테이블에도 추가되야하고, 인덱스에도 추가되어야 한다.

테이블 같은 경우는 데이터가 입력될 때 그냥 입력된 순서대로 정렬없이 저장이 된다.

하지만 인덱스는 데이터가 순서대로 정렬 되어야 하는데 새 데이터가 들어오게 되면 자기 순서에 맞는 곳에 끼어들어가야된다.

그래서 하나의 블럭으로 되어있던 인덱스들이 새 데이터를 입력시키기 위해 들어갈 위치에 맞춰 두개로 나눠지는걸 INDEX SPLIT 이라고 함

새 데이터가 들어가는 칼럼들 모두에 Split 현상이 생기게 되면서 성능 저하를 야기 시킴

2) DELETE

인덱스는 데이터가 지워지지 않음

기존 데이터를 DELETE 할때 테이블에서는 데이터가 지워지지만 인덱스에서는 안지워진다!

지워지지 않고 그냥 사용 안된다는 표시만 해둔다. 다 남아있음.

나중에 조회해봤을 때 테이블에는 데이터가 100건인데 인덱스는 무슨 300건이나 있을 수 있다.

지워지지 않는 것들이 쌓이고 쌓여서 결국엔 쿼리 수행속도가 느려지게 됨.

그래서! 관리자가 수동으로 정리작업을 해줘야한다 --> 이게 바로 INDEX REBUILD

* TRUNCATE나 DROP을 쓸 때는 테이블+인덱스 다 지워짐

3) UPDATE

인덱스는 UPDATE라는 개념이 없음

UPDATE를 날렸을 때

테이블에서는 데이터 내용이 변경 됨

인덱스 에서는 DELETE --> INSERT 이렇게 두 작업이 동시에 일어남 --> 결과는 부하 걸림 ㅋㅋ

완전 인덱스에 나쁜 영향을 주는것이 바로 UPDATE

4. 활용법

1) INDEX 조회하기

생성된 인덱스 조회는 

USER_INDEXES / USER_IND_COLUMNS

이 두개의 딕셔너리를 조회하면 된다

데이터베이스 전체에 생성된 내역을 조회하려면

DBA_INDEXES / DBA_IND_COLUMNS 조회

문법: SELECT table_name, index_name FROM user_indexes WHERE TABLE_NAME='대문자테이블명' ;

2) INDEX 사용여부 모니터링

사용 안하는 인덱스는 삭제해야지 성능 향상에 도움이 됨

사용 여부를 확인 하기 위해서는

모니터링 시작: ALTER INDEX 인덱스이름 MONITORING USAGE ;

모니터링 중단: ALTER INDEX 인덱스이름 NOMONITORING USAGE ;

사용유무 확인: SELECT index_name, used FROM  v$object_usage WHERE INDEX_NAME='대문자인덱스이름' ;

3) INDEX REBUILD

테이블 데이터는 지워지는데 인덱스는 안지워진다고 했음

그래서 꾸준히 인덱스 관리를 해줘야지 성능 저하가 안됨! 이게 바로 INDEX REBUILD

**********************야 이거 실습 안했어 P321**********************

4) INVISIBLE INDEX - 11g 추가 기능

인덱스를 실제로 삭제하기 전에 "사용안함" 상태로 만들어서 테스트 해볼 수 있음

문법: 

조회방법 SELECT table_name, index_name, VISIBILITY FROM user_indexes WHERE TABLE_NAME='대문자테이블명' ;

인비저블 실행 ALTER INDEX 인덱스이름 INVISIBLE ;

다시 조회했을때 사용안함 상태로 만든 것 INVISIBLE 상태로 만든 칼럼은 VISIVILITY에서 INVISIBLE로 떠야한다.

이렇게 인비저블로 설정한 후 점검해서 다른 SQL 문장에 영향을 주지 않는다면 그 인덱스는 지워도 된다는 것

인덱스 지우면 안되는거 같아서 다시 사용해야 한다면

문법: ALTER INDEX 인덱스이름 VISIBLE ;

바꿔주고 다시 조회했을 때 INVISIBLE 이었던 인덱스가 VISIBLE이라고 뜨면 변경된 것임

다른 방법은 SQL 힌트 구문에서 해당 인덱스를 사용하게 하면 된다.

select 절에서 select 바로 뒤에 /*+ INDEX (테이블명 인덱스이름) */ 를 추가해주면 됨. 

면접에서 많이 물어봄 인덱스 아는대로 말해보라우 이런식

오라클 할때 가장 중요한 것들이 OPTIMISER? 랑 INDEX랑 JOIN