What is the difference between Disabling and Re-enabling an index compared to Dropping and Re-creating it?

Question

As the question states, what is the difference between the two in terms of performance, and which is better for what kind of scenarios?

Or does these two have the same effect? What would be the pros and cons of each if it applies?

From what I understand, Recreating an index would also incur its rebuild similar to gathering stats (11g)

What about disabling and re-enabling an index? Does it also automatically gather stats?

Please take note that I am asking this with the mindset of needing to do massive batch inserts/updates

Many Thanks

Answer 1

We cannot disable an index, unless it is a function-based index.

SQL> alter index i42 disable;
alter index i42 disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL>

We can make it unusable but that has a rather unfortunate side-effect:

SQL> alter index i42 unusable;

Index altered.

SQL> insert into t42 values (sysdate, 6, 'FOX IN SOCKS');
insert into t42 values (sysdate, 6, 'FOX IN SOCKS')
*
ERROR at line 1:
ORA-01502: index 'APC.I42' or partition of such index is in unusable state


SQL> 

Just to prove the point about function-based indexes:

SQL> create index f42 on t42(initcap(name))
  2  /

Index created.

SQL> alter index f42 disable;

Index altered.

SQL> insert into t42 values (sysdate, 6, 'MR KNOX')
  2  /
insert into t42 values (sysdate, 6, 'MR KNOX')
*
ERROR at line 1:
ORA-30554: function-based index APC.F42 is disabled


SQL> 

I think that rules out disabling indexes for your purposes. If you want to work on a table without indexes you need to drop them. Whether that makes sense depends on the specifics of your case, as I said in my answer to your previous question ....

---

As for constraints, we cannot drop an index which is used to enforce a unique constraint. However, we can drop a constraint but leave the index in place. In that case, if the index is a unique one, it will still enforce integrity:

SQL> create unique index i42 on t42(id);

Index created.

SQL> alter table t42 add constraint t42_pk primary key (id);

Table altered.

SQL> insert into t42 values (sysdate, 5, 'MAISIE');

1 row created.

SQL> r
  1* insert into t42 values (sysdate, 5, 'MAISIE')
insert into t42 values (sysdate, 5)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T42_PK) violated


SQL> alter table t42 drop constraint t42_pk;

Table altered.

SQL> insert into t42 values (sysdate, 5, 'MAISIE');
insert into t42 values (sysdate, 5, 'MAISIE')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.I42) violated


SQL>