difference between unique constraint and unique index

1. What is the difference between unique constraint and unique index when unique constraint is always indexed ? Which one is better in this case for better performance ?
2. Is Composite index of 3 columns x,y,z better
or having independent/ seperate indexes on 3 columns x,y,z is better for better performance ?
3. It has been very confusing for me to decide which columns to index, I have indexed most foreignkey columns, is it a good idea ? We do lot of selects and DMLS on most of our tables. Is there any query that I can run and find out if indexes are really being used and if they are improving any performance. I have analyzed and computed my indexes using ANALYZE index index_name validate structure and COMPUTE STATISTICS;

For 1) A Unique Key constraint is a constraint which is enforced by a unique index. The unique index will be automatically created when the constraint is created. The basic functionality provided as far as ensuring uniqueness of the columns defined for the constraint or index is the same.

However, in order to define referential integrity constraints, the Foreign Key must reference either a Primary Key or Unique Key constraint. Constraints can be disabled and enabled without loss of the constraint definition. If you needed to 'disable' the unique indexes, you must drop them--thereby losing the definition.


For 2) It's depend on queries if they required quick search on one or more columns. I have seen usage of compisite keys in ERP application on some big tables. You can also use EXPLAIN PLAN to check and optimize the use of your indexes.

For 3) When to index also depends on the nature of your queires as well. In general, you probably want to index columns after your WHERE clause in your sql. Again also use EXPLAIN PLAN to validate and tune your queries. COMPUTE STATISTICS is also required if you use cost-base optimizer.

Thanks for the quick response. This is exactly what I was looking for.
If you can help me, I do not know how to use explain plan. After I ran utlxplan.sql script what do I do ?
I have ran following:
ANALYZE index index_name validate structure and COMPUTE STATISTICS;
but I don't know how to use it to find out what happens with the index.
We have about 150 indexed columns, do I have to do this for each column- index seperately ?
What if I have a unique composite index on column (a, b, c)
and I use just a or a, b in my where clause ? Does index gets used ?
How do I know if I am doing RULE based or COST based optimization ?
If you have any scripts could you please send it to me:
[email protected]

Thanks a lot
Sonali