[9장] INDEX(인덱스)

 

1. 인덱스란 무엇인가?

 

 : '어떤 데이터가 어디에 있다'라는 위치 정보를 가진 주소록 같은 개념

 

 

 

 

 

 

(사용자가 홍길동 정보를 조회하기 위해 SELECT 구문을 수행했다고 가정)

 - 오라클 서버 프로세스가 해당 쿼리를 수행해서 사용자가 원하는 데이터를 출력하는데 서버 프로세스는

    가장 먼저 메모리의 데이터베이스 버퍼 캐시를 살펴봄

 - 데이터베이스 버퍼캐시에는 많이 사용되는 테이블들이 캐싱되어 저장되어 있는데 여기에 데이터가 있을 경우

    빨리 결과를 볼 수 있음

 - 만약 데이터베이스 버퍼캐시에 조회하는 데이터가 없을 경우 서버 프로세스는 3번 과정으로

    하드 디스크에 있는 데이터 파일에서 홍길동 정보가 들어 있는 블록을 찾아서 데이터베이스 버퍼 캐시로

    복사해 온 후 사용자에게 값을 돌려줌(4번 과정)

 

- 메모리(데이터베이스 버퍼 캐시)에서 원하는 데이터를 찾을 경우 아주 빠르게 결과를 조회 할 수 있지만

   메모리의 용량이 한계가 있기에 모든 데이터를 데이터베이스 버퍼캐시에 보관 할 수 없음

- 그래서 많이 사용하는 데이터는 메모리의 데이터베이스 버퍼캐시에 캐싱 해 두고 많이 사용되지 않는 데이터는

   디스크에 저장 했다가 필요할 때 메모리로 복사해 와서 조회나 변경작업 등을 하게 됨.

 

 

 - 하드디스크에 데이터가 너무 많아 사용중인 블록이 너무 많을 경우 해당 데이터가 어떤 블록에 들어 있는지

    모른다면 모든 블록을 다 읽어 봐야 한다(=Full Table Scan)  ⇒ 시간이 아주 많이 걸림

 -  그래서 '어떤 데이터의 주소는 어디다' 라는 식으로 주소록을 만들어서 데이터를 관리하는 기법이 등장

     이렇게 데이터의 주소값을 가지고 있는 것 : 인덱스

 

 - 인덱스가 있으면 모든 블록을 다 읽지 않고 원하는 데이터가 있는 블록 주소를 찾아내서 그 블록만 메모리로

   복사해오면 빠른 작업 가능

 

 - 오라클에서는 데이터의 주소를 주소라고 표현하지 않고 ROWID라 부름

 - ROWID는 두 가지가 있는데 7버전까지 사용하던 제한적인 ROWID 가 있고

                                          8버전부터 현재까지 사용중인 확장된 ROWID가 있음

 - 확장된 ROWID는 총 10 bytes 로 그 구조는 아래와 같습니다.

 

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

  

 

 

 

< ROWID 구조 >

 

 - 어떤 데이터의 ROWID를 알고 있다는 것은 해당 데이터의 저장된 위치를 정확하게 알고 있다는 의미

 

 - 데이터들의 ROWID 정보를 별도의 세그먼트(대표적 세그먼트가 테이블입니다)에 넣어 저장/관리하는데

   이 세그먼트를 인덱스라고 함

 - 인덱스가 있으면 어떤 데이터가 어디에 저장되어 있다는 것을 다 알기 때문에 데이터를 조회나 변경을 위해

   호출 해야 할 때 아주 빠르게 작업이 가능

