What is a Unique Index in Oracle

CREATE INDEX statement (SQL, Oracle)

An index is a database object with which read access to tables is to be accelerated. As a rule, WHERE clauses are evaluated. Indices correspond to primary keys or unique keys of the relational model and are automatically created in e.g. Oracle for primary keys (PRIMARY KEY) and uniqueness keys (UNIQUE).

SYNTAX of the CREATE INDEX statement:

:: = CREATE [UNIQUE] [BITMAP] INDEX index name ON table name (column name [, column name] ...); [USING INDEX index-name | CREATE INDEX statement]

UNIQUE means that the values ​​in the corresponding INDEX column are unique, NULL values ​​are ignored. Bitmap indexes are a special form at Oracle. The addresses (ROWIDs?) Are stored in them as Oracle-specific bitmaps.

WITH USING index, an existing index can be used or a CREATE index statement can be specified explicitly. No further index can be created on a column (or several) on which an index already exists.

Examples:

CREATE UNIQUE INDEX parts_i1 ON parts (part no); - defines a unique key on the parts table.

CREATE INDEX structure_i1 ON structure (OTeil, UTeil); - Defines a composite secondary key - via the columns OTeil and UTeiI of the Structure table.

CREATE UNIQUE INDEX parts_i2 ON parts (part no); - defines a unique key on the parts table USING index TEIL_X; - which uses an existing index TEIL_X.

see also: DROPBitmap-Index, namespace

Swell:

  • ANSI / ISO / IEC 9075-1: 2003. Part 1 "SQL / Framework", ISO International Organization for Standardization / ANSI American National Standards Institute, September 2003
  • ANSI / ISO / IEC 9075-2: 2003. Part 2 "SQL / Foundation", ISO International Organization for Standardization / ANSI American National Standards Institute, December 2003
  • Elmasri, Ramez / Navathe, Shamkant B .: "Basics of database systems", Pearson Studium, Munich, 2002, ISBN 3-8273-7021-3
  • Faeskorn-Woyke, Heide / Bertelsmeier, Birgit / Riemer, Petra / Bauer, Elena: "Database systems - theory and practice with SQL2003, Oracle and MySQL", Pearson Education, Munich, 2007, ISBN 978-3-8273-7266-6
  • Kemper, Alfons / Eickler, André: "Database Systems", Oldenbourg, Munich, 2009, 978-3-486-59018-0
  • Melton, Jim / Simon, Alan R .: "SQL: 1999 - Understanding Relational Language Components", Morgan Kaufmann, San Francisco, 2001, ISBN 1558604561
  • Oracle® Database SQL Language Reference 11g Release 2 (11.2), E17118-03, August 2010, http://download.oracle.com/docs/cd/E11882_01/server.112/e17118.pdf
  • Saake, Gunter / Sattler, Kai-Uwe / Heuer, Andreas: "Databases - Concepts and Languages", mitp-Verlag, Redline GmbH, Heidelberg, 2007, ISBN 3-8266-1664-2
  • Vossen, Gottfried: "Data models, database languages ​​and database management systems", Oldenbourg, Munich, 2008, ISBN 978-3-486-27574-2

Category: SQL, C