ORACLE 인덱스(Index) 개념/종류/주의사항/활용,관리

/*

  [ 인덱스(Index)의 개념/종류/주의사항/활용,관리 ]

  

  1. 인덱스(Index)란???

    : 어떤 데이터가 HDD(하드디스크)의 어디에 있는지 위치 정보를 가진 주소록과 같은 개념.

    -> (데이터 - 위치주소(ROWID)) 쌍으로 저장하고 관리됨

    

    - 목적)

      : 빠르게 쿼리 검색을 하오기 위함

      

    데이터 위치 정보(주소) : ROWID -> 총 10Byte

*/

-- 데이터 튜플의 rowid를 검색해 보기

select rowid, empno, ename from scott.emp where empno = 7521;

-- 결과 : 

/*

  ROWID             EMPNO   ENAME

AAAE+3AAEAAAAFfAAC   7521    WARD

*/

-- [ ROWID의 구조 ] : AAAE+3AAEAAAAFfAAC

/*

  -AAAE+3 : 데이터 오브젝트의 번호

  -AAE : 파일 번호

  -AAAAFf : 블럭 번호

  -AAC : ROW(튜플) 번호

*/

/*

  2. [ 인덱스의 생성 원리 ]

  

    : 전체 테이블을 스캔(Table Full Scan) -> PGA내의 Sort Area에서 정렬(Sort) 공간 부족시 Temporary tablespace 이용해 정렬

                                        -> 정렬한 데이터를 기반으로 HDD Block에 기록

    -> 인덱스는 데이터가 ""정렬"" 되어 들어간다!!

*/

/*

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

  

    : 테이블(Table)과 인덱스(Index)의 비교

    - 테이블은 컬럼이 여러개, 데이터가 "정렬되지 않고" 입력된 순서대로 들어간다.

                            vs

    - 인덱스(Index)는 컬럼이 "Key컬럼(사용자가 인덱스를 지정하라고 지정한 컬럼)"과 "ROWID컬럼" 두개로 이루어져 있다.

      (오름차순, 내림차순으로 정렬 가능)

*/

select * from emp where empno = 7902; -- 를 찾을 때

/*

  데이터 파일의 블록이 10만개가 있다고 할 때 sql문을 수행한다면,

  1) 서버 프로세스가 구문파싱 과정을 마친 후 DB Buffer 캐시에 empno가 7902인 정보가 있는지를 먼저 확인한다.

  2) 해당 정보가 캐시에 없다면 디스크 파일에서 7902정보를 가진 블럭을 찾아서 DB Buffer 캐시로 가져온 뒤 해당 정보를 사용자에게 보여줌

  이 경우에

    - Index가 없는 경우 -> 7902정보가 디스크 어떤 블럭에 있는지 모름으로 10만개 전부 DB Buffer 캐시로 복사한 뒤 Full Scan으로 찾게 됨.

    - Index가 있는 경우 -> where절의 조건으로 준 컬럼이 Index의 Key로 생성되어 있는지 확인한 뒤, 인덱스에 먼저 가서 7902정보가

                          어떤 ROWID를 가지고 있는지 확인한 뒤 해당 ROWID에 있는 블럭만 찾아가서 db Buffer 캐시에 복사하게 됨.

*/

