8. INDEX(인덱스) ★★★

8장. 인덱스(INDEX)

 

1. 인덱스란 ? : 어떤 데이터의 위치 정보를 가진 주소록.

 - 데이터가 많이 있을 때 효과적이다.

 

2. 인덱스의 생성원리 (B-Tree인덱스 기준)

* 전체 테이블 스캔 (Table Full Scan) → ★정렬(Sort)★ → Block기록

 ① 인덱스를 생성하라고 명령을 실행하면 제일먼저 해당 테이블의 내용들을 전부 다 읽어서 메모리로 가져온다.

 ② 인덱스 만드는 동안 데이터가 변경되지 못하도록 조치를 한 뒤 PGA의 Sort area에서 정렬을 한다. 만약 PGA의 메모리가 부족하게 되면 Temporary tablespace를 사용해서 정렬한다. →이 때, 오라클에서 정렬을 최대한 줄여야 한다.

 ③ 정렬과정이 모두 끝난 데이터들은 인덱스를 저장하는 파일의 블록에 순서대로 기록을 한다.

 → 인덱스는 데이터가 정렬되어 들어간다.★

 

3. 인덱스 구조와 작동원리 (B-TREE 인덱스 기준)

* 데이터를 저장할 때 테이블에는 데이터가 들어오는 순서대로 입력이 되는데, 이때 정렬없이 그냥 순서대로 저장이 된다.(IOT는 제외)

* 그러나 인덱스는 컬럼의 개수도 적고, 데이터가 정렬되어 들어간다. (기본:오름차순)

key컬럼	인덱스를 생성하라고 지정한 칼럼값
ROWID컬럼	모든 데이터가 저장되어 있는 주소  → 원래 rowID는 딕셔너리에 저장되어 있는데, 많이 쓰는 주소만 인덱스에 저장해놓는다.

 

▶인덱스 작동원리

① 사용자가 A값을 조회하는 쿼리를 실행했을 때, 먼저 메모리의 데이터베이스 버퍼캐쉬에서 해당 데이터가 있는지를 먼저 살펴본다.

② 버퍼캐쉬에 원하는 데이터가 없을 경우, 하드디스크의 data file에 가서 해당 데이터를 database buffer cache로 가져온 후 사용자에게 보내야 한다. 이때 원하는 블록을 data file에서 buffer cache로 복사해 올 때 인덱스가 필요하다.

▷인덱스정보가 적힌 Index file을 찾는 순서(이어서) ③ 가장 위에 있는 Root block을 index file에서 buffer cache로 가져온 후, 사용자가 찾는 데이터(A)가 들어있는 블록에 대한 정보를 찾는다. ④ 그리고 두번째 줄에 있는 Branch block에서 봤을 때, index file에서 사용자가 찾는 데이터(A)가 들어있는 블록(7번)을 다시 buffer cache로 복사한 후, 데이터(A)가 들어있는 그 다음 블록 정보를 찾는다. ⑤ Leaf blook에서 사용자가 찾는 데이터(A)가 들어있는 블록(1번)을 buffer cache로 복사해서, 데이터 파일에서 사용자가 찾는 데이터(A)가 어떤 블록에 있는지(A-1 : datafile의 실제 블록정보)를 찾아낸다.

⑥ data file에 사용자가 찾는 데이터(A)가 들어있는 블록(1번)을 buffer cache로 복사해서 해당 데이터를 찾아 사용자에게 준다.

 

 

 

▶Single block I/O 와 Multi block I/O

Single block I/O	여러 건의 데이터를 조회할 경우에 인덱스를 사용할 경우에는 한번에 하나의 블록만 읽을 수 있다.  ex) 예를들어 사용자가 10건의 데이터를 입력하고 저장할 때 최악의 경우 이 10건의 데이터가 모두 다른블록에 저장될 수도 있는데, 이경우 인덱스를 사용해서 10건의 데이터를 읽으려면 디스크의 10개 블록을 읽어서 메모리로 가져와야 한다. 즉 1번에 1개의 블록만 읽어서 메모리로 가져옴.
Multi block I/O	한번에 여러 블록을 가져오는 것.   →인덱스를 쓰지 않고 디스크에서 블록을 메모리로 가져올 경우는 여러 블록을 한꺼번에 가져올 수 있다.

 

 

4. 인덱스의 종류

▷데이터를 처리하는 방법

