OraFAQ Forum: Performance Tuning » Function based index

Home » RDBMS Server » Performance Tuning » Function based index (Oracle, 10g, Windows Server 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Function based index [message #455720]

Wed, 12 May 2010 12:30

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hi,

Check the following query

Select EMPNO,ENAME,DEPTNO
From EMP
Where Upper(ENAME) Like Upper('ritesh%');

Can a Function based index be beneficial on ENAME

Create Index emp_ename On EMP(Upper(ENAME));

Regards,
Ritesh

Report message to a moderator

Re: Function based index [message #455721 is a reply to message #455720]

Wed, 12 May 2010 12:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

UPDATE EMP set ename = upper(ename);

above would be MUCH more effective & efficient in the long run

Report message to a moderator

Re: Function based index [message #455725 is a reply to message #455721]

Wed, 12 May 2010 12:53

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

My doubt here is that will index emp_ename be used

Regards,
Ritesh

Report message to a moderator

Re: Function based index [message #455726 is a reply to message #455725]

Wed, 12 May 2010 12:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

an empirical test would answer your question

Report message to a moderator

Re: Function based index [message #455769 is a reply to message #455726]

Wed, 12 May 2010 22:21

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hi,
My index named emp_ename is used by oracle, is text index on column ename more beneficial?

But for that i have to remove Upper() function, is there a way to use TEXT index without removing Upper().

Regards,
Ritesh

[Updated on: Wed, 12 May 2010 22:25]

Report message to a moderator

Re: Function based index [message #455770 is a reply to message #455769]

Wed, 12 May 2010 22:23

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

he got the answer Wink

sriram

Report message to a moderator

Re: Function based index [message #455933 is a reply to message #455770]

Thu, 13 May 2010 11:02

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

TEXT indexes are a whole nother ball of wax. Yes, you can create a TEXT index on ename. Yes it will work (more or less). But it won't make things any easier for you. You will find you still need to deal with query syntax. Additionally, depending upon your version of ORacle, TEXT indexes present their own challenges. I suggest you do significant reading and testing before you adopt use of TEXT indexes.

Good luck, Kevin

Report message to a moderator

Re: Function based index [message #455937 is a reply to message #455933]

Thu, 13 May 2010 11:49

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

I wonder how its possible to a normal user to edit his post even after the reply?
here OP edited his post 2 min after my reply.

sriram

Report message to a moderator

Re: Function based index [message #455940 is a reply to message #455937]

Thu, 13 May 2010 12:07

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

He started to edit BEFORE your reply was recorded.

Regards
Michel

Report message to a moderator

Previous Topic:	Help required to tune a query
Next Topic:	Tune sql query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 16 21:26:40 CDT 2025