[SQL 기초 - 15장] 오라클 SQL 인덱스(2) (feat. 실행계획)

 

안녕하세요! 

오늘은 지난 시간에 이어서 인덱스에 대해서 설명해보려고 합니다.

인덱스

인덱스의 역할

지난번에 제가 인덱스를 설명할 때 '책의 목차와 비슷한 것이다' 라고 설명했던것 같은데요

그 말은 인덱스를 사용하면 데이터를 찾을 때 좀 더 빠르게 찾을 수 있다는 것입니다.

​

우리는 데이터를 찾을 때 테이블을 조회하고 하나씩 찾아봅니다.

하지만 그 테이블의 데이터들은 ORDER BY 를 쓰지 않는 이상

데이터의 순서가 뒤죽박죽 이겠죠.

​

따라서 데이터를 조회할 때 빠르게 조회하려면 정렬이 되어 있어야 합니다.

지난 글에서 인덱스의 특징들을 설명을 했었죠?

그 중에서 인덱스는 '테이블의 일부 컬럼을 기준으로 데이터를 정렬한 객체'

라고 했는데요, 이 특징덕분에 더욱 빠르게 데이터를 찾을 수 있습니다.

​​

 

그럼 인덱스를 이용해서 어떻게 데이터를 찾는가 하는 질문이 생길 수 있습니다.

오라클에는 rowid 라는 특수 컬럼이 있습니다.

행의 주소를 알려주는 컬럼이죠.

​

지난 글에서도 한 번 언급이 됐었습니다.

row와 그 주소인 rowid 가 있었죠.

​

만약 우리가 emp 테이블에서 사번이 7698 인 사원의 데이터를 찾고

그 데이터의 주소를 알아보려고 한다면

 

위와 같이 쿼리를 작성할 수 있고 결과도 볼 수 있습니다.

rowid 라고 컬럼이 하나 더 나왔죠?

사번이 7698인 사원의 데이터가 있는 주소를 나타냅니다.

​

이 처럼 인덱스에는 인덱스를 생성할 때

구성하려고 하는 컬럼의 정보과 그 정보들의 rowid 가 함께 저장됩니다.​

인덱스 종류와 생성방법

인덱스에는 UNIQUE 인덱스와 NON-UNIQUE 인덱스(일반 인덱스)가 있습니다.

UNIQUE 인덱스는 쉽게 말해서

PRIMARY KEY + NOT NULL 이라고 생각하시면 됩니다.

그리고 PRIMARY KEY 를 생성시 오라클은 자동으로 UNIQUE 인덱스를 생성해줍니다.

하지만 우리가 사용할 인덱스는 대체로 NON-UNIQUE 인덱스에요 ㅎ

차이점만 알아두세요!

​

그러면 인덱스를 생성해 보겠습니다.

문법은

위와 같이 작성하면 되겠습니다.

만약 emp 테이블의 empno 컬럼을 인덱스로 구성하려고 한다면

이렇게 작성하면 되겠습니다.

인덱스를 만들고 나서 emp 테이블 탭에들어가서 '인덱스'를 눌러보시면

만든 인덱스가 생성된 것을 볼 수 있을겁니다.

​

방금 만든 인덱스는 위와 같은 정보가 저장되어 있을거에요.

하지만 인덱스를 테이블을 조회하는 쿼리처럼 작성해서 조회할 수는 없기 때문에

위 처럼 작성해서 확인을 하는 것입니다.

​

이제 인덱스는 emp 테이블의 empno 데이터에 대한 주소를 가지고 있는 것입니다.

그 주소를 알고 있으니 더 빠르게 접근할 수 있게됩니다.

인덱스가 없다면 테이블을 처음부터 끝까지 찾아보고

쿼리가 종료 되겠죠?

실행계획​

실행계획 작성과 인덱스의 효율

그럼 인덱스가 있고 없고의 차이를

실행계획을 통해서 보여드리도록 하겠습니다.

위는 실행계획을 보기 위한 쿼리입니다.

먼저 EXPLAIN PLAN FOR 아래에

조회할 쿼리를 작성하고 실행을 시켜준 다음에

두번째 쿼리를 작성하고 실행 시켜 줍니다.​

위는 인덱스가 없을때와 있을때의 실행계획을 조회해 본 것입니다.

그 전에 먼저 실행계획을 읽는 방법부터 알려 드겠습니다.

실행계획을 읽는 방법이라하면, 조회를 하는데

오라클이 어떻게 접근할 지 에대한 순서(?) 를 읽는 방법 입니다.

​

먼저 Operation 을 보시면 들여쓰기가 되어 있습니다.

들여쓰기가 된 노드들은 그 위 노드의 자식 노드라고 합니다.

실행계획은 위에서 아래로 읽되, 자식노드부터 읽습니다.

즉 위에에서 인덱스가 있을 때의 실행계획을 보면

2 - 1 - 0 순서로 읽으면 됩니다.

만약 3번 노드가 있을 때 들여쓰기의 정도가 1번 노드와 같다고 하면

2 - 1 - 3 - 0 이 되겠습니다.

따라서 노드들의 들여쓰기 정도(?)를 보고 순서를 알 수 있습니다.

​

인덱스가 없을 때의 실행계획은

TABLE ACCESS FULL 조회를 하려고 합니다.

즉, 참조할 어떤 것도 없기 때문에 테이블 전체를 조회하는 것입니다.

​

인덱스가 있을 때의 실행계획을 보시면

INDEX RANGE SCAN 을 하게되죠?

참조하는 것은 아까 만들었던 ind_nu_emp_01 이라고 뜨네요!

그리고 나서 emp 테이블로 가서 INDEX ROWID 에 접근을 합니다.

이렇게 되면 emp 테이블을 전체 다 볼 필요가 없이

ROWID 로만 찾을 수 있어 속도가 단축 되는 것이죠~!

설명을 어렵게 했나요..?

 

어렵지만 계속 보다보면 이해가 되실 겁니다!

​

그리고 실행계획의 우측에 row나 cost, time 이런 것들도 보시면

음.. 낮으면 더 좋다(?) 라고 생각하셔도 무방할 것 같아요

아직은 조회하는 데이터가 많지 않지만

나중에 실무에서는 데이터가 많아지면서

차이가 많이 나게 됩니다!!

그리고 마지막으로 인덱스를 구성 할 때

컬럼을 적잖아요?

그 컬럼을 적을 때 복수개를 적을 수 있는데,

그 복수개의 컬럼들의 순서에 따라서도 영향을 미친다는 점도

기억하고 인덱스를 생성해주세요!​​

오늘은 이정도로 마치겠습니다.

설명을 잘 했는지 모르겠네요..

궁금한 점이 있다면 댓글을 달아주세요!

오늘도 끝까지 봐주셔서 감사합니다!

다음에 또 봐요!