2. 인덱스의 생성 원리 

 - 인덱스의 종류가 여러 가지로 많이 있지만 생성되는 원리는 거의 동일

 

 - 인덱스를 생성하라고 쿼리를 수행하면

    ① 제일 먼저 해당 테이블을 전부 다 읽음(내용 파악)

    ② 인덱스 만드는 동안 데이터가 변경되면 문제가 되므로 해당 데이터들이 변경되지 못하도록 조치

    ③ 메모리(PGA 의 Sort Area 라는 곳입니다)에서 정렬

    ④ 만약 메모리가 부족하게 되면 임시 테이블스페이스(Temporary tablespace)를 사용해서 정렬(시간이 많이 걸림)

    ⑤ 메모리에서 정렬과정이 모두 끝난 데이터들은 파일의 블록에 순서대로 기록 함

 

 - 전체 테이블 스캔(Table Full Scan) → 정렬 (Sort) → Block 기록 이라는 과정을 거침

 - 데이터가 정렬이 되어서 들어간다

 

 

 

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

 

 - 테이블은 여러 칼럼이 있고, 데이터도 기본적인 방법으로는 들어오는 순서대로 입력됨

 - 즉, 어떤 기준으로 정렬되는 것이 아니라 입력되는 순서대로 그냥 들어가서 정렬없이 저장된다(IOT 제외)

 - 인덱스는 컬럼이 두개뿐!!

 

 

 

 

 

 - 테이블은 컬럼이 여러개이고 데이터도 정렬없이 입력된 순서대로 들어가 있지만,

    인덱스는 컬럼이 Key 컬럼과 ROWID 컬럼 두개로 이루어져 있음

 

 - Key 컬럼이란 인덱스를 생성하라고 지정한 컬럼 값이 됨

 

 

 

 

 

 - 이름 컬럼과 주소 컬럼에 인덱스를 생성한 것

 

 

 

 EX) 사원 테이블에 사원이 100만 명 있고, 하드 디스크에 사원테이블이 지정한 파일의 블록이 10만개가 된다고 가정

 

SQL> SELECT *     2    FROM 사원     3    WHERE 이름=’홍길동’ ;

 

 - 사용자가 홍길동 사원의 정보를 가져오는 SQL을 작성했다면,

 

  ① SQL을 수행하면 서버 프로세스가 파싱이란 과정을 마친 후

      메모리에(데이터베이스 버퍼캐시) 이름이 홍길동인 사람의 정보가 있는지 살펴봄

  ② 만약 메모리에 홍길동 정보가 없으면 하드 디스크의 파일에서 홍길동 정보를 가진 블록을 복사해서 메모리

     (데이터베이스 버퍼캐시)로 가져온 후 홍길동 정보만 골라내서 사용자에게 보여줌

     (이 때 하드 디스크의 데이터 파일의 블록이 아까 10만개라고 했으므로 서버 프로세스는 홍길동 정보가

      어느 블록에 들어 있는지 모를 경우 10만개 블록 전부다 메모리로 복사를 해야 함 ㅜ.ㅜ)

  ③ 그래서 이렇게 하기 전에 WHERE 절에 있는 이름 컬럼에 인덱스가 만들어져 있는지 확인

      (우리 예 에서는 인덱스가 만들어져 있으므로 IDX_사원_이름 인덱스에 먼저 가서 홍길동 정보가 어떤 ROWID를

       가지고 있는지 확인 한 후 해당 ROWID 에 해당되는 블록만 찾아가서 복사를 해 옴)

 

 

 

4. 인덱스의 종류

 

 - 일반적으로 많이 사용되는 인덱스는 크게 B-TREE 인덱스와 BITMAP 인덱스로 나뉨

 - B-TREE 인덱스 안에 세부적으로 여러 가지 인덱스로 또 나뉘게 됩니다.

 - 인덱스(주소록)가 많다는 뜻은 그 용도가 다 달라서 정확한 용도에 맞게 사용하고 관리해야 한다는 뜻

 - 데이터를 처리하는 방법 중에

   OLTP(OnLine Transaction Processing – 실시간 트랜잭션 처리용) 시스템인 경우가 있고

   OLAP (OnLine Analytical Processing – 온라인 분석 처리용) 시스템일 경우가 있음

 

 - OLTP 환경에서는 주로 B-TREE 인덱스들이 많이 사용되며

    OLAP 환경에서는 BITMAP 인덱스가 많이 사용된다.

 

 

(1) B-TREE 인덱스

 

 

 

 - 실제 테이블에 데이터들의 주소는 Leaf Block들에 전부 들어있으며

    해당 데이터들에 대한 데이터들이 Branch Block과 Root Block에 들어있음

 

 - 특정 데이터를 찾아야 할 경우 Root Block에서 Branch Block 정보를 찾고 Branch Block에서 Leaf Block 정보를

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

 

 - B-TREE에서 B란 Binary/Balance란 의미

 - Root Block을 기준으로 왼쪽과 오른쪽에 들어있는 데이터의 Balance가 맞을 때 성능이 가장 좋다는 것이

   이 인덱스의 가장 큰 특징

 

 

 ① UNIQUE INDEX

   : 인덱스 안에 있는 Key 값에 중복되는 데이터가 없다는  뜻

  - 해당 테이블의 컬럼에 중복된 값이 없다는 뜻, 앞으로도 중복된 값이 들어 올 수 없다.

 

