Menu
I am currently learning indexes in Oracle. In Oracle documentation, following can be found :
Although the database creates an index for you on a column with an integrity constraint, explicitly creating an index on such a column is recommended.
Can anybody here tell me the rationale behind this ? If database creates an index automatically, why should we create another index on those columns explicitly ?
587
32) Why we need to create an index if the primary key is already present in a table? Primary key can store null value, whereas a unique key cannot store null value.
When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.
If a UNIQUE constraint is added to a column that has duplicated values, the Database Engine returns an error and does not add the constraint. The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint.
The column you want to index does not need to have unique values.
At the very least, if you create an index explicitly, you can give it a meaningful name. If you then need to refer to the index by name, it will be in a human-readable form.
Note that you can create an explicit index WHILE you are creating the constraint, in the USING INDEX clause. The documentation has a few examples: https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11725
69
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