- OLTP(OnLine Transaction Processing 실시간 트랜잭션 처리용)시스템 : 실시간으로 데이터가 입력되고 수정되는 환경 → OLTP 환경에서 주로 B-TREE 인덱스들이 많이 사용.  ex)회원테이블, 판매테이블

- OLAP(Online Analytical Processing 온라인 분석 처리용) 시스템 : 대량의 데이터를 한꺼번에 입력한 후 주로 분석이나 통계 정보 등을 출력할 때 사용하는 환경 → OLAP환경에서는 BITMAP인덱스가 많이 사용.

 

▷B-TREE인덱스 vs BITMAP인덱스

B-TREE 인덱스	주로 데이터의 값의 종류(카디널러티)가 많고, 중복되는 데이터가 적을 경우에 사용하는 인덱스이다.  → OLTP환경에서 주로 사용한다.
BITMAP 인덱스	데이터의 값의 종류(카디널러티)가 적고, 중복되는 데이터가 많을 경우에 사용하는 인덱스이다.  → OLAP환경(Data Warehouse) 에서 주로 사용한다.

 

 

1) B-TREE 인덱스 (많이씀)

* 인덱스는 탑 쌓는 것처럼 leaf → branch → root 순서로 생성이 되나, 데이터를 찾을 때에는 Root → Branch → Leaf 순서로 정보를 찾아 해당 데이터의 RowID를 찾은 후에 데이터가 들어있는 블록을 메모리로 복사해온다.

* B-Tree 에서 B란 Binary란 의미도 있고, Balance란 의미도 있다. → Root block을 기준으로 왼쪽과 오른쪽에 있는 데이터의 balance가 맞을 때 성능이 가장 좋다.

 

 

 (1) UNIQUE INDEX

 : 인덱스를 만드는 Key값에 중복되는 데이터가 없다는 뜻.

장점	인덱스중 성능이 제일 좋으므로, 어떤 컬럼에 unique index와 일반인덱스 두가지 모두 생성할 수 있다면 무조건 unique index를 생성한다. 왜냐하면 unique index는 무조건 하나밖에 없으므로, 한번만 읽으면 되지만, 일반인덱스는 최소 2개이상읽어서 같은게 있는지 확인해봐야 하기 때문이다.
단점	중복되는 데이터가 들어 올 수 없다.

* 주소, 핸드폰번호처럼 중복될 수 있을법한거로 unique index를 만들면 안된다.

* 생성 문법

CREATE UNIQUE INDEX 인덱스명  ON 테이블명(컬럼명1 ASC|DESC, 컬럼명2 ,...);    --ASC가 기본값(오름차순)

* 일반적인 인덱스명 : 인덱스를 뜻하는 접두어(IX, IDX 등)와 테이블명, 컬럼명을 섞어서 인덱스명을 정한다.

 

 (2) Non UNIQUE INDEX

 : 중복된 값이 들어가야만 하는 칼럼일 경우 생성하는 인덱스.

* 생성문법

CREATE INDEX 인덱스명  ON 테이블명(컬럼명1 ASC|DESC , 컬럼명2, ...);

 

 (3) Function Based INDEX (FBI-함수기반 인덱스)

* 인덱스는 대부분 where절에 오는 조건컬럼이나 조인컬럼 등에 만들어야 한다. (단 특별히 select에 있는 컬럼에 생성하기도 한다.)

* INDEX Suppressing Error : 인덱스는 잘 생성해놓고 SQL을 잘못 작성해서 인덱스를 사용할 수 없는 경우 → where절의 조건을 절대 다른 형태로 가공해서 사용하면 안된다.

- where ename like '%FORD%';  %는 모든것이라는 의미야. (인덱스는 많은 데이터에서 소수를 찾을 때 쓰는 것이므로) - where sal != 100 ; 이건 쓰면 안되지만, where sal>100 or sal<100 ;이건 쓸수 있음. - create index idx_emp_sal on emp(sal)    where sal+100=1000 ;    sal+100이라는 칼럼은 없으므로 에러.(where절 뒤에는 이꼴앞까지 칼럼으로 이해한다.)

:오라클이 인덱스를 만들 때 저 연산을 수행해서 인덱스를 만들어 준다. (임시적인해결책)

→단점 : 저렇게 FBI를 생성했는데 쿼리의 조건이 변경된다면 인덱스를 다시 만들어야 된다. 또한 기존 인덱스를 활용할 수 없다는 단점도 있다.

 

 (4) DESCENDING INDEX (내림차순 인덱스)