생성 문법 ​SQL> CREATE UNIQUE INDEX 인덱스명    2    ON 테이블이름(컬럼명1 ASC | DESC , 컬럼명,……..); ​ 생성 예제​ SCOTT> CREATE UNIQUE INDEX idx_dept2_dname       2      ON dept2(dname) ;

 

 

  - 인덱스 생성시에 설정하는 인덱스 이름은 회사마다 규칙이 있으니 그 규칙에 맞게 임의로 설정하면 되고

     일반적으로 인덱스를 뜻하는 접두어를 사용하고 테이블 이름과 컬럼 이름을 사용합니다.

  - 정렬방식은 인덱스 생성시에 이를 지정할 수 있습니다.

      위 생성문법에 2번 줄에 컬럼이름 뒤에 ASC 를 쓰면 오름차순정렬(기본값)이고 DESC 를 쓰면 내림차순 정렬

  - dept2 테이블의 dname 컬럼에 UNIQUE INDEX가 설정이 되면 데이터가 중복으로 들어갈 수 가 없음

 

SCOTT> INSERT INTO dept2       2     VALUES(9100,'임시매장',1006,'서울지사'); 1 row created. SCOTT> INSERT INTO dept2       2     VALUES(9101,'임시매장',1006,'부산지사'); INSERT INTO dept2 * ERROR at line 1: ORA-00001: UNIQUE constraint (SCOTT.IDX_DEPT2_DNAME) violated

 

- 첫 번째 dname의 값 '임시매장'은 중복이 되지 않아서 들어갔지만 두 번째로 입력하는 건

   이미 들어간 dname 이라서 에러가 발생함을 알 수 있습니다.

 

 

 ② Non UNIQUE INDEX

   : 중복되는 데이터가 들어가야만 하는 컬럼일 경우는 UNIQUE INDEX를 생성할 수 없기 때문에

     이럴 경우 Non UNIQUE INDEX를 생성

 

생성 문법 ​SCOTT> CREATE INDEX 인덱스명      2      ON 테이블명(컬럼명1 ASC | DESC , 컬럼명2 , …….) ;   ​ 생성 예제​) professor테이블의 position컬럼에 Non UNIQUE INDEX를 내림차순으로 생성하세요. ​SCOTT> CREATE INDEX idx_prof_position      2       ON professor(position DESC ) ;

 

 

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

 

 - 인덱스는 WHERE 절에 오는 조건 컬럼 이나 조인 컬럼 등에 만들어야 한다
    ex) WHERE PAY = 100 이란 조건일 경우 SAL 컬럼에 인덱스를 만들어야 함

 

 - SAL 컬럼으로 인덱스를 생성했는데 정작 SQL 문장에서는 WHERE PAY + 100 = 200 이라는 조건으로 조회 하면

    SAL 컬럼의 인덱스는 사용할 수 없음 ⇒ 이런 현상을 INDEX Suppressing Error 라고 부름

 

 - 인덱스를 사용하려면 WHERE 절의 조건을 절대로 다른 형태로 가공해서 사용하면 안 됨

 

 - 꼭 SQL 에서 WHERE PAY+100 = 200 이라는 형태로 써야 한다면?
     : 인덱스를 생성할 때 PAY+100) 형태로 인덱스를 생성하면 되고 이런 형태의 인덱스를 함수기반 인덱스라고 부름

 

​생성 예제​ ​SCOTT> CREATE INDEX idx_prof_pay_fbi      2      ON professor(pay+100) ;

 

 

 ④ DESCENDING INDEX

   : 큰 값이 먼저 오도록(즉, 내림차순) 인덱스를 생성하는 것

 

 - 주로 큰 값을 많이 조회하는 SQL 에 생성하는 것이 좋음

    ex) 계좌 조회 같은 메뉴는 주로 최근 날짜부터 먼저 나오게 하는 경우가 많습니다.

          날짜일 경우 최근 날짜가 큰 날짜이고

          예전 날짜가 더 작은 날짜이니 큰 날짜부터 먼저 조회하는 경우입니다.

 

​생성 예제​ ​SCOTT> CREATE INDEX idx_prof_pay      2      ON professor(pay DESC );

 

 

 

