| Index overhead [message #279454] |
Thu, 08 November 2007 09:50  |
balajisundar
Messages: 29
Registered: October 2007
|
Junior Member |
|
|
Hi Guys,
I have two indexes defined for a product_account table namely PAC_INDX1 and PAC_INDX1_TEST. I run a procedure that updates field values of the product_account. Does the optimiser chooses the best of these two indexes and executes???
CREATE INDEX XYZ.PAC_INDX1 ON XYZ.PRODUCT_ACCOUNT (ORG_NO, CRACCT_NO, PROD_NO) TABLESPACE INDEX_M;
CREATE INDEX XYZ.PAC_INDX1_TEST ON XYZ.PRODUCT_ACCOUNT (ORG_NO, CRACCT_NO, PROD_NO, PAST_DUE_AM, STATUS_CD) TABLESPACE INDEX_M;
im just trying to find out if having an alternate index might reduce the performance. Correct me if im wrong.
Regards,
Balaji
|
|
|
|
| Re: Index overhead [message #279455 is a reply to message #279454] |
Thu, 08 November 2007 09:54  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I don't think there is any advantage to having the first index on the system if the second one is there too.
It will slow inserts, updates and deletes down slightly as you will need to update two indexes rather than just one.
|
|
|
|
| Re: Index overhead [message #279470 is a reply to message #279455] |
Thu, 08 November 2007 11:40 |
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
|
Senior Member |
|
|
It's depend on your query as well indexed columns .Let suppose you have column A B C and D what would be the
benefit of index if it on columns (A & B or C) if your where clause doesn't use any of the indexed column?
You should 1st know the queries and the columns mostly used in where clause then try to indexed your table according to that.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