/*

  4. [ 인덱스의 종류 ]

    1) B-TREE 인덱스(Index)

        : OLTP(Online Transaction Processing : 실시간 트랜잭션 처리)

          -> 실시간으로 데이터 입력과 수정이 일어나는 환경에 많이 사용함.

        

    2) BITMAP 인덱스(Index)

        : OLAP(Online Analytical Processing : 온라인 분석 처리)

          -> 대량의 데이터를 한꺼번에 입력한 뒤 주로 분석이나 통계 정보를 출력할 때 많이 사용함.

          -> 데이터 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용함

        

        (1) [ B-Tree 인덱스 ]

          B : binary, balance 의 약자

          

              ROOT block(branch block에 대한 정보)

               |

            Branch Block(Left Block에 대한 정보)

               |

              Leaf Block(실제 데이터들의 주소)

          

        (1-1) [ B-Tree 인덱스의 종류 ]

          (A) Unique Index  

              : 인덱스 안에 있는 컬럼Key값에 중복되는 데이터가 없다.(성능이 좋음)

              -> 마치 Unique 제약조건과 유사하다. 따라서, Unique 제약조건 사용시에도 자동으로 UNIQUE INDEX가 생성된다.

또한, 기본키를 생성해도 오라클은 자동으로 UNIQUE INDEX를 생성하게 되는데 이때 UNIQUE나 기본키 객체명과 동일하게 생성된다. 

              - 생성 방법)

                SQL > create unique index 인덱스명

                      on 테이블명(key로지정할컬럼명 1 ASC|DESC, 컬럼명2...);

                ASC : 오름차순 정렬(기본값)

                DESC : 내림차순 정렬

                

                EX)

                -- dept테이블과 같은 테이블 하나 생성

                SQL > create table dept2 as select * from dept;

               

                -- dname을 key로하는 unique index를 생성

                SQL > create unique index idx_dept2_dname on dept2(dname); 

                -- 튜플 하나 추가

                SQL > insert into dept2 values(50,'개발부','서울');

                -- 위의 추가한 튜플과 dname값이 일치하는 다른 튜플을 하나 추가할라하면 unique인덱스이기 때문에

                -- 에러가 발생한다.

                SQL > insert into dept2 values(60,'개발부','인천');

                -- 이미 들어가 있는 dname이기 때문!!

            

            (B) Non Unique Index 

                : 중복되는 데이터가 들어가야 하는 경움(key로 지정한 필드의 중복된 값이 들어갈 수 있다.)

                

                - 생성 문법)

                SQL > create index 인덱스명 on 테이블명(컬럼명1 ASC|DESC, 컬럼명2....);

                

                EX)

                   -- 테스트용 테이블 새로 생성

                   SQL > create table dept3 as select * from dept;

                

                   -- dname을 Key로하는 Non-Unique index 생성

                   SQL > create index idx_dept3 on dept3(dname);

                 

                   SQL > insert into dept3 values(50,'개발부','서울');

                 

                   -- 중복되는 dname을 가진 튜플이 삽입이 가능하다.

                   SQL > insert into dept3 values(60,'개발부','인천');

            (C) FBI 인덱스( Function Based Index ) : 함수기반 인덱스

              : - 인덱스는 where절에 오는 조건 컬럼이나 조인에 쓰이는 컬럼으로 만들어야 한다.

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

                

                예를들자면)

                  where절의 조건이 sal + 100 > 200 이러한 조건일 경우

                  단순히 index컬럼을 sal로만 지정하게 되면 인덱스가 적용되지 않고 검색쿼리가 수행되게 됨

                  -> 따라서, 인덱스도 테이블명(sal+100)의 형태로 "함수기반 인덱스"로 사용해야 함

                  

                  EX)

                    SQL > create index idx_dept_fbi on emp(sal+100);

                    -- 이때, emp테이블에는 sal+100 컬럼은 없지만 인덱스를 만들 때 저 연산을 수행해서 인덱스를 만듬

                    

                    (주의 사항)

                    - 임시적인 해결책은 될 수 있어도 근본적인 처방은 아니다.

                    - sal + 100을 인덱스로 생성했는데 쿼리 조건이 변경되면 인덱스를 다시 생성해야 한다.

                    - FBI는 기존의 인덱스를 활용할 수 없다.(단점)

              

              (D) Descending Index : 내림차순으로 인덱스를 생성한다.

                    : 큰 값을 많이 조회하는 SQL에 생성하는 것이 좋다.

                    ex) 최근 날짜부터 조회, 회사 매출 조회

                    

                    SQL > create index idx_emp_sal on emp(sal desc);

                    

                    (주의사항)

                      : 하나의 메뉴에 오름차순과 내림차순을 한번에 조회할 경우

                      -> 오름차순, 내림차순 두 개의 인덱스를 만들면 DML의 성능에 악영향을 미침

                      -> 이때는, 힌트를 사용한다.(아래나 위에서부터 읽도록 할 수 있다.)

                

              (E) 결합 인덱스(Composite Index) 

                  : 인덱스 생성시에 두개 이상의 컬럼을 합쳐서 인덱스를 생성하는 인덱스

                  -> 주로 where 절의 조건이 되는 컬럼이 2개 이상으로 and로 연결되는 경우 사용된다.

                  -> 잘못 생성하게 되면 성능에 영향을 미칠 수 있다.

                    (컬럼의 순서에 따라 효율에 차이가 있다.) -> 보통, 자주 사용하는 컬럼을 앞에 위치시키는 것이 좋다.

                    

                    생성 방식)

                      SQL > create index 인덱스명 on 테이블명(컬럼명1, 컬럼명2);

                      

                    EX) (성별, 이름) 두개의 컬럼으로 인덱스를 생성하는 경우

                      SQL > create index idx_emp_composite on emp(gender,dname);

                      

                      -- 생성된 인덱스는 정렬되어 데이터를 저장시키게 되는데

                      -- 이때, 인덱스 생성시 기술한 컬럼순으로 정렬이 된다. 

                      -- 즉, gender(성별)을 기준으로 asc로 정렬한 뒤 같은 성별일 때

                      -- dname을 asc로 정렬해 인덱스를 생성하는 것

                      

                      -- 생성된 인덱스가 어떤 모습으로 저장되어 있는지 검색해 보자.

                      EX) emp테이블의 empno와 deptno 두개의 컬럼을 조건으로 하는 인덱스 생성

                          SQL > create table emp3 as select * from emp;

                          SQL > create index idx_emp3_composite on emp3(deptno,empno);

                          

                          -- 정렬된 상태로 저장되어있는 인덱스 모습을 보기

                          SQL > select * from emp3 where empno > 0 and deptno > '0';

                        

                          

                          -- 이때, 검색 쿼리가 deptno가 20이면서 empno가 7902인 사원정보를 검색한다면

                          SQL > select * from emp3 where deptno = 20 and empno = 7902;

                          

                          -- 인덱스 생성시 dept 컬럼을 앞에 기술했기 때문에 dept=20을 먼저 찾기위해

                          -- 4번 검사를 하고 이때 20이 나옴으로 empno 7369검사(1번) 다음 deptno 검사 1번

                          -- 다시 empno 검사(1번) 식으로해서 최종 찾는 데이터까지

                          -- 총 9번의 검사를 해 찾게된다.

                          

                          -- 이런식으로 검사가 이루어지기 때문에 주의할 사항이 생기는데

                          (deptno, empno)로 생성할지 (empno, deptno)로 생성할지에 따라 검사 효율이 다르게 나타날 수 있다.

                          

                          이때는 평균적인 검사 효율을 어느정도 계산을 해 바서 인덱스를 생성하는 것이 중요하다!!(신중히 생성하자)

       

       

        (2-1) [ BITMAP 인덱스의 종류 ]

            : 데이터 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용된다.

            

            Bitmap Index를 생성하려면 데이터의 변경량이 적어야 하고, 값의 종류도 적은 곳이 좋다.

            일반적으로 OLAP환경에서 많이 생성하게 되는대

            

            Bitmap Index는 어떤 데이터가 어디에 있다는 지도정보(MAP)를 Bit로 표기하게 된다.

            데이터가 존재하는 곳은 1로 표시하고, 데이터가 없는 곳은 0으로 표기한다.

            정보를 찾을 때 1인 값만 찾게 된다!

            

            SQL > create bitmap index 인덱스명 on 테이블명(컬럼);

            

            bitmap index를 생성하면 성별 컬럼 값의 종류대로 map이 생성된다.

            남자 : 1 0 1 0 0 -> 남자데이터가 1,3번 튜플에 있다.

            여자 : 0 1 0 1 1 -> 여자데이터가 2,4,5 튜플에 있다.

            

            이때 문제되는게

            bitmap index사용하고 있는 상태에서 만약 컬럼 값이 새로 하나 더 생긴다면?

            기존의 Bitmap Index를 전부 수정해야한다.

            

            -> B-Tree Index는 관련 블럭만 벽여되지만 Bitmap Index는 모든 맵을 다 수정해야 한다는 문제점!

            -> Bitmap Index는 블럭 단위로 lock을 설정해서 같은 블럭에 들어있는 다른 데이터도 수정작업이 안되는 경우가

               종종 발생한다.

                       

*/