⑤ 결합 인덱스(Compisite INDEX)

  : 인덱스를 생성할 때 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것

//* 수정중  http://cafe.naver.com/dbian/251 *//

 

(2) BITMAP INDEX

 

 - 데이터 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용하는 인덱스

 - B-Tree 인덱스는 데이터의 값의 종류가 많고 데이터가 적을 경우에 사용하는 인덱스

 

 - 학생이 2천명이라 하더라도 성별 컬럼의 종류는 남,여 2가지뿐이므로 이곳은 BITMAP INDEX가 적당하다

 - 학번 컬럼의 경우에는 B-TREE INDEX로 생성하는 것이 유리

 

 - BITMAP INDEX를 생성하려면 우선 데이터의 변경량이 적어야 하며 종류 또한 적은 곳이 좋다 (=OLAP 환경)

 

 

 - BITMAP INDEX는 어떤 데이터가 어디에 있다라는 지도정보(MAP)을 BIT로 표시한다.

 - 어떠한 데이터가 존재하는 곳은 참인 1로 표시, 데이터가 없는 곳은 0으로 표시

 

 

 

 

​사원 테이블(성별)에 BITMAP INDEX 생성​ ​SCOTT>CREATE BITMAP INDEX idx_사원_성별_bit      2      ON 사원(성별) ;

 

- 해당 데이터가 있는 자리만 1로 표시, 나머지 행은 0으로 표시

 

- 이미 생성되어 있던 BITMAP INDEX에 새로운 데이터가 추가되면?!

   : 기존 BITMAP INDEX를 전부 수정해야 한다.

     즉, BITMAP INDEX는 데이터가 변경되는 테이블이나 컬럼에 만들게 되면 인덱스를 자주 수정하게 된다.

 

 

 

5. 인덱스의 주의사항 

 - 인덱스를 생성하면 'SQL이 빨라진다'라는 말은 맞을 수도, 틀릴 수 도 있다.

 

 - SELECT를 하는 경우에도 데이터 블록 수와 DB_FILE_MULTIBLOCK_READ_COUNT값과 분포도 등에 따라서

    인덱스가 빠를 수도 있고, Full Table Scan보다 늦어지는 경우도 종종 있다.

 

 1) DML에 취약하다.

  (1) Insert
  - Insert 작업일 경우 INDEX Spilt 현상이 발생 할 수 있고 이 현상으로 인해 Insert 작업의 부하가 심해 질 수 있음

  - INDEX Split 현상 : 인덱스의 Block들이 하나에서 두 개로 나누어지는 현상

      이유 : 테이블과 인덱스의 저장방식의 차이 때문

    - 테이블은 데이터가 입력이 될 때 정렬되지 않고 그냥 차례대로 입력, 인덱스는 데이터가 순서대로 정렬이 되어야

      하는데 문제는 기존 블록에 여유 공간이 없는 상황에서 그 블록에 새로운 데이터가 입력이 되어야 할 경우

 

   - 이 경우 기존 블록의 내용 중 일부를 새 블록에다가 기록한 후 기존 블록에 빈공간을 만들어 새로운 데이터를 추가

      즉 하나의 블록에 있던 내용이 두개의 블록으로 나누어진다는 의미로 INDEX Split 라고 부르는 것

  (2) Delete
  - 테이블은 데이터가 delete 되면 지워집니다. 그리고 그 자리에 다른 데이터가 들어와서 그 공간을 사용할 수 있다.

     그러나 INDEX 의 경우는 delete 가 되지 않고 해당 데이터가 사용 안 된다는 표시만 해 둠

     (테이블에서는 데이터가 지워지지만 인덱스에서는 데이터가 안 지워 진다는 의미)

     

  - 테이블에는 데이터가 1만 건이 있더라도 인덱스에는 2만 건이 있을 수 있다는 뜻입니다.

     물론 1만 건은 예전에 delete 된 데이터들 입니다.

     이런 상태의 인덱스를 사용하게 되면 인덱스를 사용함에도 불구하고 쿼리의 수행 속도가 아주 느려지게 됩니다.

 

 

  (3) Update
  - 테이블에서 데이터가 업데이트 될 경우 인덱스에서는 어떻게 될까?
     인덱스도 업데이트 될 거 같지만 인덱스에는 Update라는 개념이 없다

 - 테이블에 update가 발생 할 경우 인덱스에서는 delete가 먼저 발생 한 후 새로운 데이터의 Insert 작업이 발생하게 됨

   즉 update 는 두 가지의 작업이 인덱스에 동시에 일어나게되어 다른 DML 문장들 보다 더 큰 부하를 주게 된다

