인덱스의 유형과 특징(2. 함수기반 인덱스)

 

저번 글에서는 인덱스란 무엇인지와 종류, 그리고 B-tree인덱스에 대해서 간단하게 살펴보았다. 이 글에서는 이어서 함수 기반 인덱스에 대해서 살펴보겠다. 함수기반 인덱스는 말 그대로 함수를 기반으로 하는 인덱스이다. 인덱스 값을 단순히 컬럼이 아니라 컬럼을 이용한 연산으로 정한다는 것이다. 아래는 간단한 예시이다. 

 

CREATE INDEX prod_idx1 (cnt * price);

 

 

위와 같은 인덱스를 생성했을 때 아래와 같은 조건문으로 검색을 한다면 이미 인덱스가 만들어져 있으므로 더 빠른 성능을 보여줄 것이다.

 

 

SELECT * FROM prod WHERE cnt * price = 2000;

 

 

함수 기반형 인덱스는 위에서의 경우 인덱스 키로 cnt * price의 값이 정해져 있고 각 값에 해당하는 ROWID가 저장되는 것이다. 

 

함수 기반 인덱스는 함수느 수식으로 계산된 결과에 대해 B-Tree 인덱스나 비트맵 인덱스를 생성할 수 있다. 여기서 사용할 수 있는 함수는 산술식 사용자 지정 함수, SQL의 제공 함수, 패키지 등이 가능하지만  SUM, AVG 등의 그룹 함수는 사용할 수 없다. 그 이유는 이러한 함수들은 테이블의 로우 단위가 아닌 새롭게 생성된 논리적 로우 단위로 적용되기 때문이다. 인덱스는 구체적인 단위 로우를 가리키는 일종의 포인터이므로 논리적으로 생성된 로우에 대해서는 적용할 수 없는 것이다. 

 

그렇다면 어떤 경우들에 함수 기반 인덱스가 사용될까? 생각해보면 어떤 경우에서든 사용 가능 하다. 그리고 예전에 테이블 설계 문제로 발생하는 문제로 이 방식으로 해결할 수 있는데 하나하나씩 예시를 살펴보겠다.

 

 

먼저 컬럼의 중간 부분을 검색하는 경우이다. 컬럼의 일부 값을 검색 조건으로 활용할 때 선행 값을 조건에 부여할 수 없기에 인덱스를 사용할 수 없거나 불필요한 범위를 액세스해야 한다. 그럴 경우는 아래와 같은 인덱스를 만들면 해결이 된다. 

 

 

CREATE INDEX from_loc_idx ON orders (SUBSTR(ship_id, 5, 3));
CREATE INDEX repair_loc_idx ON orders (SUBSTR(ship_id, 5, 3), ord_date);

 

 

그다음 경우는 조인 연결고리 컬럼이 대응하지 않는 경우이다. 

아래의 코드를 보자

 

...
FROM item_group , item y
WHERE x.class1 || x.class1 || x.class3 = y.group_cd

 

 

위와 같은 경우는 어느 한쪽도 인덱스를 사용할 수 없어 조인에 나쁜 영향을 미칠 수 있다. 그렇기에 이러한 경우는 아래와 같이 함수 기반 인덱스로 해결할 수 있다. 

 

CREATE INDEX group_cd_idx ON item_group ( class1 || class2 || class3);

 

 

그다음 경우는 일자 컬럼이 분할된 경우이다. 흔히 날짜컬럼을 년, 월, 일 이렇게 3개의 컬럼으로 나눠서 관리하는 경우가 있는데 이럴 경우 20220402 같은 형태의 날짜로 한 번에 비교를 할 때 하나하나 비교하거나 년, 월, 일 을 다 붙여서 비교할 수밖에 없는데 이럴 경우 인덱스를 사용하지 못한다. 하지만 아래와 같이 함수 기반 인덱스로 해결할 수가 있다. 

 

CREATE INDEX sal_date_idx ON sales CONCAT(sal_yyyy, sal_mm, sal_dd);

 

 

