Disable table indexes to boost up performance when doing insertion

There is pro and con for having indexes on the table.

The pro is when you use select statement indexes will help boost up the query. However, when you do DML statement (insert, update, delete) you might run into performance issue.

The work around of this when you need to insert a lot of data is just don’t use the indexes when you do the insertion. In other words, disable the indexes or make the indexes as unusable.

When you try to run the following command,

Alter Index . Disable; 

you may run into the error

ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

The solution for this is using the following command to mark the index unusable.

alter index your_index unusable;

And to remark your index usable again, you must rebuild the index by executing the following command.

alter index your_index rebuild [online];

If you still experience failure when executing DML after mark the index unusable.

You need to change parameter “skip_unusable_indexes” to true.

Use the following command to check if this parameter value is false or true.

show parameter skip_unusable_indexes;
If it is false then change it to true by running the following command. 

alter session set skip_unusable_indexes = true;

Note: If you are using 10g you cannot mark unusable unique index and insert the data.

Posted in: ORA-02243, Oracle Tuning, SQL Tuning