2) 타 SQL 실행에 악영향을 줄 수 있습니다.
  - 만약 어떤 테이블에 A 라는 인덱스만 있고 쿼리 수행시간이 1초 였는데

     그 테이블에 B 라는 새로운 인덱스를 생성 할 경우, 갑자기 쿼리 수행시간이 아주 느려지는 경우가 종종 발생

     즉 잘 수행되고 있던 SQL 문장이 새로 만든 인덱스 때문에 갑자기 아주 느려지는 것

     why?

    옵티마이져가 실행 계획을 세우게 되는데 갑자기 없었던 인덱스가 테이블에 생기면 더 최근에 만들어진 인덱스가

    더 좋을 것이라고 생각해서 잘 되고 있던 실행계획을 바꾸기때문

 

 

6. 인덱스 관리 방법

 

1) 인덱스 조회하기

 

 - 생성된 인덱스를 조회 :  USER_INDEXS 와 USER_IND_COLUMS 딕셔너리를 조회

 - 데이터베이스 전체에 생성된 내역을 조회 : DBA_INDEXES와 DBA_IND_COLUMNS 조회

 

​​SCOTT> SELECT table_name, index_name      2      FROM user_indexes      3      WHERE table_name='DEPT2';  TABLE_NAME     INDEX_NAME ------------ -----------------  DEPT2               IDX_DEPT2_DNAME  DEPT2               SYS_C0014275

 

 

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

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

 - 오라클 9i부터는 사용유무를 파악하는 기능을 제공함

 

​- 모니터링 시작하기 SCOTT> ALTER INDEX idx_dept2_dname MONITORING USAGE ;

 

 

​- 모니터링 중단하기 SCOTT> ALTER INDEX idx_dept2_dname NOMONITORING USAGE ;

 

​- 사용 유무 확인하기 SCOTT> SELECT index_name, used      2      FROM v$object_usage      3      WHERE index_name='IDX_DEPT2_DNAME';  INDEX_NAME               USED ---------------- -------  IDX_DEPT2_DNAME     NO

 

 

3) INDEX Rebuild하기

 - 대량의 DML 작업등을 수행 한 후에는 일반적으로 인덱스의 밸런싱 상태를 조사해서 문제가 있을 경우

   수정을 해 주는 작업들을 수행

   즉, 인덱스는 한번 만들어 놓으면 영구적으로 잘 작동하는 것이 아니라 생성 후에도 꾸준하게 관리를 해 주어야

    좋은 성능을 기대 할 수 있다는 뜻

 

​1. 테스트용 테이블 i_test 를 생성하고 데이터를 넣은 후 인덱스를 생성 SCOTT> CREATE TABLE i_test      2      (no number) ; Table created. SCOTT> BEGIN      2      FOR i IN 1..1000 LOOP      3      INSERT INTO i_test VALUES (i);      4      END LOOP;      5      COMMIT;      6      END;      ​​7      / PL/SQL procedure successfully completed. SCOTT> CREATE INDEX IDX_ITEST_NO      2      ON i_test(no); index created. 2. 인덱스의 상태 조회 SCOTT> ANALYZE INDEX idx_itest_no VALIDATE STRUCTURE ; INDEX analyzed. SCOTT> SELECT (del_lf_rows_len / lf_rows_len) * 100 BALANCE      2      FROM index_stats ;   BALANCE ----------               0           ← 0 에 가까울수록 좋은 상태를 의미 ​ 3. 테이블에서 1000 건의 데이터 중 300 건을 지운 후 인덱스 상태 조회 SCOTT> DELETE FROM i_test      2      WHERE no BETWEEN 1 AND 300 ; 300 rows deleted. SCOTT> SELECT COUNT(*) FROM i_test ;  COUNT(*) ---------           700 SCOTT> SELECT (del_lf_rows_len / lf_rows_len) * 100 BALANCE      2      FROM index_stats ;   BALANCE ----------              0 ​SCOTT> ANALYZE INDEX idx_itest_no VALIDATE STRUCTURE ; INDEX analyzed. SCOTT> SELECT (del_lf_rows_len / lf_rows_len) * 100 “BALANCE”      2      FROM index_stats ;   BALANCE ----------  29.5346182     ← 대략 30% 정도 밸런싱이 망가진 상태 ​ ​4. Rebuild 작업으로 수정 SCOTT> ALTER INDEX idx_itest_no REBUILD ; INDEX altered. SCOTT> ANALYZE INDEX idx_itest_no VALIDATE STRUCTURE ; INDEX analyzed. SCOTT> SELECT (del_lf_rows_len / lf_rows_len) * 100 BALANCE 2 FROM index_stats ;  BALANCE ----------              0       ← 다시 밸런싱이 0 으로 개선   ​

 

 

 - REBUILD 옵션 중에 ONLINE이라는 옵션도 있습니다. (Rebuild ONLINE)
    이 옵션은 REBUILD 작업 중에 데이터를 사용 가능하게 해 주지만 전체적인 성능이 많이 떨어지기 때문에

    잘 선택해서 사용해야 함

 

 

 

