w3resource

SQL ALTER INDEX

Alter Index

The ALTER INDEX statement is used to alter the definition of an index.

Note : The ALTER INDEX command is not a part of the ANSI SQL standard, and thus its syntax varies among vendors.

Syntax:

ALTER [UNIQUE] INDEX <index name> ON <table name> (<column(s)>);

Parameters:

Name Description
UNIQUE Defines the index as a unique constraint for the table and disallows any duplicate values into the indexed column or columns of the table.
index_name Name of the index table.
table_name Name of a base table.
column(s) Name of the columns of the table.

Alter Index in PostgreSQL, Oracle, SQL Server

Alter Index in PostgreSQL 9.3.13

In PostgreSQL, ALTER INDEX command changes the definition of an existing index.

Syntax:

ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )

Alter Index in Oracle 11g

In Oracle ALTER INDEX statement is used to change or rebuild an existing index.

Prerequisites :

  • The index must be in your own schema or you must have to ALTER ANY INDEX system privilege.
  • To execute the MONITORING USAGE clause, the index must be in your own schema.
  • To modify a domain index, you must have EXECUTE object privilege on the index type of the index.
  • Schema object privileges are granted on the parent index, not on individual index partitions or subpartitions.
  • You must have tablespace quota to modify, rebuild, or split an index partition or to modify or rebuild an index subpartition.

Syntax:

ALTER INDEX [ schema. ]index
  { { deallocate_unused_clause
    | allocate_extent_clause
    | shrink_clause
    | parallel_clause
    | physical_attributes_clause
    | logging_clause
    } ...
  | rebuild_clause
  | PARAMETERS ( 'ODCI_parameters' )
               )
  | COMPILE
  | { ENABLE | DISABLE }
  | UNUSABLE
  | VISIBLE | INVISIBLE
  | RENAME TO new_name
  | COALESCE
  | { MONITORING | NOMONITORING } USAGE
  | UPDATE BLOCK REFERENCES
  | alter_index_partitioning
  }

Alter Index in SQL Server 2014

In SQL Server ALTER INDEX command changes the definition of an existing index.

Syntax:

-- SQL Server Syntax

  ALTER INDEX { index_name | ALL } ON <object>
    {
    REBUILD {
    [ PARTITION = ALL ] [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
    | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]
    }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
    [ ; ]
  <object> ::= 
    {
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
    }
  <rebuild_index_option > ::=
    {
    PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor 
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { 
    ON [ ( 
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
    ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } ) 
    ) ] 
    | OFF } 
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | COMPRESSION_DELAY = {0 | delay [Minutes]}
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } 
    [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    }
  <single_partition_rebuild_index_option> ::=
    {
    SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    }
  <reorganize_option>::=
    {
    LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF}
    }
  <set_index_option>::=
    {
    ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY= {0 | delay [Minutes]}
    }
  <low_priority_lock_wait>::=
    {
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] , 
    ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )

 

See Also : CREATE INDEX for information on creating an index.

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Create Index
Next: Drop Index