URGENT PL:difference between unique constraint and unique index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: URGENT PL:difference between unique constraint and unique index

  1. #1
    Join Date
    Jan 2001
    Posts
    318

    Angry

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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.

  3. #3
    Join Date
    Dec 2000
    Posts
    46
    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



  4. #4
    Join Date
    Aug 2000
    Posts
    194
    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)

  5. #5
    Join Date
    Jan 2001
    Posts
    318

    Red face

    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

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    Jan 2001
    Posts
    318

    Arrow

    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

  8. #8
    Join Date
    Sep 2000
    Posts
    155

    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.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [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?

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
  •  



Click Here to Expand Forum to Full Width