Adding Indexes drastically slows down App

We are seeing drastic slowdowns in our system after adding 2 indexes to the largest table in our DB.

Let me give you some background:
We develop Accounting Software that can use either SQL Server (ugh..I know!) or Oracle as its DB backend. As we put out new releases, we sometimes need to "Migrate" the database. In our case to migrate means to add new columns to a table, add a new table, add new indexes, etc.

In our most recent release one of our changes is to add 2 indexes to 2 separate columns on our biggest table. In a brand new build of the new version, everything flies -- even with millions of rows. But, when we apply the changes via our migration tools, a 2 minute process(generating a report) turns into 45 minutes(the table has 18 million rows). When we remove the two new indexes, all returns to normal. We have done everything from running new stats to exporting and importing to remove fragmentation. We still are not seeing any imporvement. In total this table has 11 indexes. While, we know this slows down inserts and updates, we can live with that since most iserting and updating is done in overnight batch jobs. The indexes can be justfied as they really speed up our reporting and searching functions.

We just can't figure out why adding the 2 new indexes to an existing DB would cause SO much slow down - even after we did exp/imp to get rid of fragmentation. I know there will be some slow down with new indexes, but this seems extreme.

What else can we look at? What does Oracle do with those indexes that causes such a drastic slow down? We would like to try to track down a cause so we have explanations for our customers or to see if we need to change our migration process. Of course, all I hear is that the slowdown doesn't happen in SQL Server, so what is Oracle doing? I'm having a hard time explaining!

We're testing on: Oracle 8.1.6 on NT.

I would appreciate any insight or other things to try. Let me know if I need to post any additional info.

Thanks,

Elizabeth Elliott Received on Wed Jun 20 2001 - 10:24:38 CDT