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" <ORACLE-L_at_fatcity.com> 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:ORACLE-L_at_fatcity.com>
> 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: iain.nicoll_at_calanais.com
>
> 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: 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: Naveen Nahata
> INET: naveen_nahata_at_mindtree.com
>
> 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: 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: Marul Mehta
  INET: marul_at_zycus.com

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: 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).