필터링된 인덱스

설명

필터링된 인덱스(filtered index)는 한 테이블에 대해 잘 정의된 부분 집합을 정렬하거나 찾거나 연산해야 할 때 사용되며, 전체 인덱스에서 조건에 부합하는 일부 인덱스만 사용되므로 부분 인덱스(partial index)라고도 한다. 필터링된 인덱스를 적용하여 질의를 처리하려면 USING INDEX 절에 해당 필터링된 인덱스를 반드시 명시해야 한다.

SELECT * FROM blogtopic WHERE postDate>'2010-01-01' USING INDEX my_filter_index;

구문

CREATE /* hints */ INDEX index_name

        ON table_name (col1, col2, ...) WHERE <filter_predicate>;

 

ALTER  /* hints */ INDEX index_name

        [ ON table_name (col1, col2, ...) [ WHERE <filter_predicate> ] ]

        REBUILD;

 

<filter_predicate> ::= <filter_predicate> AND <expression> | <expression>

• <filter_predicate> : 칼럼과 상수 간 비교 조건. 조건이 여러 개인 경우 AND로 연결된 경우에만 필터가 될 수 있다. 필터 조건으로 CUBRID에서 지원하는 대부분의 연산자와 함수가 포함될 수 있다. 그러나 현재 날짜/시간을 출력하는 날짜/시간 함수(예: SYS_DATETIME), 랜덤 함수(예: RAND())와 같이 같은 입력에 대해 다른 결과를 출력하는 함수는 허용되지 않는다.

예제

다음은 버그/이슈를 유지하는 버그 트래킹 시스템의 예이다. 일정 기간의 개발 활동 이후 bugs 테이블에는 버그들이 기록되어 있는데, 이들 대부분은 오래 전에 종료된 상태이다. 버그 트래킹 시스템은 여전히 열린(open) 상태의 새로운 버그를 찾기 위해 해당 테이블에 질의를 한다. 이 경우 버그 테이블의 인덱스는 닫힌(closed) 버그의 레코드들에 대해 알 필요가 없다. 이런 경우 필터링된 인덱스는 열린 버그만 인덱싱하는 것을 허용한다.

CREATE TABLE bugs

(

        bugID BIGINT NOT NULL,

        CreationDate TIMESTAMP,

        Author VARCHAR(255),

        Subject VARCHAR(255),

        Description VARCHAR(255),

        CurrentStatus INTEGER,

        Closed SMALLINT

);

열린 상태의 버그만을 위한 인덱스는 다음 문장으로 생성될 수 있다.

CREATE INDEX idx_open_bugs ON bugs(bugID) WHERE Closed = 0;

열린 상태의 버그에만 관심있는 질의 처리를 위해 해당 인덱스를 USING INDEX 절에 명시하면, 필터링된 인덱스를 통하여 더 적은 인덱스 페이지를 접근하여 질의 결과를 생성할 수 있게 된다.

SELECT * FROM bugs

WHERE Author = 'madden' AND Subject LIKE '%fopen%' AND Closed = 0;

USING INDEX idx_open_bugs;

 

SELECT * FROM bugs

WHERE CreationDate > CURRENT_DATE - 10 AND Closed = 0;

USING INDEX idx_open_bugs;

주의 사항

필터링된 인덱스 생성 조건과 질의 조건이 부합되지 않음에도 불구하고 USING INDEX로 인덱스를 명시하여 질의를 수행하면 잘못된 질의 결과를 출력할 수 있음에 주의한다.

제약 사항

필터링된 인덱스는 일반 인덱스만 허용된다. 예를 들어, 필터링된 유일한(unique) 인덱스는 허용되지 않는다. 다음은 필터 조건으로 허용하지 않는 경우이다.

• 날짜/시간 함수 또는 랜덤 함수와 같이 입력이 같은데 결과가 매번 다른 함수

• CREATE INDEX idx ON bugs(creationdate) WHERE creationdate > SYS_DATETIME;

   

  ERROR: before ' ; '

  'sys_datetime ' is not allowed in a filter expression for index.

   

  CREATE INDEX idx ON bugs(bugID) WHERE bugID > RAND();

   

  ERROR: before ' ; '

  'rand ' is not allowed in a filter expression for index.

• OR 연산자를 사용하는 경우

• CREATE INDEX IDX ON bugs(bugID) WHERE bugID > 10 OR bugID = 3;

   

  In line 1, column 62,

   

  ERROR: before ' ; '

  ' or ' is not allowed in a filter expression for index.

• INCR(), DECR() 함수와 같이 테이블의 데이터를 수정하는 함수를 포함한 경우
• 시리얼 관련 함수와 의사 컬럼을 포함한 경우
• MIN(), MAX(), STDDEV() 등 집계 함수를 포함한 경우
• 인덱스를 생성할 수 없는 타입을 사용하는 함수
  • SET 타입을 인자로 받는 연산자와 함수
  • LOB 파일을 생성하는 함수(CHAR_TO_BLOB, CHAR_TO_CLOB, BIT_TO_BLOB, BLOB_FROM_FILE, CLOB_FROM_FILE)
• IS NULL 연산자는 인덱스를 구성하는 칼럼들 중 적어도 하나가 NULL이 아닐 경우에만 사용 가능

• CREATE TABLE t (a INT, b INT);

  Current transaction has been committed.

   

  -- IS NULL cannot be used with expressions

  CREATE INDEX idx ON t (a) WHERE (not a) IS NULL;

  ERROR: before ' ; '

  Invalid filter expression (( not t.a<>0) is null ) for index.

   

  CREATE INDEX idx ON t (a) WHERE (a+1) IS NULL;

  ERROR: before ' ; '

  Invalid filter expression ((t.a+1) is null ) for index.

   

  -- At least one attribute must not be used with IS NULL

  CREATE INDEX idx ON t(a,b) WHERE a IS NULL ;

  ERROR: before '  ; '

  Invalid filter expression (t.a is null ) for index.

   

  CREATE INDEX idx ON t(a,b) WHERE a IS NULL and b IS NULL;

  ERROR: before ' ; '

  Invalid filter expression (t.a is null  and t.b is null ) for index.

   

  CREATE INDEX idx ON t(a,b) WHERE a IS NULL and b IS NOT NULL;

  Current transaction has been committed.

• 필터링된 인덱스에 대한 인덱스 스킵 스캔(ISS)은 지원되지 않는다.
• 필터링된 인덱스에서 사용되는 조건 문자열의 길이는 128자로 제한한다.

• CREATE TABLE t(VeryLongColumnNameOfTypeInteger INT);

  1 command(s) successfully processed.

   

  CREATE INDEX idx ON t(VeryLongColumnNameOfTypeInteger) WHERE VeryLongColumnNameOfTypeInteger > 3 AND VeryLongColumnNameOfTypeInteger < 10 AND sqrt(VeryLongColumnNameOfTypeInteger) < 3 AND SQRT(VeryLongColumnNameOfTypeInteger) < 10;

  ERROR: before ' ; '

  The maximum length of filter predicate string must be 128.