16장 오라클 인덱스의 의미 및 총 정리 (조회 속도 비교,인덱스 정의, 사용 방법)

주니어 기초 코딩공부/Database 기초

16장 오라클 인덱스의 의미 및 총 정리 (조회 속도 비교,인덱스 정의, 사용 방법)

jju_developer 2022. 12. 20. 20:34

728x90

안녕하세요 jju_developer입니다.

오늘은 오라클의 인덱스의 의미 및 총정리 (조회 속도 비교, 인덱스 정의, 사용 방법)에 대해 소개하는 시간을 갖겠습니다.

✔ 목차

인덱스란?
인덱스 정보 조회
조회 속도 비교하기
인덱스 생성 및 제거하기
인덱스를 사용해야 하는 경우 판단하기

✔ 인덱스의 개요?

데이터베이스를 빠르게 찾기 위한 하나의 자료구조입니다.

우리가 데이터를 찾아가는 방법 중 빠른 방법에 대한 여러 가지 알고리즘이 있습니다.

인덱스란 SQL 명령문의 처리 속도를 향상하기 위해서 칼럼에 대해서 생성하는 오라클 객체입니다.

인덱스 장점	인덱스 단점
• 검색 속도가 빨라진다. • 시스템에 부하를 줄여서 시스템의 전체 성능을 향상시킵니다. • 데이터베이스의 모든 블록을 다 읽지 않고, 목표 데이터가 있는 블록 주소를 찾아 당해 블록만을 메모리에 복사하여 작업을 진행합니다.	• 인덱스를 위한 추가 공간이 필요하다. • 인덱스가 많거나 / 있다고 하여 무조건 쿼리 속도가 빨라지지 않습니다. - 테이블과 연관된 인덱스가 많을수록 오라클 서버 부담 증가

데이터가 위치한 장소의 정보를 가진 일종의 주소록입니다.
인덱스는 데이터의 주소(ROWID)를 가지고 있습니다.

결과적으로 인덱스는 데이터 검색을 빠르게 하기 위한 자료구조로 만드는 것 을 의미합니다~!

✔ 인덱스 정보 조회하기

기본적으로 오라클에서 제공되는 EMP와 DEPT의 테이블은

PK로 만들어진 DEPTNO와 EMPNO가 있기 때문에

이 둘의 인덱스가 있습니다.

✔ 인덱스 조회 속도 비교

인덱스가 아닌 칼럼으로 검색해보겠습니다.

INSERT INTO EMP01

SELECT * FROM EMP01;

자기 자신을 EMP01에 여러 번 더블 클릭하여 넣습니다.

여러 번 클릭하여 데이터가 많아지도록 합니다.

테이블 자체 복사를 여러 번 반복해서 상당히 많은 양의 행을 생성한다.

이 정도로 많이 테이블을 생성 후,

SET TIMING ON --타이밍 설정
SELECT DISTINCT EMPNO, ENAME
FROM EMP01
WHERE ENAME = 'SES'; --'SYS'테이블 검색 속도 확인하기

SET TIMING ON 을하여,

방금 넣은 데이터가 몇 초 정도 걸리는지 확인합니다.

그렇다면 인덱스를 설정하면 어떻게 될까요?

인덱스 설정하기

CREATE INDEX IDX_EMP01_ENAME
ON EMP01(ENAME);

내가 만든 테이블에 인덱스를 CREATE 하는 것입니다.

이미 많은 테이블이 있기 때문에 인덱스 자체를 생성할 때에 시간이 어느 정도 소요됩니다.

이후에 속도를 동일하게 비교를 하면

속도 차이가 나는 것을 볼 수 있습니다.

이처럼 인덱스를 사용하는 경우를 잘 판단하여야 합니다.

✔ 인덱스 생성 및 제거하기

-- 인덱스 생성하기
CREATE INDEX IDX_EMP01_ENAME
ON EMP01(ENAME);
SELECT DISTINCT EMPNO, ENAME
FROM EMP01
WHERE ENAME='SYJ';

--인덱스 있는지 확인
SELECT INDEX_NAME, TABLE_NAME , COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME IN('EMP01');

--인덱스 제거하기!!
DROP INDEX IDX_EMP01_ENAME;

인덱스 생성 시,

CREATE INDEX를 하고 나서 뒤에는 인덱스 이름_테이블 이름_칼럼 이름 하면 이게 이제 인덱스 이름입니다.

ON 테이블 (칼럼 이름);

ON 뒤에 오는 테이블과 칼럼 이름은 제가 지정한 인덱스 이름과 동인 한 테이블과 칼럼 이름을 넣어주면 됩니다~!

✔ 인덱스를 사용해야 하는 경우 판단하기

no	인덱스를 사용해야 하는 경우	인덱스를 사용하지 말아야 하는 경우
1	테이블에 행의 수가 많을 때	테이블에 행의 수가 적을 때
2	WHERE 문에 해당 컬럼이 많이 사용될 때 (where문을 쓴다는 것은 조회를 하기 위한 조건이기 때문입니다.)	WHERE 문에 해당 컬럼이 자주 사용되지 않을 때
3	검색 결과가 전체 데이터의 2%~4% 정도 일 때	검색 결과가 전체 데이터의 10%~15% 이상 일 때
4	JOIN에 자주 사용되는 컬럼이나 NULL을 포함하는 컬럼이 많은 경우	테이블에 DML 작업이 많은 경우 즉, 입력 수정 삭제 등이 자주 일어 날 때 -> 데이터 수정이 잦은 경우에는 인덱스를 활용하지 않는 것이 좋습니다.

 다음과 같은 조건에서 사원 테이블의 부서 번호(DEPTNO)에 인덱스를 거는 것이 좋을까요?
• 테이블에 전체 행의 수는 10000 건이다.
• 위의 쿼리문을 전체 쿼리문들 중에서 95% 사용된다.
• 쿼리 문의 결과로 구해지는 행은 10건 정도이다.

=> 조건을 위 표를 비추어보고 판단해 보면 부서 번호(DEPTNO) 칼럼을 인덱스로 사용하기에 알맞다는 결론이 납니다.

이처럼 데이터를 보고 개발자가 판단하여 인덱스를 활용하셔야 합니다.

여기까지 인덱스의 기초 설명을 마치겠습니다.

그럼 오늘도 수고하셨습니다~! :)

728x90