Unique vs Nonunique indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Unique vs Nonunique indexes

  1. #1
    Join Date
    Nov 2000
    Posts
    344
    Hi everyone -

    I've read that unique indexes perform better than nonunique indexes.

    Now, in an app where I do not have the source code, I have found some indexes that are classified as 'nonunique', but every row in the index is indeed unique.

    Can I expect better performance out of this index if I drop it and recreate it as unique? Or does it perform as well as a unique index now because it actually is unique even though it is not defined in the data dictionary as such.

    The index is quite large so I don't want to just 'do it and see'.

    thanks for your help!

    -John

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    ARe u using 8.1.6 , then check whether the primary key constraint or unique constraint was created deferrable .
    If yes, u need to find out the reason from developers.

    The reason unique performs better is because it uses less space , but now in 8.1.6 u can compress the indexes.

  3. #3
    Join Date
    Feb 2001
    Posts
    295
    It's strange that you found an non-unique index over a unique column. When you define a unique (or primary key) constraint, Oracle implicitly creates an unique index with the same name of the constraint. The non-unique indexes you found probably were related to another columns (or combination of columns), since it's impossible to have more than one index on the same column.

    Just an add, Oracle reccomends that you do NOT create unique indexes explicitly, and let the work to be done automatically. But, if you do it, Oracle will use default settings to the index, what could be a bad thing. So:

    1- Create Unique index with particular settings;
    2- Create Unique constaints on table.

    If you swap these steps, you won't be able to customize your index.


    Adriano.

  4. #4
    Join Date
    Nov 2000
    Posts
    344
    Hi Again,

    Sorry, maybe my first post was misleading.

    The index does not have an associated constraint of any kind. I know that the index is considered nonunique because that's what it says in DBA_INDEXES. I know what the data in that index actully is unique, though, because after an ANALYZE..COMPUTE on that index, the number of DISTINCT KEYS is the same as the number of rows in the table....

    OK, if it will consume less space as a unique index that is a good reason to change it. How much do you think it will shrink? It is a 3 column index with about 3 million distinct keys. The column types all NUMBER type (with no specified precision)

    Thanks!
    John

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    This may be a case of the designers will allow duplicate values in this column, but that's not the way you are using it. By putting a unique index on this field, you may be putting a constraint on the application that was not intended. Since it's a third-party app, I would just leave it alone...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2000
    Posts
    344
    Incase anyone is curious, I tried it in a development environment to see what the difference would be.

    As a Non-unique index, it occupied 90Mb.
    as a Unique index, it occupied 86Mb.

    So we're looking at about 4 or 5 percent.

    this is a 3 column index - columns of type NUMBER

    -John

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