[Oracle SQL Developer] 기초 XVIII - INDEX

1. INDEX

---

  1.1. 설명
    - 자료의 검색 효율의 증대를 위해 사용하는 객체
    
    ( 장점 )
    - SELECT / INSERT / UPDATE 문 등에서 WHERE 절을 
      이용하여 조건 처리할 때 인덱스 사용으로 처리속도를 증대시킬 수 있다.
    - SORT / GROUP BY 의 처리 속도 증대시킨다.
    - DBMS의 부담을 경감 시킨다.
    
    ( 단점 )
    - 인덱스 구성에 공간과 시간이 소요된다.
    - 데이터의 수정, 삽입, 삭제가 빈번히 발생되면 인덱스 재구성에 많은 시간을 소요한다.
  
    ( 인덱스가 필요한 컬럼 )
    - 검색에 자주 사용되는 컬럼
    - WHERE 절의 조건에서 '=' 으로 자료 검색이 이루어지는 경우
    - PRIMARY, FOREIGN KEY
    - SORT 나 JOIN 연산에 자주 참여하는 컬럼
    
    ( 인덱스를 구성해서는 안되는 컬럼 )
    - 같은 값이 많이 존재하는 컬럼 (성별, 나이 등 중복되는 데이터)
    - WHERE 절의 조건문을 수행한 결과 테이블 내의 자료 대부분이 결과로 반환되는 경우
    - 검색 속도보다 삽입,수정, 삭제가 더 중요한 경우
  
    ( 인덱스 객체의 구분 )
      (1) UNIQUE / NON-UNIQUE
        . UNIQUE 인덱스는 중복 값을 허용하지 않음 (≒ 대표적인 예로 PK, FK 값)
        . NULL 도 한번만 허용한다. (예외 PK, FK 인덱스-NULL을 허용하지 않음)
        . NON-UNIQUE 인덱스 : 중복을 허용한다.
  
      (2) 단일/복합 인덱스
        . 단일 인덱스 : 하나의 컬럼으로 구성된다.
        . 복합 인덱스 : 두 개 이상의 컬럼으로 구성된다. 
                      WHERE 절 구성시 모든 인덱스 구성 컬럼이 참여 해야 효율적
                      조건문 구성시 컬럼의 기술 순서도 중요하다.
        
    ( 인덱스 객체의 종류 )
      - 인덱스 구성 방식에 의한 구분
      (1) NORMAL INDEX
        . B-TREE INDEX 라고도 한다.
        . 기본 인덱스 구성 방식
        . TREE 검색을 수행한다.
        . 검색시 기대 시간이 모두 동일하다.
        . 컬럼값과 ROWID(의사컬럼 - 물리적 저장위치 정보)를 기반으로 저장한다.
      
      (2) FUNCTION-BASED INDEX
        . 조건절에 함수가 사용되어 검색하는 경우에 해당 함수를 기반으로 인덱스를 구성한다.
        
      (3) BITMAP INDEX
        . 컬럼값과 ROWID(의사 컬럼 : 물리적 저장위치 정보)를 2진수로 변환하여 저장한다.
        . 수정, 삭제 등이 많이 발생되는 경우 효율적이다.
    
  
  1.2. 사용형식
    - 
    CREATE [UNIQUE|BITMAP] INDEX 객체명 
        ON 테이블명(컬럼명1[, 컬럼명2,...]) [ASC|DESC];
        
    EX) 회원테이블에서 회원의 생년월일을 인덱스로 구성하시오.
     
      CREATE INDEX IDX_MEM_BIR
        ON MEMBER(MEM_BIR);
        
        
      SELECT MEM_ID, 
           MEM_NAME, 
           MEM_BIR  
      FROM MEMBER
     --WHERE EXTRACT(YEAR FROM IDX_MEM_BIR) > '1975';
     WHERE MEM_BIR >= '19750101';  
        
        
  1.3. 인덱스 재구성
    - 원본테이블의 내용이 많은 변동(삭제, 수정, 삽입)이 발생된 경우
    - 사용하는 테이블 워크스페이스가 변경된 경우
    
    1)사용형식
    ALTER INDEX 인덱스명 REBUILD;
          
    EX) 회원테이블에서 주민번호 앞자리 두글자로 인덱스 생성
    -- 생성한 인덱스를 그대로 사용해야 효율성이 높아진다.
    CREATE INDEX IDX_MEM_REGNO1
        ON MEMBER(SUBSTR(MEM_REGNO1,1,2));
        
    SELECT MEM_ID, MEM_NAME, MEM_REGNO1
      FROM MEMBER
     WHERE (SUBSTR(MEM_REGNO1,1,2)) = '77';