Re: Calculating impact of indexes for DML

From: rjamya <>
Date: Thu, 25 Jun 2009 20:29:39 -0400
Message-ID: <>

Thanks Flado,
Yes we are doing some of what you have mentioned, but my question was more on the measurement side. e.g. using 10046 trace one can measure lios etc ... can one also estimate/gauge impact of existing indexes? I am asking this because in some cases we use exchange partition to load from staging to DW, so indexes will be important.


On Thu, Jun 25, 2009 at 6:41 AM, Vladimir Andreev <>wrote:

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

Best regards

Received on Thu Jun 25 2009 - 19:29:39 CDT

Original text of this message