Home » SQL & PL/SQL » SQL & PL/SQL » Function Based Index
Function Based Index [message #216340] Fri, 26 January 2007 11:16 Go to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi Friends,
I am very thankful to all of you for helping me in the past.
I have a small problem in My query.
My tables contain these columns
.
.
strFirstname
strMiddleName
strLastName
.
.

Now in my package i am concating these 3 columns and searching for my string value.

SELECT * FROM RES_NAME
CCN WHERE
UPPER(DECODE(ccn.strFirstname, NULL,DECODE(ccn.strMiddleName, NULL, ccn.strLastName, ccn.strMiddleName ||' '|| ccn.strlastname),ccn.strFirstname ||' '||
DECODE(ccn.strMiddleName, NULL, ccn.strLastName, ccn.strMiddleName ||' '|| ccn.strlastname )))LIKE '%' || UPPER(TRIM('Peter')) || '%'

Now in Production database it is taking lot of time. Can anybody suggest me what can be the solution.
Will Function Based Index wil help me out?

Thank you.
Amit.

I have also created a function based index

CREATE INDEX IDX_FUN_CCN ON RES_NAME CCN
(UPPER(DECODE(ccn.strFirstname, NULL,DECODE(ccn.strMiddleName, NULL, ccn.strLastName, ccn.strMiddleName ||' '|| ccn.strlastname),ccn.strFirstname ||' '||
DECODE(ccn.strMiddleName, NULL, ccn.strLastName, ccn.strMiddleName ||' '|| ccn.strlastname ))))

But when i use LIKE clause it does a full table scan.
Pls help me.

[Updated on: Fri, 26 January 2007 12:09]

Report message to a moderator

Re: Function Based Index [message #216349 is a reply to message #216340] Fri, 26 January 2007 14:26 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try:
grant query rewrite to public;


Also see query_rewrite_integrity=trusted
Re: Function Based Index [message #216454 is a reply to message #216340] Sun, 28 January 2007 04:34 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

The condition you are using in your query is NOT indexable,
so function based index will not help.

Try following:
1. Create 3 function based indexes:
CREATE INDEX ... ON ( UPPER(strFirstname));
CREATE INDEX ... ON ( UPPER(strMiddleName));
CREATE INDEX ... ON ( UPPER(strLastName));

2. Rewrite your query:
SELECT * FROM RES_NAME CCN
WHERE UPPER(strFirstname) LIKE UPPER('Peter%')
UNION
SELECT * FROM RES_NAME CCN
WHERE UPPER(strMiddleName) LIKE UPPER('Peter%')
UNION
SELECT * FROM RES_NAME CCN
WHERE UPPER(strLastName) LIKE UPPER('Peter%');

HTH.
Michael
Previous Topic: SQL Types and Procedure
Next Topic: Creating triggers, need help
Goto Forum:
  


Current Time: Sat Dec 10 01:31:27 CST 2016

Total time taken to generate the page: 0.09412 seconds