Mahul, Sounds a bit of a classic problem - optimum performance and availability required with no time given for database admin/reorg work. One idea you might think about is messy but could involve the following:
Step 1 Create 2 tables - Prob_table and prob_table_copy Step 2 Create indexes on prob_table Step 2 Load up 10,000 rows into prob_table(while being read at the same time) Step 3 Load next 10,000 rows into prob_table_copy Step 4 Insert into prob_table_copy select * from prob_table Step 5 Build indexes on prob_table_copy step 6 Rename prob_table to prob_table_temp Step 7 Rename prob_table_copy to prob_table Step 8 Insert into prob_table select * from prob_table_temp where timestamp > xxxx (to capture rows that were inserted after the select was called) Step 9 Load up next 10,000 rows into prob_table(while being read at the same time) Step 10 Rename prob_table_temp to prob_table_copy etc.... I'm sure you get the idea. As I mentioned messy but not impossible. In addition you will also need to capture timestamps if you don't already. Good luck! Chris -----Original Message----- Sent: 05 September 2002 16:08 To: Multiple recipients of list ORACLE-L Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. But if this auto-commit is the issue than why first 10K records are inserted quickly. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Any clues? TAI Marul. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 05, 2002 2:53 PM > Marul, > > I think this question needs to be answered, otherwise impossible to make > suggestions... > > Chris > > -----Original Message----- > Sent: 05 September 2002 08:18 > To: Multiple recipients of list ORACLE-L > > > Marul, what i fail to understand is: > > Are you running a batch job of inserting 350,000 inserts? > > If that is the case then you should go for dropping and recreating the > indexes. Can you partition the table and use local partitioned indexes? > > Can't you try the option of inserting in parallel? > > Did you try disabling the constraints and then ENABLE NOVALIDATE them(that > will only work if you r sure of the data)? > > Naveen > > -----Original Message----- > Sent: Thursday, September 05, 2002 11:48 AM > To: Multiple recipients of list ORACLE-L > > > Thanks Chris, > So than any clues how to resolve this issue, as earliest, becuase this is > causing bottleneck in our application . > > Rgds, > Marul. > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, September 05, 2002 2:29 AM > > > > Good question, Jared! Perhaps 'overflow' is technically not the correct > > term to use to decribe this scenario but it seemed to fit the bill > > sufficiently to mail off a quick one-liner solution without going into > great > > depth. Some of us have work to do, you know ;) > > > > To redeem myself I probably should have mentioned that this table sounds > > pretty volatile. Consequently the index(es) are likely to end up fairly > > disorganized, especially if the 350k records are being inserted in > > ascending order. Once you start adding levels to the index things start > to > > slow down.... > > > > Chris > > > > -----Original Message----- > > Sent: 04 September 2002 16:50 > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > > > > On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: > > > It sounds to me like the indexes are going into overflow - this will > cause > > > > What do you mean by 'overflow'? > > > > Jared > > > > > the insert time to increase. I would suggest batching up the inserts, > > > dropping the indexes, running the inserts and re-creating the indexes. > > > > > > Chris > > > > > > -----Original Message----- > > > Sent: 04 September 2002 07:53 > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Hi All, > > > > > > We have a table which can contain more than half a million records. When > > we > > > try to insert some 10k records in the empty table it get inserted in 10 > > > min. but as the size increases time taken to insert also increases. > After > > > 350,000 records it takes around an hour to insert 10k records. > > > There are around 15 columns in it out of which 11 are indexed. There is > > one > > > concatenated function-based index on two columns of Varchar type and two > > > separate index for the same two columns. > > > > > > I have checked the free space for the tablespaces to which the table and > > > indexes are attached to. They are in two separate tbs. > > > > > > Any clues why this is happenning. > > > > > > TIA > > > Marul. > > > > ---------------------------------------- > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > > Content-Transfer-Encoding: 7bit > > Content-Description: > > ---------------------------------------- > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California -- Public Internet access / Mailing Lists > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (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: Marul Mehta > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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: Naveen Nahata > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
