Re: Disabling indexes - temporarily
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
RE: Disabling indexes - temporarily
If adding 400 records slows down the system, I guess it is time to take a hard look at your set-up and see where the problems are occurring by measuring and analyzing the waits in the database. How did you establish that balancing indexes causes the delay? No matter what kind of solution you implement, I'd still look at the waits that you see during these inserts, that will explain the problem to you. To me, 400 rows causing problem hints at a bigger problem than one visible. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Disabling indexes - temporarily
Thanks Naveen, Lets forget aboutthe statistics and performance, but I have such type of requirenment than is there any way out ? Marul. - Original Message - From: Naveen Nahata To: Multiple recipients of list ORACLE-L Sent: Saturday, August 31, 2002 11:58 PM Subject: RE: Disabling indexes - temporarily Firstly, you are only inserting 100-400 records daily, which is not a big deal. Even if there was a way to stop the indexesfrom gettingupdated, it won't increase the performance by a noticable amount. Secondly, there isno way(asfar 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-From: Marul Mehta [mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 11:08 PMTo: Multiple recipients of list ORACLE-LSubject: Disabling indexes - temporarily 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.
RE: Disabling indexes - temporarily
Title: Marul, have a look at table partition and ALTER TABLE EXCHANGE PARTITION WITH TABLE statement. Using the combination of these approaches you're able to limit index rebuilding with a few partitions if your indexes are local. At least you will be able to separate I/O. Regards, Ed -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Marul MehtaSent: Monday, September 02, 2002 2:38 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Disabling indexes - temporarily Thanks Naveen, Lets forget aboutthe statistics and performance, but I have such type of requirenment than is there any way out ? Marul. - Original Message - From: Naveen Nahata To: Multiple recipients of list ORACLE-L Sent: Saturday, August 31, 2002 11:58 PM Subject: RE: Disabling indexes - temporarily Firstly, you are only inserting 100-400 records daily, which is not a big deal. Even if there was a way to stop the indexesfrom gettingupdated, it won't increase the performance by a noticable amount. Secondly, there isno way(asfar 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-From: Marul Mehta [mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 11:08 PMTo: Multiple recipients of list ORACLE-LSubject: Disabling indexes - temporarily 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.
RE: Disabling indexes - temporarily
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).
RE: Disabling indexes - temporarily
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).
Re: Disabling indexes - temporarily
if u are very sure about the data, then how about inserting these data into a temporary table until eod, and insert into actual tables after ur busy hrs. - Original Message - From: Marul Mehta To: Multiple recipients of list ORACLE-L Sent: Monday, September 02, 2002 6:38 PM Subject: Re: Disabling indexes - temporarily Thanks Naveen, Lets forget aboutthe statistics and performance, but I have such type of requirenment than is there any way out ? Marul. - Original Message - From: Naveen Nahata To: Multiple recipients of list ORACLE-L Sent: Saturday, August 31, 2002 11:58 PM Subject: RE: Disabling indexes - temporarily Firstly, you are only inserting 100-400 records daily, which is not a big deal. Even if there was a way to stop the indexesfrom gettingupdated, it won't increase the performance by a noticable amount. Secondly, there isno way(asfar 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-From: Marul Mehta [mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 11:08 PMTo: Multiple recipients of list ORACLE-LSubject: Disabling indexes - temporarily 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.
Disabling indexes - temporarily
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.
RE: Disabling indexes - temporarily
Firstly, you are only inserting 100-400 records daily, which is not a big deal. Even if there was a way to stop the indexesfrom gettingupdated, it won't increase the performance by a noticable amount. Secondly, there isno way(asfar 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-From: Marul Mehta [mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 11:08 PMTo: Multiple recipients of list ORACLE-LSubject: Disabling indexes - temporarily 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.
