FAQ
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

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 -----
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 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-----From: Marul Mehta
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.


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

Search Discussions

  • Edward Shevtsov at Sep 2, 2002 at 11:58 am
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

    Сообщение

    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: root@fatcity.com
    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 about the 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 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-----From: Marul Mehta
    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.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Edward Shevtsov
    INET: eshevtsov_at_detmir.ru

    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).
  • Nicoll, Iain \(Calanais\) at Sep 2, 2002 at 11:58 am
    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
    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).
  • Naveen Nahata at Sep 2, 2002 at 1:18 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
    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).
  • Apmanikandan at Sep 2, 2002 at 2:13 pm
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

    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 about the 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 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-----From: Marul Mehta
    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.


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: apmanikandan
    INET: apmanikandan_at_hotmail.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).
  • Marul Mehta at Sep 3, 2002 at 1:33 pm
    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"
    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
    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).
  • Jamadagni, Rajendra at Sep 3, 2002 at 1:58 pm
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jamadagni, Rajendra
    INET: Rajendra.Jamadagni_at_espn.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).

    text/plain attachment: InterScan_Disclaimer.txt

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 2, '02 at 10:38a
activeSep 3, '02 at 1:58p
posts7
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase