Function based index on multiple columns

[patrick196901]

Hi Tim,

I am going to ask you something weird, but I really have to, sorry if you find this post crazy. Developers in my company want to use FUNCTION BASED INDEXES in some queries for one application, so I read some documentations and sent them links about this feature. Then they came back to me with a query and they created a function-based index on all the columns in the WHERE clause like below.

The query:

SELECT DISTINCT DCF_DOCUMENT_ID
FROM DOCUMENT_FIELD
WHERE DCF_DOCUMENT_ID = 123456789
AND DCF_TYPE = 18018
AND UPPER(DCF_VALUE) LIKE UPPER('%DHA%');

The index:

CREATE INDEX IDX_DOCUMENT_FIELD_004 ON DOCUMENT_FIELD (DCF_DOCUMENT_ID, DCF_TYPE, UPPER(DCF_VALUE)) TABLESPACE TBS_INDX;

Then after reading documents about function-based indexes, I came back to them and told them that function-based indexes couldn't be created like composite indexes with more than 1 column in the index. They replied to me that it was possible and it would work. But first time I see that. I have looked in the Oracle documentation, and also in one of your page and I haven't found anything about function-based indexes created on more than 1 column like above.

Could you tell me if the creation of this index is correct? If so do you have some link that could show how to create them correctly?

Thanks in advance

Regards

Patrick

[Tim...]

Hi.

I've added and extra section on my (very old) article to show this in operation.

http://www.oracle-base.com/articles/8i/ ... ed-columns

There is nothing wrong with doing this, just remember that an update to any of those columns will require index maintenance, which will involve running the function again. As a result, the incidence of index maintenance may be increased, depending on how you use the table.

Cheers

Tim...

[patrick196901]

Hi Tim,

I have read the link you put in your answer, and I will create the index in UAT soon.

Thanks for your help on this one.

Regards

Patrick

[Tim...]

No worries.