: 여러 인덱스를 생성할 때 큰 값이 먼저 오도록, 내림차순으로 인덱스를 생성하는 것. (큰 값을 많이 조회하는 SQL)

ex) 인터넷 뱅킹에서 계좌조회같이 최근날짜부터 먼저나오게 하는 경우나, 회사의 매출 테이블에서 매출상위 매장을 보는 메뉴 등

* 만약 한 메뉴에 오름차순과 내림차순을 한꺼번에 조회할 경우, 오라클에서는 인덱스를 위에서부터 읽게하거나 아래에서부터 읽게하는 힌트라는 방법을 제공한다. 힌트들을 이용해 튜닝에서는 정렬을 하지 않고 정렬한 효과를 내기도 하고, 최대값과 최소값 등을 구하기도 한다.

 

 (5) 결합 인덱스 (Composite INDEX) ★★ (아주 많이 사용)

 : 인덱스를 생성할 때 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것.(where절의 조건 컬럼 2개 이상이 and로 연결되어 함께 사용되는 경우에 주로 사용)

* 결합 인덱스에서 칼럼을 적는 순서에 따라 속도나 검사 횟수가 완전히 달라질 수 있다.

- 컬럼의 개수가 n개인 경우, 컬럼의 배치순서는 n!(n팩토리얼)가지의 경우가 있다.

* 두 개의 컬럼을 합쳐서 결합인덱스 키가 만들어져 있기 때문에, 해당 데이터를 찾을 때 두 개의 컬럼을 동시에 만족하는 블록을 검색한다.

(아래 그림은 편의상 두 컬럼을 차례대로 따로 읽는 것처럼 분리해서 설명하는 것이므로 오해x)

- 아래 그림처럼 첫 번째 조건에서 최대한 많은 데이터를 걸러내서 두번째 검사를 쉽게 만들어 줘야 한다.

 

2) BITMAP 인덱스

 : 데이터 값의 종류(cardinality)가 적고, 동일한 데이터가 많을 경우에 많이 사용하는 인덱스.

ex) 사원이 1천명이더라도 성별 컬럼의 카디널러티는 2가지 뿐이므로 bitmap인덱스가 적당하다.

* 데이터의 변경량이 적거나 없는 것이 좋다. 왜냐하면 데이터가 변경될 때 모든 맵을 다 수정해야 하고, 또 BITMAP인덱스는 블록 단위로 Lock을 설정하기 때문에 같은 블록에 들어있는 다른 데이터도 수정작업이 안되는 경우도 종종 생긴다.

* 중복되는 값이 많다는건 카디날라티가 적다는 것이다. 즉 중복되는 값이 많으면 bitmap 인덱스로 만들면 좋다.

* OLAP환경(Data warehouse)에서 주로 생성한다.

* 어떤데이터가 있다라는 지도정보(map)을 bit로 표시한다. 즉 어떤 데이터가 존재하면 참인 1로 표시하고, 데이터가 없는 곳은 거짓인 0으로 표시한다. 따라서 어떤 정보를 찾을 때 해당 맵에서 1인 값만 찾으면 된다.

* 생성문법

CREATE BITMAP INDEX 인덱스명  ON 테이블명(컬럼명);

 

* 사용예 : 성별컬럼에 BITMAP인덱스를 생성하면, 성별컬럼의 카디날러티가 남,여 2가지이므로, 성별 칼럼의 값의 종류대로 MAP이 2개 만들어 진다.

 

 

5. 인덱스의 주의사항

1) 인덱스를 생성한다고 무조건 SQL이 빨라지는 것은 아니다.

2) DML(insert,delete,update,merge)에 취약하다.

 ① insert시에 index split이 발생할 수 있다.★ (인덱스의 block들이 정렬된 상태로 삽입하기 위해 두개로 쪼개 나눠지는 현상)

index split 현상 ★

