Disable an Oracle index
Question:
I want to disable an Oracle index. What is the syntax
to disable an index?
Answer: First,
why do you want to disable an index? If you are doing batch
loads, the proper procedure is to drop and then re-create the index.
In most releases, Oracle allows you to disable an index ONLY
if it is a function-based index! For non-function-based
indexes, you disable the index by marking the index as unusable.
You can disable a function-based index with this command:
SQL> create index my_fbi
on mytab (upper(emp_name));
SQL> alter index my_fbi disable
You can mark an index as unusable with this command:
SQL > alter index
my_nonfbi_index unusable;
SQL> alter session set skip_unusable_indexes =
true
(BEWARE: Marking an
index as unusable will prevent any DML against the base table!)
Also see:
skip_unusable_indexes tips.
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|