3

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

APC
  • 144,005
  • 19
  • 170
  • 281
Avias
  • 354
  • 1
  • 4
  • 14

2 Answers2

6

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>
Community
  • 1
  • 1
APC
  • 144,005
  • 19
  • 170
  • 281
  • That unfortunate side-effect that leads to ORA-01502 you'll get in 9i version for sure, and in 10g to 12c only, if it's a unique index that has been made unusable or if the default value(which is `true`) of `skip_unusable_indexes` initialization parameter has been changed to `false`, or there is a hint that forces the usage of that index. Otherwise, that exception wont be raised. – Nick Krasnov Nov 23 '13 at 12:55
0

When you disabe/enable index, you are telling DBMS to "not usethat index", but index still exists (as a physical entity). When you are droping indexes, you are telling DBMS to delete it, so when you alter your table with index, whole index tree has to be rebuild from begining (well that depends on implementation). Anyway, if you disable index for inserting and enable it, DBMS will have to add only inserted values to index. If you drop and create index, DBMS is starting from scratch (more expensive)

Antoniossss
  • 31,590
  • 6
  • 57
  • 99
  • But in terms of performance, does disabling the index have the same effect as to no index at all when inserting data? As compared ofcouse to inserting data into a table where an index is still present and enabled – Avias Nov 23 '13 at 10:03
  • What about in the case of update statements? In that scenario, would it still be better to just disable/re-enable an index? – Avias Nov 23 '13 at 10:07
  • Well, I think that it would be easier to check it out by yourself. Create temporary DB, insert milion of records with disabled index, later on, insert the same data without disabling index - measure timings. I say, that it is faster to disable and reenable index, as in such scenario index table is updated in single run, insteed of updating after each insert. – Antoniossss Nov 23 '13 at 10:08
  • Again, try it yourself. I would say, that disabling indexing is faster. – Antoniossss Nov 23 '13 at 10:08
  • 1
    I don't think disabling an index does what you think it does. – APC Nov 23 '13 at 11:09