그다음 경우는 데이터 타입이 다른 조인 컬럼의 탐색이다. 어떤 테이블의 기본키와 이를 상속받는 외부 키의 데이터 타입이 일치하지 않는 경우가 일어날 때 아래와 같이 인덱스에서 저장할 때에는 데이터 타입을 변경하여 인덱스를 생성하면 된다. 여기서 헷갈리면 안 되는 것은 인덱스에서만 타입이 다르게 저장되는 것이지 실제 테이블의 컬럼의 타입이 변경되는 것은 아니다. 그리고 처음에 테이블을 생성할 때 타입 지정을 제대로 했다면 굳이 발생하지 않았을 문제이다. 

 

CREATE INDEX deptno_idx ON emp (To_NUMBER(deptno));

 

 

다음의 경우는 조인 컬럼이 경우에 따라 달라지는 문제이다. 아래의 예시 코드를 봐보자.

 

...
FROM sales s, departments d
WHERE d.deptno = (CASE WHEN s.sal_type = 1 THEN S.sal_dept ELSE agent_no END)
  AND d.location = 'PUSAN'
...

 

SALES 테이블이 먼저 수행되는 Nested loops 조인이라면 문제가 되지 않지만 위의 예시 코드처럼 DEPARTMENTS 테이블이 먼저 수행되어야 하는 경우에는 문제가 된다.  

이 경우도 아래처럼 함수기반 인덱스를 사용하면 문제를  해결할 수 있다. 

 

CREATE NDEX deptno_idx ON sales (CASE WHEN sal_type = 1 THEN sal_dept ELSE agent_no END);

 

 

다음 경우는 부모 테이블의 컬럼과 결합한 인덱스를 생성하는 경우이다.  아래의 예시 코드를 봐보자. 

 

...
FROM movement x, movement_trans y
WHERE x.mov_order = y.mov_order
  AND x.deptno = '12310'
  AND y.mov_date like '2005125'

 

 

이 쿼리가 Neste Loops 조인으로 수행딘다고 했을 때 처리 범위가 넓은 deptnp = '12310'이 처리 주관이 된다면 mov_date like '200512%'는 체크 기능의 역할만 하게 된다. 물론 반대 방향으로 처리돼도 문제는 마찬가지다. 여기서 우리는 이 둘을 함께 처리한다면 더 좋은 수행 속도를 얻을 수 있을 것이다.  

이 문제 또한 함수기반 인덱스를 만들어서 해결할 수 있다. 아래의 코드를 한번 봐보자. 

 

CREATE or REPLACE FUNCTION get_deptno
  ( v_mov_order in number)
  RETURN varchar2 DETERMINISTIC IS
  REL_VAL varchar2(5);
BEGIN
  SELECT deptno into RET_VAL
  FROM movement
  WHERE mov_order = v_mov_order;
  RETURN REL_VAL;
END get_deptno;

CREATE INDEX dept_date_idx ON movement_trans (get_deptno(mov_order), mov_date);

 

 

인덱스에서 하나의 테이블을 지정하고 조인되는 컬럼을 매개변수로서 조인되는 다른 테이블의 값을 가져오게 하는 사용자 지정 함수를 이용한 것이다. 그렇기에 x.deptno와 y.mov_date로 이루어진 인덱스가 생성되어 더 빠른 검색을 이룰 수 있는 것이다.

 

그다음 경우는 NULL 값을 치환하여 검색하는 경우이다. 일반적으로 NULL값을 넣었지만 이를 인덱스에 다른 값으로 저장하여 찾을 때에는 그 저장한 값으로 찾을 수 있게 하는 것이다. 예를 들면 날짜를  입력하지 않으면 테이블에는 NULL이 들어가지만 검색할 때에는 미입력 된 날짜는  마지막 날로 생각되게 하여 찾을 수 있게 하고 싶은 경우이다. 예시 코드는 아래와 같다. 

 

CREATE INDEX end_date_idx ON account_history
      (NVL(end_date, '20241231'));

 

 

이 밖에도 복잡한 계산결과의 검색을 위해서, 기간, 컬럼의 길이 검색, 오브젝트 타입의 검색, 접두사를 채워서 검색, 대 소문자나 공백이 혼재된 컬럼의 검색 등 매우 다양한 경우에서 사용될 수가 있다. 

 

CREATE INDEX ename_upper_ix ON employees (UPPER(REPLACE(ename, ' '));

 

참고

새로쓴 대용량 데이터베이스 솔루션 - 이화식