How to Implement a Filtered Index in Oracle

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.

Previously on my blog post (Sometimes a histogram existence may mislead to a suboptimal plan), I analyzed a poorly performing query in detail and detected that sometimes a histogram existence may mislead to a suboptimal plan. I have listed some possible solutions. But what i applied as a solution was to emulate a partial/filtered index in Oracle. 

I know partial indexes concept from Postgresql database, it is also available on MS SQL and some others. 

What is Partial Index?

...

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate. Partial indexes are a specialized feature, but there are several situations in which they are useful.

One major reason for using a partial index is to avoid indexing common values. Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway, there is no point in keeping those rows in the index at all. This reduces the size of the index, which will speed up those queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases.  

... More of it available on Postgresql Official Documentation.

Basically, Index is constructed over a subsection of a table according to the query filter predicates.

In Oracle, the term "Partial Index" differs a little bit. My friend Meftun has a nice post series Have you ever need to index only SOME partition of a table? about it.

I will implement a filtered index solution by creating a functional index for the problematic query i mentioned in previous post and show how a real performance boost gained.

	SQL> CREATE INDEX ACCS.IN_ACCDOC_DOCTYPDCDTRLTDDCRLTD
	ON ACCS.T_ACC_DOC
	(
	CASE
	WHEN ( DOC_TYPE IN ('X11',
	'X21',
	'X71',
	'X81')
	AND DOC_REC_DATE >= TO_DATE ('01.01.2014', 'DD.MM.YYYY')
	AND RELATED_DOC IS NULL
	AND RELATED_DOC_ID IS NULL)
	THEN
	'BJK'
	ELSE
	NULL
	END)
	TABLESPACE USERS_IDX
	ONLINE
	PARALLEL 64;
	SQL> ALTER INDEX ACCS.IN_ACCDOC_DOCTYPDCDTRLTDDCRLTD NOPARALLEL;
	SQL> SELECT D.RECV_ACC_ID
	FROM S_ACC_DOC D
	WHERE CASE
	WHEN ( DOC_TYPE IN ('X11',
	'X21',
	'X71',
	'X81')
	AND DOC_REC_DATE >= TO_DATE ('01.01.2014', 'DD.MM.YYYY')
	AND RELATED_DOC IS NULL
	AND RELATED_DOC_ID IS NULL)
	THEN
	'BJK'
	ELSE
	NULL
	END = 'BJK'
	Execution Plan
	----------------------------------------------------------
	SQL_ID: 7dmtqz284r3kr
	Plan hash value: 583430513
	----------------------------------------------------------------------------------------------------------------------
	| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
	----------------------------------------------------------------------------------------------------------------------
	| 0 | SELECT STATEMENT | | 87775 | 942K| 23527 (1)| 00:00:01 |
	| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_ACC_DOC | 87775 | 942K| 23527 (1)| 00:00:01 |
	|* 2 | INDEX RANGE SCAN | IN_ACCDOC_DOCTYPDCDTRLTDDCRLTD | 87775 | | 185 (1)| 00:00:01 |
	----------------------------------------------------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	---------------------------------------------------
	2 - access(CASE WHEN (("DOC_TYPE"='X11' OR "DOC_TYPE"='X21' OR "DOC_TYPE"='X71' OR
	"DOC_TYPE"='X81') AND "DOC_REC_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
	AND "RELATED_DOC" IS NULL AND "RELATED_DOC_ID" IS NULL) THEN 'BJK' ELSE NULL END ='BJK')
	Statistics
	----------------------------------------------------------
	9 recursive calls
	5 db block gets
	33662 consistent gets
	0 physical reads
	1836 redo size
	1627091 bytes sent via SQL*Net to client
	65128 bytes received via SQL*Net from client
	5853 SQL*Net roundtrips to/from client
	0 sorts (memory)
	0 sorts (disk)
	87776 rows processed

view raw create_filtered_index.sql hosted with ❤ by GitHub

When Full table scan is done, total block gets was 1352052. When composite index (IN_ACCDOC_DCTYPRLTDDDTRLTD)  is used, 90813 block gets done. But when the partial/filtered index solution applied, total block gets decreased heavily to 33662. %98 performance boost observed. Its size is only 2MB. 1000 times smaller than the composite index applied on my previous post.

Hope it helps.