인덱스를 생성하면 정렬된 상태로 생성되어 있다. 그 상태에서 데이터가 추가로 입력이 되면 인덱스에도 반영이 되어야 하는데, 이 때 인덱스는 정렬되어야 하므로 중간자리에 끼어 들어가야 한다. 이 행이 들어갈 자리에 빈 자리가 없을 경우에는 오라클이 새로운 블록을 하나 가져와 빈자리가 없는 기존 블록에 있던 데이터들을 약 50% 정도 새로운 블록으로 옮긴다. 그 후에 그 빈자리에 새로 입력된 데이터를 넣는 것이다. 이렇게 한 블록에 저장되어 있던 데이터들이 빈자리가 없어서 2개의 블록으로 옮겨지게 되는 현상을 index split이라고 한다.  → n개의 컬럼에 인덱스가 있을 경우 index split도 n회 생기게 된다.  → index split현상은 오라클이 자동으로 진행해주지만 완료되기 전까지는 다음 데이터가 입력이 안되고 계속 진행중인 상태로 대기하고 있어야 하므로 데이터를 입력하거나 변경할 때 속도가 느려진다. (사용하지 않는 인덱스는 지워야 한다.★)

 ② index를 delete시에 index가 삭제되지 않고 데이터가 사용 안된다는 표시만 해둔 채 그대로 남아 있다. (truncate하면 다 지워짐.)

  (많은 양의 데이터가 update나 delete될 경우 인덱스를 정상적인 상태로 만들어 주기 위해, 수동으로 제거해주는 ReBuild작업을 해줘야한다.)

 ③ table에 update가 발생할 경우, index에서는 먼저 delete된 후, 다시insert된다. 그래서 index는 두 가지 작업들이 동시에 일어나므로 다른 DML문장보다 더 큰 부하를 준다. 즉, index에는 update라는 개념이 없다. (index의 최악의 적!!)

3) 다른SQL에 악영향을 줄 수 있다.

 : 잘 수행되고 있던 SQL문장이 새로 만든 인덱스 때문에 갑자기 아주 느려지는 경우가 종종 발생한다.

→ 이유 : 옵티마이져가 실행 계획을 세울 때, 기존에 없었던 인덱스가 갑자기 테입르에 생기면 최근에 만들어진 인덱스가 더 좋을 것이라고 생각해서, 잘 되고 있던 실행계획을 바꾸기 때문이다.

 

 

6. 인덱스 관리방법

1) 인덱스 조회하기.

 * 특정 사용자가 생성한 인덱스를 조회하려면 USER_INDEXES와 USER_IND_COLUMNS 딕셔너리를 조회하면 되고,

    데이터베이스 전체에 생성된 내역을 조회하려면 DBA_INDEXES와 DBA_IND_COLUMNS 를 조회하면 된다.

- user_ind_columns   :  column명까지 같이 나옴.

- user_indexes : 인덱스명만 나옴

 

 2) 사용여부 모니터링하기.

 ① 모니터링 시작하기

 SCOTT> ALTER INDEX 인덱스명 MONITORING USAGE;

 ② 모니터링 중단하기

 SCOTT> ALTER INDEX 인덱스명 NOMONITORING USAGE;

 ③ 사용유무 확인하기 : 자신이 만든 인덱스만 확인할 수 있다.

- 사용중이면 USED가 Yes로 바뀜.

 - 만약 SYS계정으로 모든 인덱스의 사용유무를 조회하고 싶으면 DBA가 별도의 뷰를 생성해 조회하면 된다.

  → 뷰 생성 후, SCOTT계정의 특정 인덱스를 조회해 모니터링을 시작한 후 뷰를 다시 조회하면 된다.

▷ 별도의 뷰 생성   SYS> CREATE OR REPLACE VIEW  V$ALL_INDEX_USAGE    2     ( INDEX_NAME,    3    TABLE_NAME,    4    OWNER_NAME,    5    MONITORING,    6    USED,    7    START_MONITORING,    8    END_MONITORING )    9    AS   10         SELECT a.name, b.name,e.name,   11              decode(bitAND(c.flags, 65536), 0, 'NO', 'YES'),   12              decode(bitAND(d.flags, 1), 0, 'NO', 'YES'),   13              d.start_monitoring,   14              d.end_monitoring   15         FROM     sys.obj$ a, sys.obj$ b, sys.ind$ c,sys.user$ e,   16                 sys.object_usage d   17        WHERE    c.obj# = d.obj#   18        AND       a.obj# = d.obj#   19        AND       b.obj# = c.bo#   20        AND       e.user# = a.owner# ;    ▷ 뷰 조회  SYS>SELECT * FROM v$all_index_usage;

 

 

3) INDEX Rebuild 하기

: 인덱스가 삭제되지 않은 상태로 오래 지속되면 성능이 떨어지므로, 대량의 DML작업을 수행한 후에는 일반적으로 인덱스의 밸런싱 상태를 조사해서 문제가 있을 경우 수정해 줘야 한다.

