21.인덱스

/*
    < INDEX >
    SQL 명령문의 처리 속도를 향상 시키기 위해서 컬럼에 대해 생성하는 오라클 객체
    컬럼을 복사하여 정렬해 놓고 정렬된 상태의 데이터를 조회
    
    - 검색 속도가 빨라지고 시스템에 걸리는 부하를 줄여 시스템 전체 성능 향상
    - 인덱스를 위한 추가 저장 공간이 필요하고 인덱스를 생성하는데 시간이 걸림
    - 데이터의 변경 작업이 자주 일어나는 경우 오히려 성능이 저하 될수 있음
    
    [사용법]
    CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명, 컬럼명 | 함수명, 함수 계산식);
    
*/
-- 인덱스 조회 
SELECT * FROM USER_INDEXES;
SELECT * FROM USER_INDEXES WHERE TABLE_NAME='TB_STUDENT';
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'TB_STUDENT';

-- 인덱스 테스트용 테이블 생성
CREATE TABLE copy_student 
AS SELECT * FROM tb_student WHERE 1=2;
-- 시퀀스 생성
-- 1부터 시작해서 1씩 증가하는 시퀀스 생성
CREATE SEQUENCE seq_tb_student_no; 
DROP SEQUENCE seq_tb_student_no;
-- 데이터 삽입
-- 학생테이블의 데이터를 복사하여 입력
-- student_no -> 시퀀스의 증가값 이용, A로 시작하는 8자리 숫자값 
-- student_ssn -> 시퀀스의 현재값 이용
SELECT 'A'||LPAD(seq_tb_student_no.NEXTVAL,8,0) AS student_no, seq_tb_student_no.CURRVAL AS student_ssn
FROM DUAL;

SELECT COUNT(*) FROM copy_student;
DELETE FROM copy_student;

INSERT INTO copy_student 
    (SELECT 'A'||LPAD(seq_tb_student_no.NEXTVAL,7,0), DEPARTMENT_NO, STUDENT_NAME,
           seq_tb_student_no.CURRVAL, STUDENT_ADDRESS, ENTRANCE_DATE,
           ABSENCE_YN, COACH_PROFESSOR_NO
    --FROM tb_student);
    FROM copy_student);
-- 인덱스 생성 전 : 0.051초
-- 인덱스 생성 후 : 0.016초
SELECT * FROM copy_student WHERE student_no = 'A00003928'; 

-- 인덱스 생성
-- 컬럼에 중복이 없는경우 UNIQUE 인덱스로 생성
CREATE UNIQUE INDEX idx_copy_student_no  ON copy_student(student_no);

-- ERR : UNIQUE인덱스는 중복된 컬럼에는 사용 불가
-- CREATE UNIQUE INDEX idx_copy_student_no  ON copy_student(student_name);

-- 인덱스 제거
DROP INDEX idx_copy_student_no;

-- 옵티마이저(Optimizer)는 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행한다. 
-- 이러한 최적의 실행 방법을 실행계획(Execution Plan)이라고 한다. 
-- UNIQUE SCAN : 컬럼이 유일한 값이고 동등조건(=)인 경우 사용

/*
    NONUNIQUE INDEX 
    중복 값이 있는 컬럼에 생성 가능한 인덱스 
    
    *인덱스를 타지 않는 경우
        - 내부적으로 데이터 형변환이 일어나는 경우
*/
-- 이름컬럼에 인덱스 생성
CREATE INDEX idx_student_name ON copy_student(student_name);
-- 인덱스 생성 전 : 0.034초
-- 인덱스 생성 후 : 0.016초
SELECT count(*) FROM copy_student WHERE student_name='윤상민';

-- 날짜컬럼에 인덱스 생성
CREATE INDEX idx_entrance_date ON copy_student(entrance_date);
-- 인덱스 생성 전 : 0.059초
-- 인덱스 생성 후 : 0.017초
SELECT COUNT(*) FROM copy_student WHERE entrance_date = '04/03/01';
-- 조건에 따라 시간에 차이가 남, 동등비교가 빠름 

-- 주민등록번호에 인덱스 생성
CREATE INDEX idx_student_ssn ON copy_student(student_ssn);
-- 인덱스 생성 전 : 0.034초
-- 인덱스 생성 후 : 0.001초
SELECT COUNT(*) FROM copy_student WHERE student_ssn = '100000';
-- 인덱스 생성 전 : 0.034초
-- 인덱스 생성 후 : 0.074초
SELECT COUNT(*) FROM copy_student WHERE student_ssn = 100000; -- 인덱스 적용 안됨

/*
    인덱스 재생성
    DML 작업을 수행한 경우, 인덱스 엔트리가 논리적으로만 제거됩니다.
    필요없는 공간을 차지하고 있지 않도록 인덱스를 재 생성 해줍니다.
*/
ALTER INDEX idx_student_ssn REBUILD;

/*
    <결합 인덱스>
        두개 이상의 컬럼을 사용
*/
CREATE INDEX idx_copy_student_a_p
    ON copy_student(absence_yn, coach_professor_no);
SELECT index_name, column_name FROM USER_IND_COLUMNS
    WHERE table_name = 'COPY_STUDENT' ORDER BY INDEX_NAME;
    
/*
    함수 기반 인덱스
        
*/
CREATE INDEX idx_copy_studnet_성별 ON copy_student(SUBSTR(student_ssn,3,1));

-- 인덱스 전후 조회 시간 : 0.09 -> 0.04
SELECT COUNT(SUBSTR(student_ssn,3,1)) from copy_student WHERE SUBSTR(student_ssn,3,1) = '8';