Coskan’s Approach to Oracle

March 26, 2007

Do Primary/Unique Keys need Index??

Filed under: Basics — coskan @ 7:33 pm

Till 10 minutes ago I was thinking that primary key or unique key constraints always created with their indexes by default. But after reading Tables chapter of Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution (book of Mr Kyte) all my thoughts changed.

He noted “A unique or primary key constraint may or may not create a new index. If there is an existing index on the constrained columns, and these columns are on the leading edge of the index, the constraint can and will use them.

Here is the proof of the note;

code_listing 7

References Used :

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution
(Pg 341)

All Test are done on 10.2.0.1 XE

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers