URGENT PL: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;
But I have no idea what to look for after doing this ?

Hi

constraints and indexes are not the same, one is to force business rule and the other is the access path to retrieve the data.
as for your secondquestion I am not expert with that mattr butI guess it depends much on you select query(in where clause)
indexing foreign keys is recommended
to find out if indexes are being used you can issue explain plan (you need to run utlxplan.sql before) which shows how the query is retrieving data, using indexes or full table scan
after analyze indexes you check some columns in view user_indexes, I cant remember all but one of them is LEAF
or something like that, it´s to check if b*tree index´s LEAF are balanced.

I don't see much difference between the two (I might be wrong).

Both unique Constraint and Unique index are enforced using an Index.
When oracle reports an unique index violation it will report that as a
constraint violation like : ORA-00001: unique constraint (SCOTT.DEPT_UI1) violated.

When you create a primary key constraint on a table, the uniqueness of the data
is enforced using an unique index. So if you try to create a duplicate
record that violates the uniqueness of the index, you will get
a constraint violation error.

Hope it helps

1) I don't think there is any difference between the two, as the Unique Constraint also creates the Unique Index
(when u define a unique index you normally specify most of the physical attributes, but am not sure how many do that when creating as unique constraint)

2) It really depends on the queries
(if most of ur qry is like col1= and col2= and col3 then, it is better to have composite index. if most of them have "OR" as the condition, single index may be better.

3) Use the explain plan and see whether the particular index is used in the query.
(you may be able to find many other tools, which can also give this details)

I discovered something interesting today:
Unique constraint on (a, b, c) is always indexed but the index on the unique constraint will not be used unless the column a, b, c all are in the where clause of the sql statement, whereas
for unique index on (a, b, c) it will be force the unique value and will be used if a, b, c or a, b or a is in the where clause.

But I still don't know whether the composite index is better or the individual index on each column is better as far as the performance goes.

Thanks to all

Sonali

Well I think there is a important difference between the UK and unique index, however this difference comes into play only with 8i and above with the emerge of "deffered constraints". Preveously UK and PK could not have been implemented without the corresponding underlying *unique* index, so there realy wasn't any difference. But now you can implement UK and PK on *nonunique* index, so that you can temporrarilly deffer constraint.

So with unique index your data must allways be unique, while unique constraint allows you to temporarilly have nonunique values in a column, which sometimes comes very handy.

BTW, what sonaliak mentioned about the requirement that all unique_key columns must be referenced in a where clause for the index to be used is certanly not correct.

HTH,

Here is the link to the Oracle documentation page which says:

Path 4: Single Row by Unique or Primary Key
This access path is available if the statement's WHERE clause uses all columns of a unique or primary key in equality conditions. For composite keys, the equality conditions must be combined with AND operators. To execute the statement, Oracle performs a unique scan on the index on the unique or primary key to retrieve a single rowid, and then accesses the table by that rowid.

So optimizer does not use unique key constraint (index) if all the columns are not in where clause.
Here is the link:
http://technet.oracle.com/docs/produ.../doc_index.htm

If what Sonali says is TRUE (as of now, I do not agree) about the usage of Unique Index on the UNIQUE CONSTRAINT, then
why create a Constraint at all. Just create a Unique Index so that this unique index is used if you use columns (abc) or column (ab) or column(a) in the WHERE clause. Moreover, this unique index would return an error if the uniqueness is violated thus enforcing the unique constraint.

Please correct me, if I am worng.

Quote:

---

Originally posted by sonaliak
Here is the link to the Oracle documentation page which says:

Path 4: Single Row by Unique or Primary Key
This access path is available if the statement's WHERE clause uses all columns of a unique or primary key in equality conditions. For composite keys, the equality conditions must be combined with AND operators. To execute the statement, Oracle performs a unique scan on the index on the unique or primary key to retrieve a single rowid, and then accesses the table by that rowid.

So optimizer does not use unique key constraint (index) if all the columns are not in where clause.
Here is the link:
http://technet.oracle.com/docs/produ.../doc_index.htm

---

Sonaliak,

What documentation you quoted says is totaly correct, but you have to read it very precisely! And what it says about unique constraints is valid for unique indexes as well.

