Re: Calculating impact of indexes for DML

From: Vladimir Andreev <vandreev_at_gmail.com>
Date: Thu, 25 Jun 2009 12:41:41 +0200
Message-ID: <442adaf60906250341s451498afx3d5aef8a1e6e4988_at_mail.gmail.com>



Hi Raj,

In a "DW and staging environment", it should be easy to disable i.e., mark unusable, and measure, since there is usually no concurrent activity that relies on these indexes. You also need to factor in the cost of rebuilding the indexes at the end of the ETL, but in my (albeit limited) experience mark unusable, insert (direct-path), rebuild (nologging, parallel, with the usual caveats about I/O bandwidth, backup, etc.) is faster than
insert (non-direct, or direct in a non-empty table/partition with lots of indexes).

It is also a matter of balance: the ETL itself may need some of the indexes (think MERGE), so you may want to leave some of them intact.

Also, direct-loading an empty partition with local indexes present (nologging, parallel, see above) would be about as fast as direct-loading the data and then building the indexes.

I realize that this is unscientific and too general to hold in all situations, but too much depends on the actual ETL, hardware, data model...

HTH,
Flado

On Thu, Jun 25, 2009 at 10:59, rjamya <rjamya_at_gmail.com> wrote:

> Hi all,
> Is it possible to gauge/capture impact of having (a bit too many) indexes
> on tables for DML operations? I know one can always drop and measure, but
> outside of that are there any other methods? This relates to DW and staging
> environment so multi-million row inserts do happen, db is 10203. I am trying
> to set-up a test env but it is going to take time.
>
> Many thanks in advance
> -----
> Best regards
> Raj
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 25 2009 - 05:41:41 CDT

Original text of this message