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:

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

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

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

Previous: Create Index
Next: Drop Index