This paragraph is talking about one specific access path: * Single Row by Unique or Primary Key* aka "unique scan". It is selfunderstandable that for unique identification of a single row from composite unique index you must provide values for all indexed columns with "AND" ralations and equality operator. However if you reference a leading column of an unique constraint (or of an underlying index) the optimizer can still use index, however it uses a different access path - *index range scan*, since there might be more than a single row that can satisfiy this condition.

Here is a simple proof:

SQL> set autotrace on explain
SQL> CREATE TABLE test1 (c1 NUMBER, c2 NUMBER,
2 CONSTRAINT uk_test1 UNIQUE (c1, c2));

Table created.

SQL> SELECT * FROM test1 WHERE c1=0;

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'UK_TEST1' (UNIQUE)

jmodic is absolutely right about the index usage. A Unique Index is treated exactly the same whether you made it directly or indirectly through a UK and will be utilitized the same as any other index on the table - based on the usage of a left-leading subset of columns.

As for the other questions, you will be best served by an outside utility. There are utilities out there that can collect all your SQL from your code (VB or whatever) and from your packages, procs and functions and generate EXPLAIN PLANS for all of them. This would tell you whether or not your indexes are being used.

Unfortunately, there isn't anything to tell you (easily) which of the 2 approaches you mentioned are better across all your SQL.

I am currently writing one and will (hopefully) be releasing it with a book I am writing, but until then you're on your own. You will have to gather up the SQL that uses those columns in those tables and see, as a whole, which strategy is better. Run them all with the single index and then with the separate indexes and see which is better.

There is no easy answer on this one (that I know of)

Sorry,

- Chris

Quote:

---

Originally posted by chrisrlong
As for the other questions, you will be best served by an outside utility. There are utilities out there that can collect all your SQL from your code (VB or whatever) and from your packages, procs and functions and generate EXPLAIN PLANS for all of them. This would tell you whether or not your indexes are being used.

Unfortunately, there isn't anything to tell you (easily) which of the 2 approaches you mentioned are better across all your SQL.

I am currently writing one and will (hopefully) be releasing it with a book I am writing, but until then you're on your own. You will have to gather up the SQL that uses those columns in those tables and see, as a whole, which strategy is better. Run them all with the single index and then with the separate indexes and see which is better.

- Chris

---

As Chris mentions, there are a couple of approaches of how to identify which indexes are
used and which are not. I was studying this subject quite extensively some time ago while
I was trying to identify which indexes are totally useless on some overindexed database.
BTW, I think it is much easier to identify a missing index (a phone from an unsatisfied
user will ring sooner or later) than to find one that is siting there but is never used in
access path to the data. I came to the conclusion that there is no perfect way to find
used/unused indexes. The perfect solution would be if Oracle would provide us a
dictionary table with stored explain plans that have been used with selects and DDLs in
the past - something similar as V$SQL_AREA. Maybe we'll be pleasantly surprised with
9i on that field too.....?

Some possible approaches, which all have some serious drawbacks:

1.) Extract all your SQLs from your source code and explain them all. The problem here
is that you could possibly have totally different explain plans if you run them under
different users (same table names/synonyms for different tables,....)
2.) Extract all your SQLs from shared pool periodically (before they can be flushed out)
and analyze them. The same problems as above, you have no idea under which schema
they might have been run.
3.) Trace a representative session or an entire instance for sufficiently long period, tkprof
the trace files and analyze the explain plans. This brings some overhead which might not
be acceptable in an production system
4.) If you want to analyze the usage of one particular index, you might configure your
buffer pool with a small KEEP area, designated only to the inspected index. If after some
time this KEEP BUFFER pool is empty you know this index was never used. This is
viable solution only if you know that only reads are performed on the indexed table, no
writes.
5.) Similarly you can create a separate tablespace and move the index you are inspecting
to that tablespace (no other segments should be inthere), then observe the I/O on that
tablespace's datafile
6.) The approach that seemed the most promising one to me when I was more deep into
this subject (I tried to implement it in combination with 4.)): The fact is that all db blocks
that are read or written goes through the buffer pool and that SYS.V$BH (or more
precisely, SYS.X$BH) provides us with the extensive information about the state of each
block currently in a buffer pool. However all this internal stuff is so complicated and
undocumented (I don't blame Oracle Corp. for this in any way) that I've finally given up.

There are many utilities for all kind of DBA stuff, but I don't know of any one that has
some efficient and exact mechanism for this kind of analysis for used/unused indexes.
Although this seems like a one of a basic requirements this leeds me to believe that all this
is not so trivial and easy to implement after all....