Home » SQL & PL/SQL » SQL & PL/SQL » Is tuning needed for this SQL ? (Oracle 10g)
Is tuning needed for this SQL ? [message #311897] Mon, 07 April 2008 05:39 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Please suggest me if any tuning is needed for this SQL particularly in deocde part. also suggest me the columns that I can index. Should I decode the columns involved in decode ? If so, What type of index , function based ....?
SELECT 
VEI.PRODUCT_HOLDING_ROLE_ID PRODUCT_HOLDING_ROLE_ID, 
VEI.SCHEME_MEMBERSHIP_ID SCHEME_MEMBERSHIP_ID, 
VEI.SCHEME_ROLE_ID SCHEME_ROLE_ID, 
DECODE(NVL(VEI.PRODUCT_HOLDING_ROLE_ID,'PHR'),'PHR', DECODE(NVL(VEI.SCHEME_MEMBERSHIP_ID,'SM'),'SM', VEI.SCHEME_ROLE_ID, VEI.SCHEME_MEMBERSHIP_ID), VEI.PRODUCT_HOLDING_ROLE_ID) AS FIRST_VE_INVOLVEMENT, 
VE.VOTE_ID VOTE_ID, 
VE.VE_UNIFCN_STATUS_ID VE_UNIFCN_STATUS_ID 
FROM 
TPD_TL_W_VE_INVOLVEMENT VEI, 
TPD_TL_W_VOTE_ENTITLEMENT VE 
WHERE 
VEI.VOTE_ID=VE.VOTE_ID AND 
VEI. EFF_END_DATE IS NULL 

Re: Is tuning needed for this SQL ? [message #311913 is a reply to message #311897] Mon, 07 April 2008 06:28 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Please suggest me if any tuning is needed for this SQL particularly in deocde part.
I doubt that tuning DECODE will have any measurable effect.

> also suggest me the columns that I can index.
The only reasonable index for this query should be on VOTE_ID. It shall be unique/primary key in one table.

> Should I decode the columns involved in decode ? If so, What type of index , function based ....?
See the first answer.

It would be more difficult if both "tables" in the query are in fact views.
For performance tuning, you shall read and follow steps described in "Performance Tuning" tab in the Forum Guide sticky.
Re: Is tuning needed for this SQL ? [message #312048 is a reply to message #311897] Mon, 07 April 2008 20:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
DECODE is actually somewhat expensive as functions go. many a time I have wished to not have to use it. Problem is, it escapes me as to the few alternatives to DECODE that exist which might also be faster.

Function based indexes is a good one, but you can't really tune decode with an index. You should be using the index for access precision. If decode happens to be part of the requirements of your access, then all the better.

Kevin
Re: Is tuning needed for this SQL ? [message #312051 is a reply to message #311897] Mon, 07 April 2008 20:27 Go to previous message
bibsdash
Messages: 47
Registered: April 2008
Member
There are two things you have to look as both are expensive:

DECODE
VEI. EFF_END_DATE IS NULL


But if they are necessay then you donot have any option.

Make sure that you have a index(unique) built on the following column:

VEI.VOTE_ID=VE.VOTE_ID

Else Oracle will do a full table scan which will be expensive.
Previous Topic: Sql Query
Next Topic: How to add sysdate to the output file(spool) name in sqlplus
Goto Forum:
  


Current Time: Tue Dec 06 10:16:25 CST 2016

Total time taken to generate the page: 0.08752 seconds