7. 인덱스 활용 예제

 

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

 - ORDER BY 구문을 사용하지 않고 어떻게 동일한 효과를 낼 수 있느냐가 성능상 아주 중요한 관건

   : 그 해답이 바로 인덱스!

 

 - 인덱스는 정렬이 되어 있기 때문에 테이블에서 데이터를 가져올 때 인덱스를 활용해서 가져온다면

   정렬이 된 상태로 출력 됨

 

 

(실습) 인덱스를 활용한 정렬하기

 

​Step 1. 예제 사원 테이블을 생성하고 데이터 입력 SCOTT>CREATE TABLE 사원      2      ( no NUMBER ,      3      name VARCHAR2(10),      4      sal NUMBER ) ; Table created. SCOTT> INSERT INTO 사원 VALUES(1,'홍길동',300); SCOTT> INSERT INTO 사원 VALUES(2,'김설희',250) SCOTT> INSERT INTO 사원 VALUES(3,'모병환',430); SCOTT> INSERT INTO 사원 VALUES(4,'도루묵',220); SCOTT> INSERT INTO 사원 VALUES(5,'나얼짱',620); SCOTT> INSERT INTO 사원 VALUES(6,'심슨',810); SCOTT> COMMIT ; Commit complete.     Step 2. Name 컬럼에 인덱스 생성 ​SCOTT> CREATE INDEX idx_사원_name      2      ON 사원(name) ; ​   ​Step 3. 인덱스를 사용하지 않는 일반적인 SQL 작성 ​​SCOTT> SELECT name FROM 사원 ;  NAME ----------  홍길동  김설희                   ← 인덱스를 사용하지 않았기 때문에 정렬 x  모병환  도루묵  나얼짱  심슨 ​ ​Step 4. 인덱스를 사용하도록 SQL을 작성 ​SCOTT> SELECT name FROM 사원      2     ​ WHERE name > '0' ;         ← 인덱스를 사용하라는 의미 ​NAME ----------  김설희  나얼짱                     ← 정렬된 데이터 출력  도루묵                          인덱스의 순서대로 출력된 결과  모병환  심슨  홍길동 ​ ​

 

 

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

 

 

SCOTT> SELECT MIN(name)        2      FROM 사원 ;  MIN(NAME) ---------  김설희                 ​                                      SORT 발생 !! ​

SCOTT> SELECT name        2      FROM 사원        3      WHERE name > '0'        4      AND rownum=1 ;  MIN(NAME) ---------                      SORT 발생 안함 !!  김설희

 

- 왼쪽 SQL는 정렬이 발생해서 데이터가 많을 경우 속도가 아주 느림

- 오른쪽 SQL은 인덱스를 활용하여 1건의 데이터만 읽어 오도록 함(ROWNUM=1),

   이 부분을 STOPKEY라고도 한다

 

 

 

 

 

- 위 그림은 최대값을 구하는 것이므로 인덱스를 아래쪽에서부터 1건만 읽으면 간단히 해결됨

- 인덱스를 아래쪽부터 읽는 것은 오라클 Hint라는 기능을 이용하면 됨

- Hint : 실행계획을 세워주는 옵티마이저에게 사람의 의도를 알려주는 기능

 

 

​SCOTT>SELECT MAX(name)      2      FROM 사원 ;  MAX(NAME) ----------                  MAX함수를 사용해 정렬이 발생하여 성능이 나빠짐  홍길동

 

