Thanks a lot to all who have contributed their experiences and ideas for thsi problem. I have to look into the application and business details whether this is feasible or not. Have to talk to our tech head.
Marul. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 02, 2002 6:48 PM > Iain, > > thats a fantastic idea. > > Naveen > > -----Original Message----- > Sent: Monday, September 02, 2002 5:28 PM > To: Multiple recipients of list ORACLE-L > > > Could you have a trigger which before insert, inserts into another empty > table with exactly same layout but rejects the insert on the main table. > Then disables the trigger and adds these at a non-busy stage and reenables > the trigger. Would be a whole lot quicker if it's possible. > > > > -----Original Message----- > Sent: Monday, September 02, 2002 11:38 AM > To: Multiple recipients of list ORACLE-L > > > Thanks Naveen, > Lets forget about the statistics and performance, but I have such type of > requirenment than is there any way out ? > > Marul. > > ----- Original Message ----- > To: Multiple recipients of list ORACLE-L <mailto:[EMAIL PROTECTED]> > Sent: Saturday, August 31, 2002 11:58 PM > > Firstly, you are only inserting 100-400 records daily, which is not a big > deal. Even if there was a way to stop the indexes from getting updated, it > won't increase the performance by a noticable amount. > > Secondly, there is no way(as far as i know) to make the indexes READ-ONLY > with the table in READ-WRITE mode. > > Thirdly, rebuilding 20 indexes on a table with 1 million record will take a > long time, in comparison updation by 100-400 records is nothing. > > It neither feasible nor advisable. > > Naveen > > -----Original Message----- > Sent: Saturday, August 31, 2002 11:08 PM > To: Multiple recipients of list ORACLE-L > > > Hi all, > > Need to know if the following is possible in Oracle(any version):- > > I have a table of around > (a) 30 Columns > (b) 20 out of 30 are indexed > (c) around 1 million (1,000,000) records. > > Most of the time there will be heavy reads (select queries) on this table > except for some 100-400 records to be inserted in a day. The newly inserted > records will not be selected by the queries for the next 24 hours (this is > based on some business logic), thats for sure. > > Now the problem is when ever a record(s) is inserted the entire bunch of > indexes is updated/rebuild by the Oracle which considerably slows down the > throughput of the system during that period of time (until all indexes are > updated). > > Can we have a solution whereby indexes should not be updated when a > record(s) is inserted, because I know that these records will not be the > part of the query for the next 24 hrs. The indexes will be re-built > manually/scheduled during the off-peak hours once a day. In this way, the > next day, new records inserted a day before will be ready to be fetched by > the queries. > > Note- I can't put my indexes offline not for a single minute during peak > hours. > > Any clues? > > TIA, > Marul. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Nicoll, Iain \(Calanais\) > 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: 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).