Databases Reference
In-Depth Information
Table altered.
SQL> select index_name, status from dba_indexes where table_name='TEST';
INDEX_NAME STATUS
------------------------------ --------
TEST_IDX1 UNUSABLE
SQL>
Once you rebuild the index, it becomes usable again.
SQL> alter index test_idx1 rebuild
2 parallel 12
3 nologging;
Index altered.
SQL> select index_name, status from dba_indexes where table_name='TEST';
INDEX_NAME STATUS
------------------------------ --------
TEST_IDX1 VALID
SQL>
Unlike in the case of a table, you can't move an index by using a "move index" command. You move
an index to a different tablespace by rebuilding the index. So, if you want to move the index
test_idx1
from the
USERS
Tablespace to a different tablespace (DEV_OIM in the example), here is what you need to
do:
SQL> alter index test_idx1 rebuild
2 parallel 12
3 nologging
4* tablespace dev_oim
SQL> /
Index altered.
Improving Index Creation Efficiency
The speed with which you can create an index is always critical when you're creating indexes on large
tables. You can adopt several strategies to minimize the index creation time, as summarized in the
following sections. Often, you can combine several of these strategies to cut short the time for creating
an index.
