This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Oct 14, 2012 10:15 PM by 951274 Go to original post RSS
  • 15. Re: Difference between Primary Key and Unique Key with NOT NULL constraint
    6363 Guru
    Currently Being Moderated
    That is a cluster index

    http://docs.oracle.com/cd/E11882_01/server.112/e25494/clustrs003.htm#ADMIN11748

    i.e. an index on a cluster, rather than on a table.

    It is not a clustered index as in Microsoft SQL server

    http://msdn.microsoft.com/en-us/library/aa933131%28v=sql.80%29.aspx

    Which is in concept closer to an IOT but is implemented via create index.

    There is no such clustered index in Oracle.
  • 16. Re: Difference between Primary Key and Unique Key with NOT NULL constraint
    6363 Guru
    Currently Being Moderated
    Nag Aswadhati wrote:

    There is clustered index in sql server.
    In oracle there is Index Organized Table
    So to your original answer
    Nag Aswadhati wrote:
    Yes there is the difference between a PK and Unique + Not null.

    PK -- Clustered Index
    Unique --> Non Clustered Index
    You mean to say having a primary key forces a table to become an IOT?

    I think everyone who wrongly thinks Oracle has clustered indexes needs to read this before spreading any more misinformation.

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/toc.htm


    Finally, when you want to enforce co‚Äźlocation of data or you want data to be physically
    stored in a specific order, the IOT is the structure for you.

    For users of Sybase and SQL Server, this is when you would have used a clustered index,
  • 17. Re: Difference between Primary Key and Unique Key with NOT NULL constraint
    Nag Aswadhati Explorer
    Currently Being Moderated
    I dont mean that....
    Having a primary key table not becomes an IOT?

    an IOT might behaves like clustered index in sql server.


    Thank you for giving the information "Oracle doesnot have clustered Index".
  • 18. Re: Difference between Primary Key and Unique Key with NOT NULL constraint
    Sven W. Guru
    Currently Being Moderated
    Nag Aswadhati wrote:
    I dont mean that....
    Having a primary key table not becomes an IOT?

    an IOT might behaves like clustered index in sql server.
    No it does not. There is a little similiarity between both. However indexes are constructs mainly used for performance reasons (unique indexes are an exception to that rule). In this regard oracle, sybase and all otehr databases differ greatly. Concepts used on the one database can not be used in similiar way on the other. The reasons why sybase has clustered indexes do not exist in oracle.

    See also: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:586423377841
  • 19. Re: Difference between Primary Key and Unique Key with NOT NULL constraint
    951274 Newbie
    Currently Being Moderated
    Thanks all for putting your efforts,
    I really got more insights of Cluster Index,
    it cleared my confusion, and I am sure for few others too... :)

    Thanks to you,
    Nimish Garg
    3360
    Sven W.
    jeneesh
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points