Filtered Index

Description

The filtered index is used to sort, search, or operate a well-defined partials set for one table. It is called the partial index since only some of indexes that satisfy the condition are used. To guarantee using the filtered indexes, the USING INDEX syntax must be added as follows:

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

Syntax

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>: Condition to compare the column and the constant. When there are several conditions, filtering is available only when they are connected by using AND. The filter conditions can include most of the operators and functions supported by CUBRID. However, the date/time function that shows the current date/time (ex: SYS_DATETIME) or random functions (ex: RAND()), which outputs different results for one input are not allowed.

Example

The following example shows a bug tracking system that maintains bugs/issues. After a specified period of development, the bugs table records bugs. Most of the bugs have already been closed. The bug tracking system makes queries on the table to find new open bugs. In this case, the indexes on the bug table do not need to know the records on closed bugs. Then the filtered indexes allow indexing of open bugs only.

CREATE TABLE bugs

(

        bugID BIGINT NOT NULL,

        CreationDate TIMESTAMP,

        Author VARCHAR(255),

        Subject VARCHAR(255),

        Description VARCHAR(255),

        CurrentStatus INTEGER,

        Closed SMALLINT

);

Indexes for open bugs can be created by using the following sentence:

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

To process queries that are interested in open bugs, specify the index in the USING INDEX statement. It will allow to create query results by accessing less index pages through filtered indexes.

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;

Remark

If you execute queries by specifying indexes with USING INDEX, you may have incorrect query results as output even though the conditions of creating filtered indexes does not meet the query conditions.

Constraints

Only generic indexes are allowed as filtered indexes. For example, the filtered unique index is not allowed. The following cases are not allowed as filtering conditions.

• Functions, which output different results with the same input, such as date/time function or random function

• 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.

• When the OR operator is used

• csql> 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() function and DECR() function
• Serial-related functions
• Aggregate functions such as MIN(), MAX(), and STDDEV()
• Conditions for types where indexes cannot be created
• • The operators and functions where parameter is the SET type
  • IS NULL operator can be used only when at least one column among the columns of the index is not NULL.

• The IS NULL operator can be used only when at least one column of an index is not 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.

• Index Skip Scan (ISS) is not allowed for the filtered indexes.
• The length of condition string used for the filtered index is limited to 128 characters.

• 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.