* Rebuild ONLINE 옵션 : REBUILD 작업 중에 DML을 사용하게 해줌. (전체적인 성능은 떨어진다.)

* 인덱스의 상태를 조회.

SCOTT>ANALYZE INDEX idx_itest_no VALIDATE STRUCTURE ;    → ANALYZE한 후 조회해야 반영이 됨. INDEX analyzed.   SCOTT>SELECT (del_lf_rows_len / lf_rows_len) * 100 BALANCE    2   FROM index_stats      3  WHERE name='IDX_ITEST_NO' ;       BALANCE ----------------           0                →   0 에 가까울수록 좋은 상태. (망가진퍼센트%)

 

* Rebuild 작업 

SCOTT>ALTER INDEX idx_itest_no REBUILD ;

 

 

7. Invisible Index (인비저블 인덱스)(11g)

 : 해당 인덱스를 삭제 하려고 할 때 사용하는지 사용하지 않는 것인지 정확하게 알아야 하므로, 실제 삭제하기 전에 "사용안함" 상태로 만들어서 테스트를 해 볼 수 있는 기능을 제공한다.

* 인덱스 사용유무 조회

SCOTT>SELECT table_name,index_name,visibility    2   FROM user_indexes    3  WHERE table_name =  'EMP' ; TABLE_NAME         INDEX_NAME              VISIBILIT -------------- ---------------------- -------------- EMP                   IDX_EMP_SAL               VISIBLE EMP                    PK_EMP                      VISIBLE

* 인덱스 사용안함 상태로 변경

ALTER INDEX idx_emp_ename INVISIBLE ;

 

* INVISIBLE상태로 변경했던 인덱스를 다시 사용하려고 할 때.(두가지 방법)

① ALTER INDEX idx_emp_ename VISIBLE ;

② SQL 힌트 구문에서 해당 인덱스를 수동으로 사용하게 지정.

SCOTT>SELECT /*+ index (emp idx_emp_sal) */ ename    2  FROM emp    3  WHERE ename >'0';

 

 

8. 인덱스 활용 예제

1) 인덱스를 활용하여 정렬한 효과를 내는 방법

- ORDER BY 구문을 사용하지 않고, 테이블에서 데이터를 가져올 때 인덱스를 활용해서 가져온다면 정렬이 된 상태로 출력이 된다.

* 인덱스를 사용하도록 SQL작성요령

SCOTT>SELECT 컬럼명 FROM 테이블명    2  WHERE name > '0' ;   → 인덱스를 사용하라는 의미

 

 

2) 인덱스를 활용하여 최소값(MIN) / 최대값(MAX)를 구하는 방법

* MAX/MIN 함수는 모든 데이터를 다 읽어서 기준 칼럼으로 정렬을 한 후 최대값, 최소값을 구하는 함수이므로 성능상 문제가 된다.

→ 인덱스를 활용하여 정렬을 하지 않고 최소값, 최대값을 구할 수 있습니다.

 

▷ (기존방법) 인덱스 활용해 최소값 구하기 (인덱스가 삭제되거나 인덱스 컬럼이 변경되면 잘못된 결과를 만든다.)

- "ROWNUM=1" → 최소값을 구하기 위해, 인덱스에서 가장 위에 있는 첫번째 데이터 1건만 가져오도록 함. (STOPKEY)

 

▷ (기존방법) 인덱스 활용해 최대값 구하기(인덱스가 삭제되거나 인덱스 컬럼이 변경되면 잘못된 결과를 만든다.)

→ 최대값을구하기 위해, 인덱스의 가장 아래쪽에 있는 값을 읽어야 하므로, 오라클 HINT기능을 이용해 옵티마이저에게 인덱스를 아래쪽부터 읽으라고 알려줘야 한다.

 

▷ (권장) FIRST_ROW(MAX/MIN)방법 (인덱스를 활용한 최대/최소값 구하기)

→ 인덱스가 삭제나 변경이 되더라도 max(name)으로 인해 정상적인 결과가 나온다.

 

9. ROWID에 대해

* 종류 : 제한적인 ROWID(7버전까지) / 확정된 ROWID (8버전부터 현재까지 사용)

* 확장된 ROWID는 총 10 bytes이다.

SCOTT>SELECT  ROWID, empno, ename    2  FROM    emp    3  WHERE    empno=7902 ;   ROWID                        EMPNO     ENAME --------------------------------- ---------- ------------- AAASHOAAEAAAACXAAM       7902      FORD