/*

  5. [ 인덱스 주의사항 ]

    

    - 인덱스를 사용하면 무조건 효율이 좋을까? NO

   

    -> [ 인덱스는 DML에 취약 ]

    근거)

      1) INSERT 작업의 경우

        : "index split"현상이 발생할 수 있다.

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

        -> 인덱스는 데이터가 순서대로 정렬되어 저장되게 되는데, 기존 블럭에 여유 공간이 없는 상황에서

           그 블럭에 새로운 데이터가 입력되어야 하는 경우

           오라클은 기존 블럭의 내용 중 일부를 새 블럭에다가 기록한 다음 기존 블럭에 빈 공간을 만들어서

           새로운 데이터를 추가하게 된다.

           --> 따라서, 성능면에서 매우 불리하다.

           a)Index Split은 새로운 블럭을 할당 받고 key를 옮기는 복잡한 작업을 수행

           b)Index Split이 이루어지는 동안 해당 블럭에 대해 키 값이 변경되면 안되므로 DML이 블로킹된다.

            enq:TX-index contention 대기 이벤트 발생(RAC-gc current split)

      

      2) DELETE 작업의 경우

        : 일반적인 테이블에서 데이터가 delete될 경우 해당 위치 데이터가 지워지고 그 공간을 사용 가능하다.

                        vs

          하지만, Index에서 데이터가 delete될 경우 -> "데이터는 지워지지 않고, 사용하지 않는다는 의미의 표시만 해두게 된다!"

            --> 즉, 테이블에 2만건의 데이터가 있었는데 1만건을 삭제해도 Index에는 데이터가 2만건이 존재한다는 말이된다.

            -> 인덱스를 사용해도 수행속도를 기대하기는 힘들다.

            

      3) UPDATE 작업의 경우

        : 인덱스에는 UPDATE란 작업이 존재하지 않기 때문에

          기존의 데이터를 DELETE한 다음 새로운 값의 데이터를 INSERT하는 두번의 과정으로 작업이 발생하는데

          따라서, 다른 DML작업보다 더 큰 부하를 주게 된다.

   

[ 최종적으로 인덱스 생성시 고려해야할 사항 ]

1 일반적으로 테이블 전체 로우 수의 15%이하의 데이터를 조회할 때 인덱스를 생성한다.

2 테이블 건수가 상당히 적다면 굳이 인덱스를 만들 필요가 없다. -> 테이블 건수가 적으면 인덱스를 경유하기보다 테이블 전체를 스캔하는 것이 더 빠르다.

3 인덱스 생성시 컬럼은 유일성 정도가 좋거나 범위가 넓은 값을 가진 컬럼을 지정하는 것이 좋다. (NULL값을 많이 갖는 컬럼은 피하는 것이 좋다.)

4 결합 인덱스 생성시에는 컬럼의 순서가 중요하다.

-> 보통, 자주 사용하는 컬럼을 앞에 지정한다.

5 테이블에 만들 수 있는 인덱스의 수는 제한이 없으나, 너무 많이 만들면 오히려 성능 부하가 발생한다.

why? -> 인덱스 생성을 해 놓으면 해당 테이블에 DML 작업(insert, delete, update)시 인덱스에도 수정작업이 동시에 발생하기 때문에 과도하게 많은 인덱스를 생성해 놓으면

오히려 성능 부하가 걸릴 수 있다.

-> 일반적으로, 테이블 당 최대 5개를 넘지 않는 것이 좋다.

6 데이터의 검색보다 수정, 삭제, 삽입 작업이 빈번한 테이블에는 인덱스를 생성하지 않는 것이 좋다.

-> 인덱스는 DML작업에는 성능이 좋지 않기 때문에 검색을 위주로 하는 테이블에 생성하는 것이 좋다.(위에서 언급한 성능 이슈들이 발생할 수 있다.)

7 인덱스 생성시 무엇보다 가장 중요한 점은 SQL 쿼리가 인덱스를 잘 활용할 수 있게끔 짜여져야 한다는 것이다.(쿼리를 잘 짜서 만들자!)       

*/

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  [ 인덱스의 활용,관리의 예 ]

  

  1. [ 인덱스를 조회하기 위한 딕셔너리 ]

    - user_indexes, user_ind_columns

    - dba_indexes, dba_ind_columns

