-
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.
-
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
-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|