Re: Calculating impact of indexes for DML

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

Other related posts: