혼공 SQL(Oracle)- INDEX

Jobmania·2023년 2월 13일

Index sql

SQL 잘쓰기

목록 보기

9/16

1) INDEX의 의미

RDBMS에서 검색속도를 높이기 사용하는 하나의 기술
INDEX는 색인입니다. 해당 TABLE의 컬럼을 색인화(따로 파일로 저장)하여 검색시 해당 TABLE의 레코드를 full scan 하는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색속도를 빠르게 합니다.

2) INDEX의 장점

키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킵니다.
질의나 보고서에서 그룹화 작업의 속도를 향상시킵니다.
테이블의 기본 키는 자동으로 인덱스 됩니다.

3) INDEX의 단점

인덱스 된 필드에서 데이터를 (DML 작업시) 업데이트하거나, 레코드를 추가 또는 삭제할 때 성능이 떨어집니다.
인덱스가 데이터베이스 공간을 차지해 추가적인 공간이 필요해진다. (DB의 10퍼센트 내외의 공간이 추가로 필요)
인덱스를 생성하는데 시간이 많이 소요될 수 있다.
데이터 변경 작업이 자주 일어날 경우에 인덱스를 재작성해야 할 필요가 있기에 성능에 영향을 끼칠 수 있다.

따라서 어느 필드를 인덱스 해야 하는지 미리 시험해 보고 결정하는 것이 좋습니다. 인덱스를 추가하면 쿼리 속도가 빨라지지만, 데이터 행을 추가하는 속도는 느려지게 됩니다.

자동 인덱스 : PK(기본키) 또는 UINQUE 제한 규칙에 의해 자동적으로 생성되는 인덱스.
수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스

인덱스를 생성하는 것이 좋은 컬럼
① WHERE절이나 join조건 안에서 자주 사용되는 컬럼

다음과 같은 경우에는 인덱스 생성이 불필요 하다.
① 테이블이 작을 때
② 테이블이 자주 갱신될 때 (갱신 시간이 느려짐)


--  인덱스 (PK OR UNIQUE일 경우 자동으로 생성된다.)
CREATE TABLE members(
    member_id NUMBER,
    first_name VARCHAR2(100) NOT NULL,
    last_name VARCHAR2(100) NOT NULL,
    gender VARCHAR(1) NOT NULL,
    dob DATE NOT NULL,
    email VARCHAR2(255) NOT NULL,
    PRIMARY KEY(member_id)
);

-- 인덱스 조회하기
SELECT * 
FROM ALL_IND_COLUMNS
WHERE table_name = '테이블 명';

/// 1000데이터 삽입..

SELECT * FROM members  -- 인덱스 만들기전 0.008 인덱스 만든 후 0.002 속도 
WHERE last_name = 'Harse';

SELECT * FROM members  -- 0.001 조금더 빠름
WHERE member_id = 139;

-- 인덱스생성
CREATE INDEX members_last_name_i -- 인덱스이름
ON members(last_name);               -- 인덱스열 
-- 인덱스--삭제
DROP INDEX members_last_name_i;  

EXPLAIN PLAN FOR  -- 보고서 작성
SELECT * FROM members
WHERE last_name = 'Harse';

SELECT  -- 보고서 조회
    PLAN_TABLE_OUTPUT 
FROM 
    TABLE(DBMS_XPLAN.DISPLAY());