-- DEPT2 테이블에 적용된 인덱스 검색 방법(user_indexes 딕셔너리에서)

select table_name, index_name

from user_indexes

where table_name = 'DEPT2';

-- EMP 테이블에 적용된 인덱스 검색

select table_name, index_name

from user_indexes

where table_name = 'EMP';

-- [index를 rebuild하는 방법]

  -- 인덱스는 한번 만들어 놓으면 영구적으로 좋은 성능을 가질수 없기 때문에

  -- 항상 관리를 해주어야 한다. 그 방법이 rebuild 하는 것이다.

  

  -- 테스트용 테이블 생성

  create table test_rebuild(

    no number

  );

  -- 테스트용 데이터 1000개 넣기

  begin

    for i in 1..1000 loop

      insert into test_rebuild values(i);

    end loop;

  end;

  /

  commit;

  -- 들어간 데이터 확인

  select * from test_rebuild;

  

  -- 인덱스 생성

  create index idx_test

  on test_rebuild(no);

  

  -- 인덱스의 상태 조회

  analyze index idx_test validate structure; --인덱스 상태를 분석함

  

  -- 인덱스 분석 쿼리를 수행하면 index_stats 딕셔너리에 해당 결과가 반영되게 된다.

  -- 또한,  

  -- 테이블 데이터 삭제시 인덱스에는 삭제되지 않고 사용하지 않는다는 표시만 해 두는대

  -- 이때, 관리가 필요함 따라서, 인덱스에 삭제상태로 표시한 데이터가 몇개 있는지(DEL_LF_ROWS_LEN)를

  -- index_stats 에서 조회하는데

  -- 아래 구문은 삭제한데이터/총있는데이터 비율을 구한다.

  select (DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100 AS balance

  from index_stats;

  -- 0이 나온다면 좋은 상태임을 알 수 있다.

  

  -- 그럼, 데이터를 삭제해 보자.

  delete from test_rebuild where no between 1 and 400; -- 1 ~ 400까지 데이터 삭제

  -- 남은 데이터 개수 : 600개

  select count(*) from test_rebuild;

  

  -- 다시한번 인덱스 분석을 해 주어야 함 index_stats에 반영될 수 있도록...

  analyze index idx_test validate structure;

  

  -- 39.6~~~%가 나오게 됨 -> 40%정도가 인덱스의 균형이 좋지 않다는 것을 의미함.

  select (DEL_LF_ROWS / LF_ROWS) * 100 AS balance

  from index_stats;

  

  select * from index_stats;

  select DEL_LF_ROWS from index_stats; -- 400

  

  select LF_ROWS from index_stats; -- 1000

  

  -- 40%를 성능을 위해 수정하기 위해선 rebuild 작업이 필요하다.

  alter index idx_test rebuild; -- rebuild 명령으로 인덱스를 수정한다.

  

  -- 다시 분석해서 보면

  analyze index idx_test validate structure;

  select (DEL_LF_ROWS / LF_ROWS) * 100 AS balance from index_stats; -- 0이 나오게 됨

  

  -- 이처럼 인덱스는 rebuild를 통해 꾸준히 관리가 필요하다.

  

  

  -- [ 인덱스 활용 예 ]

  create table emp3(

    no number,

    name varchar2(10),

    salary number

  );

  

  insert into emp3 values(1, '강호동', 200);

  insert into emp3 values(2, '이경규', 300);

  insert into emp3 values(3, '이경실', 100);

  insert into emp3 values(4, '유재석', 400);

  insert into emp3 values(5, '홍길동', 150);

  insert into emp3 values(6, '홍길자', 250);

  

  select * from emp3;

  

  -- index 생성

  create index idx_name

  on emp3(name);

  

  -- where절 조건으로 인덱스 컬럼으로 지정한 name을 쓰지 않았음으로 인덱스를 거치지 않고

  -- 결과가 나오기 때문에 정렬이 되어 있지 않고 출력되게 된다.

  select name from emp3;

  

  -- where절에 name조건을 주었기 때문에 인덱스를 거쳐 정렬된 상태로 나오게됨

  select name from emp3 where name > '0'; -- 이처럼, order by 효과를 대신할 수 있음

  

  -- order by시에도 정렬을 위한 수행 시간이 필요한대 인덱스를 활용하면 이러한 시간을 

  -- 줄일 수 있다.

  

  -- [인덱스를 활용해서 최소값을 구해보자]

  

  -- 먼저 index를 쓰지 않고 찾아오는 방식

  select min(name) from emp3; -- 내부적으로 정렬을 한번해서 찾아오게 됨 0.04초 걸렷음.

  

  -- 인덱스를 쓴 경우

  select name from emp3 where name > '0' and rownum = 1; -- 정렬이 발생하지 않고

  -- 가져올 수 있기 때문에 상당히 빠름 -> 0초로 찍힘

  

  -- 최대값도 해보자.

  select max(name) from emp3; -- 인덱스 사용하지 않은 경우 0.002초(정렬이 먼저 발생하기 때문)

  

  -- 인덱스의 hint를 사용해 최대값을 구해오는 방법

    -- hint? : 

    -- 아래의 경우는 실행계획을 담당하는 옵티마이저에게 ~~게 할거라고 알려주는 건대

    -- 즉, 기본적으로는 asc지만, desc로 지정해 가장 큰 값이 위로 올라와 있게 되기 때문에

    -- rownum = 1을 통해 최대값을 가져올 수 있게된다.

  select /*+ index_desc(emp3 idx_name)*/ name

  from emp3

  where name > '0' and rownum = 1;

  

  -- rownum없이도 구해올 수 있는 방법(위의 방법은 인덱스가 수정되는 과정에서 문제가 발생할 수 있음)

  -- 이 방식을 -> "first_row max방법" 이라 부른다.

  select /*+ index_desc(emp3 idx_name)*/ max(name)

  from emp3

  where name > '0';

  

  

  -- 사용하지 않는 인덱스의 경우 삭제하는게 좋다.(주의사항에서 언급되어 있다.)

  -- 이렇게 삭제시 정말 사용하지 않을건지 알고 삭제하는 것이 중요한데

  -- 11g 버전에서는 사용하지 않는 상태로 만들어서 테스트 해볼 수 있도록 제공하는데

  -- invisible 인덱스란 개념을 제공한다.

    -- : 인덱스를 삭제하기 전에 사용안함 상태로 만들어 테스트 할 수 있는 기능.

  

  -- salary 컬럼으로 emp3테이블의 인덱스 생성

  create index idx_sal on emp3(salary);

  

  select table_name, index_name, visibility 

  from user_indexes

  where table_name = 'EMP3';

  

  -- 인덱스를 사용안함 상태로 바꾸기

  alter index idx_sal invisible;

  -- 다시 조회하면 invisible상태로 바껴있음을 알 수 있음.

  -- 실행계획을 세우는 옵티마이저가 해당 인덱스를 사용하지 않겠다는 의미

  -- 하지만, 인덱스가 지워진건 아니고 보여지지 않은 것과 같은 효과란 것

  select table_name, index_name, visibility 

  from user_indexes

  where table_name = 'EMP3';

  

  -- 다시 visible 상태로 바까보기

  alter index idx_sal visible;