데이터베이스 - 인덱스 객체

인덱스

데이터베이스에 데이터를 집어 넣으면 시간 순서대로 일단 정렬이 된다. 따라서 우리가 데이터를 찾을 때 다소 불편하다. 이를 극복하기 위해서 인덱스를 사용한다. 즉 인덱스는 검색 속도를 높이기 위해서 사용하는 스키마 객체이다.

인덱스를 사용하지 않을 때는 모든 데이터를 읽어 검색 조건에 따라 데이터를 선별한다. 하지만 인덱스를 사용하면 조건에 맞는 데이터만을 직접 접근하기 때문에 검색 효율을 높일 수 있다.

인덱스는 오라클 서버에 의해 자동 생성되기도 하지만, 사용자가 직접 생성할 수도 있다. 테이블에서 제약 조건으로 primary key 또는 unique를 생성할 경우 자동 생산된다. 그리고 이 경우 인덱스의 이름은 제약조건의 이름과 동일하다.

foreign key에 대해서는 자동생성되지 않지만 인덱스가 존재하면 조인할 때 검색속도가 향상되기 때문에 반드시 생성하도록 하자.

인덱스 생성

인덱스를 만들 때는 정렬 기법이 중요하다. 우리가 이미 알고 있는 오름차순, 내림차순은 데이터 간 접근속도 차이가 많이 난다. 하지만 검색 속도를 모든 데이터에 대해 가능하면 비슷한 속도로 접근할 수 있도록 해야한다.

이를 위해서 중간값 정렬이라는 것을 사용한다. 일종의 이진 트리 형식으로 중간값보다 큰 집합과 작은 집합으로 나누고, 또 나눈 데이터에서 이 작업을 반복한다. 이를 “balanced tree” 기법이라고도 부른다.

인덱스를 생성하면 인덱스는 기존 테이블로부터 데이터를 받아 Balanced tree에 넣는다. 우리가 데이터를 정렬하기 위해서 이름순, sal순 등으로 정렬할 때마다 실제 데이터를 뒤섞어놓는 것보다 필요한 칼럼을 뽑아서 트리에 넣는 것이 효율적이고 안전하기 때문이다.

테이블 스캔과 인덱스 스캔

보통 테이블에서 데이터를 찾는 것을 테이블 스캔, 인덱스에서 데이터를 찾는 것을 인덱스 스캔이라고 한다.

인덱스를 생성할 칼럼 선정

• 넓은 범위를 갖는 칼럼: 인덱스는 검색용이기 때문에 범위가 엄청 큰 데이터에 대해서 가져오는 것은 효율이 좋다. 따라서 테이블이 작은 경우에 대해서는 오히려 DML 작업을 수행하며 속도가 저하될 수 있다.

• null값이 많을 수록:  null값이 많은 칼럼에 대해서 null값은 인덱스에 포함되지 않기 때문에 인덱스의 크기를 줄이는데 유리하다.

• where절, 조인 조건에 사용되는 칼럼

• 전체 데이터의 약 2~4% 수준 이내

테이블이 작거나, 자주 변경되는 경우 오히려 효율이 떨어진다.

인덱스 확인

인덱스 확인은 user_indexes라는 데이터 딕셔너리와 user_ind_columns라는 데이터 딕셔너리를 이용한다. 각 각으로 부터 index이름이 같은 경우를 출력하도록 한다.

일단 emp 테이블에 대해서 인덱스 확인을 해보도록 하겠다. emp 테이블은 empno에 대해서 primary key설정이 되어 있다. 따라서 인덱스가 자동 생성된다. 이를 확인해보자.

다음처럼 pk_emp라는 이름의 인덱스가 emp에 대해서 적용되고 있다.

우리가 새롭게 테이블을 만들어 인덱스가 없는 경우에 똑같이 실행해보도록 하겠다. 이를 위해서 dept_i라는 테이블을 생성하였다. 그리고 인덱스 검색을 하였더니, 선택된 레코드가 없다고 출력된다.

이제 위의 테이블에 primary key를 추가하자. primary key는 인덱스를 자동으로 생성하기 때문에 인덱스가 검색될 것이다.

우리가 primary key로 정해준 deptno에 대해서 인덱스가 출력되었다.

인덱스 생성은 create문을 통해 이루어진다. 생성한 인덱스의 이름을 정하고, 어떤 테이블의 어떤 칼럼에 대해서 적용할 것인가를 정의힌다.

이번에는 새롭게 생성한 인덱스를 sql developer에 들어가서 확인해보겠다. localhost로 로그인하고 옆에 접속 탭에서 인덱스 폴더를 들어가보도록 하자. 그럼 다음처럼 우리가 방금 생성한 인덱스가 존재한다.

세부정보에 대해서 살펴볼 수도 있다.

함수 기반 인덱스 생성

우리가 이름 검색할 때를 생각해보자.

king이라는 직원에 대해서 emp 테이블로부터 가져올 때, king은 대문자로 저장되어 있기 때문에 우리가 lower함수를 통해서 이를 소문자화하였다.

이처럼 인덱스에서도 함수를 적용할 수 있을까?

인덱스도 함수를 기반으로 생성해보겠다.

검색에서 했듯이 인덱스에서도 함수를 사용하여 인덱스를 생성할 수 있음을 확인할 수 있다. 그리고 이렇게 생성된 인덱스를 함수 생성 인덱스라고 부른다.

실제로 developer를 확인하여 인덱스 타입이 어떻게 되는지를 살펴보도록 하겠다. 일단 다른 인덱스의 경우를 먼저 살펴보겠다. 아래 표시된 부분에서  index_type이 normal로 설정되어 있다.

반면에 함수 기반으로 생성한 인덱스의 경우에는 function-based라고 설정되어 있는 것을 확인할 수 있다.

인덱스 삭제

인덱스를 삭제하는 방법에 대해서도 알아보자. 인덱스를 삭제하기 위해서는 인덱스의 소유자이거나 drop any index 권한을 가지고 있어야 한다. 일단 우리가 지울 인덱스는 우리가 소유하고 있기 때문에 지워보도록 하자.