Home » RDBMS Server » Performance Tuning » Index overhead (Oracle, 9.2x, Unix)
Index overhead [message #279454] Thu, 08 November 2007 09:50 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: How to improve a group by sql stmt (merged)
Next Topic: Process Threads
Goto Forum:
  


Current Time: Thu Nov 14 06:08:26 CST 2024