Oracle-L: RE: Performance tuning

From: Vikas Khanna <vkhanna_at_quark.co.in>
Date: Wed, 06 Nov 2002 21:48:24 -0800
Message-ID: <F001.004FD70C.20021106214824@fatcity.com>

This is where the balancing comes into picture. If it is a bulk Insert then definitely the performance would degrade to the extent that it has to create an entry in the Index at a particular place. If there are so many indexes on this table you should visualise them in such a manner that a concatenated index could be formed to take care of all your selects on this table.

The other alternative is to go in for partitioning and do the data partioning by range to that the selective data is getting indexed by the local index and not the global index on the table is getting effected.

However if the inserts are huge than the Selects than dropping the index is beneficial, but if the Selects are too much that the index is beneficial. It relayy depends on the nature of the application. If the rows you want to retrieve from the table are in the range of 5% - 10% then index is beneficial else it could be better for the CBO to go in for a Full table scan.

Moreover if it's a buldk insert you can disable the index at that point of time and then rebuild it online after the insert is over for that data to be used in Selects but if the inserts are happening in an OLTP application then you are the best judge,

Vikas Khanna
Vkhanna_at_quark.co.in

-----Original Message-----
Sent: Thursday, November 07, 2002 10:08 AM To: Multiple recipients of list ORACLE-L

I have a table in my application . This table gets all inserts during one procedure and select during other . Now if I make an index on this then the first procedure gets slow and if i drop the index then the second procedure gets very slow.
Is there some solution to get out of this problem

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: dilmohan
INET: dilmohan_at_delhi.tcs.co.in

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Vikas Khanna
INET: vkhanna_at_quark.co.in

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------