26

I've recently stopped to think that Primary Keys are not indexes, they're a combination of Unique and Null constraints. And till now, I've never created index for PK columns. My question is if I should create index for PK columns if this column is going to be used in the WHERE part of many queries.

Mikayil Abdullayev
  • 12,117
  • 26
  • 122
  • 206
  • 9
    Primary keys _are_ indexed. – Mat Mar 29 '13 at 12:54
  • 1
    To see the index that backs the primary key constraint: select ui.* from USER_CONSTRAINTS uc, USER_INDEXES ui where uc.table_name = 'MY_TABLE_NAME' and constraint_type = 'P' and ui.TABLE_OWNER = uc.OWNER and ui.TABLE_NAME = uc.TABLE_NAME and ui.INDEX_NAME = uc.INDEX_NAME; – rimsky Mar 17 '15 at 23:08
  • 1
    Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled. – Thulani Chivandikwa Jun 12 '19 at 07:48
  • No action is required by you when you issue the CREATE TABLE or ALTER TABLE statement to create the index, but you can optionally specify a USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled. – Thulani Chivandikwa Jun 12 '19 at 07:48

1 Answers1

27

Oracle will create an index for you, or can use an existing one. Whether a unique or non-unique index is used is up to you.

http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1006566

A primary key itself is not an index, and nor is a unique constraint -- they are both constraints. However an index is used to support them.

A unique index is rather different as it can exist in the absence of a unique or primary key constraint, and neither constraint type require that the index supporting it be unique.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • 2
    Hi, I'd just like to ask a followup question. If a primary key has multiple columns, will an index be created for each column or just the combination of all columns? For example if I have primary key(custno, orderno, date) and I want to index 'orderno', do I need to create a secondary index on just orderno or does it already have one created by Oracle because it's part of the primary key? I tried searching this but couldn't find a clear answer. Thanks in advance! – Rash8151 Dec 13 '19 at 12:36
  • 3
    @Rash8151 a single index is automatically created for those columns. – David Aldridge Dec 17 '19 at 10:18