-
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 ?
Sonali
-
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=<val> and col2=<val2> and col3 <oper> 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
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,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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:
[url]http://technet.oracle.com/docs/products/oracle8i/doc_index.htm[/url]
Sonali
-
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][i]Originally posted by sonaliak [/i]
[B]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:
[url]http://technet.oracle.com/docs/products/oracle8i/doc_index.htm[/url]
[/B][/QUOTE]
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)
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
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
|