UNIQUE & NON UNIQUE Indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: UNIQUE & NON UNIQUE Indexes

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Posts
    171
    Can someone please explain me the architectural difference between the way UNIQUE and NON UNIQUE indexes work? I would like to know if there are acrhitectural differences between the two.

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    I don't think there is any architectural difference between two. Both will be stored in b-tree architecture unless you specify the non-unique index to be BITMAP.

    Sanjay

  3. #3
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by SANJAY_G
    I don't think there is any architectural difference between two. Both will be stored in b-tree architecture unless you specify the non-unique index to be BITMAP.

    Sanjay
    Are you sure? Cos this was asked in an interview. Anyway, what happens in case of a BITMAP. Kindly explain

  4. #4
    Join Date
    Aug 2001
    Posts
    75
    use of a non unqiue index results in Oracle scanning all leaf blocks of the relevant part of the b-tree, when it finds a row that matches the query it will continue to scan all further leaf blocks to see if they are any other matching rows.

    BUT a unique index will scan all relevant leaf blocks of the b-tree and stop when it finds a row matching the queried key value because Oracle knows the index is unique - thus making use of a unique index much quicker.


    Thanks
    Sanjay
    OCP 8i

  5. #5
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    This is the difference between Unique and Non-unique index:

    For a unique index, there is one rowid per data value.
    For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid. Key values containing all nulls are not indexed, except for cluster indexes. Two rows can both contain all nulls and not violate a unique index.

    Key compression cannot be used in a unique index with a single-attribute key.

    Vijay.
    Say No To Plastics

  6. #6
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by oravijay
    Hi,

    This is the difference between Unique and Non-unique index:

    For a unique index, there is one rowid per data value.
    For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid. Key values containing all nulls are not indexed, except for cluster indexes. Two rows can both contain all nulls and not violate a unique index.

    Key compression cannot be used in a unique index with a single-attribute key.

    Vijay.
    Excellent!! Perhaps this is what the expected answer is. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width