인덱스

<인덱스>

 

• 인덱스: RDBMS에서 검색속도를 높이기 위해 사용하는 하나의 기술

• WHERE절로 행을 찾을때 전체 테이블을 다 훑어서 비효율적. 자주 찾는 열은 별개의 공간에 인덱스를 만들어서 검색하도록 함

• 키 값을 기초로 하여 테이블에서 검색과 정렬 속도 향상시킴

• 테이블의 기본키는 자동으로 인덱스됨

 

---

 

• 인덱스 확인하기

SELECT
    TABLE_NAME 테이블명,
    INDEX_NAME 인덱스이름,
    COLUMN_NAME 컬럼이름
FROM ALL_IND_COLUMNS --오라클에서 자동 생성됨
WHERE TABLE_NAME = 'DEPARTMENTS'; --테이블 이름을 대문자로

 

 

---

 

 

--실습으로 테이블 만들기
CREATE TABLE members (
    member_id NUMBER,
    first_name VARCHAR2(100) NOT NULL,
    last_name VARCHAR2(100) NOT NULL,
    gender CHAR(1) NOT NULL,
    dob DATE NOT NULL,
    email VARCHAR2(255) NOT NULL,
    PRIMARY KEY (member_id)
);
DESC members;

 

 

---

 

 

--인덱스 확인하기
SELECT
    TABLE_NAME 테이블명,
    INDEX_NAME 인덱스이름,
    COLUMN_NAME 컬럼이름
FROM ALL_IND_COLUMNS --오라클에서 자동 생성됨
WHERE TABLE_NAME = 'MEMBERS'; --테이블 이름을 대문자로

 

 

---

 

SELECT * FROM MEMBERS
WHERE last_name='Harse'; --테이블 전체 검색

 

---

 

 

EXPLAIN PLAN FOR --다음 나오는 select문을 실행하여 보고서 작성
SELECT * FROM members
WHERE last_name = 'Harse';

 

 

---

 

작성된 보고서를 보기 위하여 출력하라는 명령문
SELECT PLAN_TABLE_OUTPUT --설명문을 출력
FROM TABLE (DBMS_XPLAN.DISPLAY());

 

 

 

---

 

 

• 인덱스 만들기

CREATE INDEX members_last_name_i
ON members (last_name);

 

 

 

--인덱스 확인하기
SELECT
    TABLE_NAME 테이블명,
    INDEX_NAME 인덱스이름,
    COLUMN_NAME 컬럼이름
FROM ALL_IND_COLUMNS --오라클에서 자동 생성됨
WHERE TABLE_NAME = 'MEMBERS'; --테이블 이름을 대문자로

 

 

 

EXPLAIN PLAN FOR 
SELECT * FROM members
WHERE last_name = 'Harse';
 
SELECT PLAN_TABLE_OUTPUT 
FROM TABLE (DBMS_XPLAN.DISPLAY());

 

 

---

 

• 인덱스 삭제하기

 

DROP INDEX members_last_name_i;

 

 

 

 

SELECT *
FROM members
WHERE first_name LIKE 'M%' AND last_name LIKE 'A%';

 

 

 

• 멀티 인덱스 만들기

CREATE INDEX name_i
ON members(first_name, last_name);

 

 

--성능평가
EXPLAIN PLAN FOR
SELECT * FROM members
WHERE first_name LIKE 'M%' AND last_name LIKE 'A%';

 

 

 

SELECT PLAN_TABLE_OUTPUT --설명문을 출력
FROM TABLE (DBMS_XPLAN.DISPLAY());

 

 

---

 

• 인덱스 삭제 후 성능평가

DROP INDEX name_i;
 
EXPLAIN PLAN FOR
SELECT * FROM members
WHERE first_name LIKE 'M%' AND last_name LIKE 'A%';
 
SELECT PLAN_TABLE_OUTPUT --설명문을 출력
FROM TABLE (DBMS_XPLAN.DISPLAY());
--cost가 2에서 5가 됨 (인덱스가 있어야 속도가 빠르다.)