This forum is currently locked. You can't register or post questions at this time. (read more)

Function based index on multiple columns

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

patrick196901
Member
Posts: 13
Joined: Thu Oct 27, 2011 10:53 am

Function based index on multiple columns

Postby patrick196901 » Thu Feb 07, 2013 4:22 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Function based index on multiple columns

Postby Tim... » Thu Feb 07, 2013 6:48 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

patrick196901
Member
Posts: 13
Joined: Thu Oct 27, 2011 10:53 am

Re: Function based index on multiple columns

Postby patrick196901 » Fri Feb 08, 2013 2:13 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Function based index on multiple columns

Postby Tim... » Fri Feb 08, 2013 2:18 pm

No worries. :)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests

cron