Search code examples
sqloracle-databasecreate-tableinformation-schemadata-dictionary

What uniquely identifies an index in Oracle?

Within the Oracle documentation, the (DBA|ALL)_INDEXES view has

OWNER The owner of the index

INDEX_NAME The name of the index

TABLE_OWNER The owner of the indexed object

TABLE_NAME The name of the indexed object

My question is what uniquely identifies the index? Is it just OWNER and INDEX_NAME, or is it OWNER, INDEX_NAME, TABLE_OWNER and TABLE_NAME?

Solution

  • An index is uniquely identifed by its INDEX_NAME and its OWNER (ie the schema it belongs to). Basically, each object in a schema must have a unique name. Two different tables cannot have an index of the same name, unless these indexes belong to two different schemas.

    Consider this simple example:

    create table mytable(id int, val int);
create index myindex on mytable(id);
-- ok

create table mytable2(id int);
create index myindex on mytable2(id);
-- ORA-00955: name is already used by an existing object