Hint 사용 ​SCOTT> SELECT /*+ index_desc(s idx_사원_name) */ name      2      FROM 사원 s      3      WHERE name >='0'      4      AND rownum=1 ;  NAME ----------  홍길동

 

- 위의 방법이 예전부터 사용되던 인덱스를 사용하여 최대/최소값을 구하는 방식입니다.

- 이 방식은 인덱스가 문제가 없을 경우에는 아무런 문제가 없지만 만약의 경우 인덱스가 삭제가 되거나

   인덱스 컬럼이 변경 된다면 잘못된 결과를 만들어 낼 수 있다.

 

Hint 사용 ​SCOTT> SELECT /*+ index_desc (s idx_사원_name) */ max(name)      2      FROM 사원 s      3      WHERE name > '0' ;  MAX(name) --------------  홍길동

 

- 위와 같은 방법을 FIRST_ROW (MAX/MIN) 방법이라고 하며 만약 인덱스가 삭제나 변경이 된다 하더라도

   MAX(name)로 인해 정상적인 결과가 나오게 됨

 

 

 

8. Invisible Index (인비저블 인덱스) - 11g New Feature

 

 - 앞에서 살펴 본 대로 인덱스가 많을 경우 DML 문장에 나쁜 영향을 주기 때문에

   사용하지 않는 인덱스는 삭제를 해 주는 것 좋음

 

 - 문제는 해당 인덱스를 삭제 하려고 했을 때 정말 사용하는지 사용하지 않는 것인지를 정확하게 알아야 한다는 것이고

    9i 버전부터 이 부분을 도와주는 기능으로 인덱스 사용 유무를 모니터링 하는 기능이 등장했지만

    만약 모니터링 기간이 잘못되었다든지 해서 인덱스를 삭제했는데 나중에 생각지도 못했던 부분에서

    문제가 발생할 수 있음

 

 - 인비저블 인덱스 : 그래서 11g 에서는 인덱스를 실제 삭제하기 전에 "사용안함" 상태로 만들어서

                              테스트를 해 볼수 있는 기능을 제공

 

​ SCOTT> CREATE INDEX idx_emp_ename ON emp(ename) ; Index created. SCOTT> SELECT table_name,index_name,visibility      2      FROM user_indexes      3      WHERE table_name = 'EMP' ; TABLE_NAME         INDEX_NAME               VISIBILIT -------------- ----------------- ---------  EMP                 IDX_EMP_ENAME     VISIBLE  EMP                      PK_EMP                       VISIBLE SCOTT> ALTER INDEX IDX_EMP_ENAME INVISIBLE ; Index altered. SCOTT> SELECT table_name, index_name, visibility      2      FROM user_indexes      3      WHERE table_name = 'EMP' ; TABLE_NAME         INDEX_NAME               VISIBILIT -------------- ----------------- ---------  EMP                 IDX_EMP_ENAME     INVISIBLE  EMP                      PK_EMP                       VISIBLE

 

 

- 인덱스를 INVISIBLE 한다는 의미 : 옵티마이져가 실행계획을 세울 때 해당 인덱스를 보여주지 않는다는 의미

  즉 옵티마이져는 인비저블로 설정한 인덱스는 없다고 생각하고 실행계획을 세우게 됨

 

- 그러나 DML 작업시 인덱스 내용은 계속 반영이되므로 인덱스가 지워 진 것은 아닙니다.

   따라서 인비저블로 설정한 후 점검하여 다른 SQL 문장에 영향을 주는 것이 없는 것으로 확인되면

   해당 인덱스를 지우시면 됩니다.

 

​1. 다시 상태를 VISIBLE 로 변경하기 SCOTT> ALTER INDEX idx_emp_ename VISIBLE ; Index altered. ​ SCOTT> SELECT table_name, index_name, visibility      2      FROM user_indexes      3      WHERE table_name = 'EMP' ; TABLE_NAME         INDEX_NAME               VISIBILIT -------------- ----------------- ---------  EMP                 IDX_EMP_ENAME     VISIBLE          ← 변경됨  EMP                      PK_EMP                       VISIBLE ​ 2. SQL 힌트 구문에서 해당 인덱스를 사용하게 하기 ​SCOTT>SELECT /*+ index (emp idx_emp_ename) */ ename 2 FROM emp 3 WHERE ename >'0';

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.302-331