Dropping Oracle index w/ .NET Oracle Data Access

Question

So my .NET utility is performing massive amounts of inserts dynamically and it would make sense to drop the index while performing these and reconstructing it after performing the inserts.

Is the best way to do this using OracleCommand and setting command text to literal SQL?

So for example:

OracleCommand dropIndexCommand = new OracleCommand();

dropIndexCommand.CommandText = "DROP INDEX MYIDX";

And after wards:

OracleCommand reconstructIndexCommand = new OracleCommand();

reconstructIndexCommand.CommandText = "CREATE INDEX MYIDX ON...";

Good? Bad? Ugly?

Answer 1

Probably safer to disable the index and then rebuild it, rather than embedding the DDL to create the index in your application. You can alter the session to skip unusable indexes.