3

When i create a primary key in oracle table, why does it create a 'clustered' index by default. What is the reason for this automatic creation of a clustered index on creation of a primary key? is it just the Oracle designer's preference that he designed oracle in this way?

MozenRath
  • 9,652
  • 13
  • 61
  • 104
Victor
  • 16,609
  • 71
  • 229
  • 409
  • 1
    What is `Clustered index`? Or why is this question labeled with `Oracle`? – ibre5041 Aug 27 '13 at 14:26
  • 2
    @Ivan This is a Microsoft SQL concept, Oracle doesn't have clustered indexes. As such the question is ambiguous: does the OP want to know why an index is created when you add a PK? or why a 'clustered' index --which doesn't exist-- is created? – Vincent Malgrat Aug 27 '13 at 14:42

3 Answers3

17

Oracle will create an index to police an unique constraint where no pre-existing index is suitable. Without the index, Oracle would need to serialize operations (such as a table lock) whenever someone tries to insert or delete a row (or update the PK).

Contrarily to MS-SQL Server, this index is not clustered on heap tables (default table organization), i.e. this index won't change the underlying table structure and natural order. The rows won't be reordered when Oracle creates the index. The index will be a B-tree index and will exist as a separate entity where each entry points to a row in the main table.

Oracle doesn't have clustered index as MS SQL, however indexed-organized tables share some properties with cluster-indexed tables. The PK is an integral part of such tables and has to be specified during creation.

(Oracle also has table clusters, but they are a completely different concept).

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • This is why when you develop applications against SQL Server you can say `Select * from table` and against Oracle you need to do `Select * from table Order By...`. Because when you have clustered index PK, it sorts stuff for you automatically. – T.S. Aug 27 '13 at 15:31
  • @t.s. only if you want the rows ordered by the pk obviously =) Also I would not rely on natural ordering without an ORDER BY clause: what happens if the optimizer changes its access path/method? (answer: you'll get rows out of order) – Vincent Malgrat Aug 27 '13 at 16:09
  • That was just an example what clustered index does to you :o) – T.S. Aug 27 '13 at 16:13
0

Creating Index is basic functionality of Primary key, it is also in SQL Server and MySQL, Clustered Index makes your searches faster.

donstack
  • 2,557
  • 3
  • 29
  • 44
-1

The Database Engine automatically creates a unique index to enforce the uniqueness of the PRIMARY KEY constraint. If a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.

Read this: http://www.sqlskills.com/blogs/kimberly/the-clustered-index-debate-continues/

Arun Kumar M
  • 848
  • 9
  • 14