ALTER INDEX

Change the properties of an index.

Syntax:

   ALTER INDEX [schema.]index options

Options:
The options used with this command can be any combination of the following

    ENABLE
    DISABLE
    COALESCE
    UNUSABLE
    RENAME TO new_index_name

    [NO]LOGGING
    PCTFREE int
    PCTUSED int
    INITRANS int
    MAXTRANS int
    STORAGE storage_clause

    ALLOCATE EXTENT [SIZE int K | M]
    ALLOCATE EXTENT [DATAFILE 'filename']
    ALLOCATE EXTENT [INSTANCE int]

    DEALLOCATE UNUSED
    DEALLOCATE UNUSED KEEP int K | M

    [NO]MONITORING USAGE
    UPDATE BLOCK REFERENCES

    NOPARALLEL
    PARALLEL int

    MODIFY PARTITION partition COALESCE
    MODIFY PARTITION partition UNUSABLE
    MODIFY PARTITION partition UPDATE BLOCK REFERENCES
    MODIFY PARTITION partition PARAMETERS ('alter_partition_params')
    MODIFY PARTITION partition partition_options

    partition_options:
        ALLOCATE EXTENT [SIZE int K | M]
        ALLOCATE EXTENT [DATAFILE 'filename']
        ALLOCATE EXTENT [INSTANCE int]
        DEALLOCATE UNUSED
        DEALLOCATE UNUSED KEEP int K | M
        [NO]LOGGING
        PCTFREE int
        PCTUSED int
        INITRANS int
        MAXTRANS int
        STORAGE storage_clause

    RENAME [SUB]PARTITION old_name TO new_name

    DROP PARTITION partition

    SPLIT PARTITION partition AT (value_list)
       [INTO (ptn_descr1, ptn_descr2)] [NOPARALLEL|PARALLEL int]

        ptn_descr:
           PARTITION [partition attrib_options]

    MODIFY DEFAULT ATTRIBUTES [FOR PARTITION partition] attrib_options

    attrib_options:
        TABLESPACE {tablespace|DEFAULT}
        [NO]LOGGING
        PCTFREE int
        PCTUSED int
        INITRANS int
        MAXTRANS int
        STORAGE storage_clause

    MODIFY SUBPARTITION subpartition UNUSABLE
    MODIFY SUBPARTITION subpartition sub_partition_options

    sub_partition_options:
        ALLOCATE EXTENT [SIZE int K | M]
        ALLOCATE EXTENT [DATAFILE 'filename']
        ALLOCATE EXTENT [INSTANCE int]
        DEALLOCATE UNUSED
        DEALLOCATE UNUSED KEEP int K | M

    REBUILD [rebuild_options]
    REBUILD NOREVERSE [rebuild_options]
    REBUILD REVERSE [rebuild_options]
    REBUILD [SUB]PARTITION partition [rebuild_options]

    rebuild_options:
        ONLINE
        COMPUTE STATISTICS
        TABLESPACE tablespace_name
        NOPARALLEL
        PARALLEL int
        [NO]LOGGING
        COMPRESS int
        NOCOMPRESS
        PCTFREE int
        PCTUSED int
        INITRANS int
        MAXTRANS int
        PARAMETERS ('odci_parameters')
        STORAGE storage_clause

More than one ALLOCATE EXTENT option should be specified in the same clause e.g.
ALLOCATE EXTENT SIZE 200K Datafile 'MyFile.idx'

"We trained hard, but it seemed that every time we were beginning to form up into teams we would be reorganised. I was to learn later in life that we tend to meet any new situation by re-organising, and a wonderful method it can be for creating the illusion of progress while producing confusion, inefficency and demoralisation" - Caius Petronius (A.D. 66)

Related Commands:

ANALYZE INDEX COMPUTE STATISTICS
CREATE INDEX
DROP INDEX

When to rebuild an index - PDF Debunking common myths about index maintenance.

Related Views:

 DBA_INDEXES            ALL_INDEXES              USER_INDEXES
    INDEX_HISTOGRAM
    INDEX_STATS
 DBA_INDEXTYPES           ALL_INDEXTYPES           USER_INDEXTYPES
 DBA_INDEXTYPE_OPERATORS  ALL_INDEXTYPE_OPERATORS  USER_INDEXTYPE_OPERATORS
 DBA_IND_COLUMNS          ALL_IND_COLUMNS        USER_IND_COLUMNS
 DBA_IND_EXPRESSIONS      ALL_IND_EXPRESSIONS    USER_IND_EXPRESSIONS
 DBA_IND_PARTITIONS       ALL_IND_PARTITIONS     USER_IND_PARTITIONS
 DBA_IND_SUBPARTITIONS    ALL_IND_SUBPARTITIONS  USER_IND_SUBPARTITIONS

Equivalent SQL Server command:

ALTER INDEX