Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Unanswered: is there any performance difference between unique and no unique index

    Hi,

    We are working with both databases Oracle and MS-SQL and we have an open channel to MS-SQL server core team.

    We got the word from them that there is a difference between unique and no unique index from the optimizer perspective, i.e. the optimizer knowing that the index is unique may choose a more suitable plan for running a specific query, we're still trying to find a specific example.

    Anyway, since we do not have an open channel to Oracle core, I thought maybe one of you guys knows something about Oracle optimizer behavior on this issue.

    I am interested verified answers from certified sources and not suggestions or thoughts.

    Thanks in advanced,


    Tal (otal@mercury.co.il).

  2. #2
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    " verified answers from certified sources "

    " not suggestions or thoughts. "

    Why post this in an open forum anyway then?
    I will give you my thoughts anyway (for what is is worth)

    Column contents will decide whether you can define a unique index, ultimately the SQL conditions will determine what is most effective.
    Accessing the greater part of a table can be even faster by doing a full table scan. There is no ultimate theory or truth here, just going for performance testing / using explain plan to get as much insight as possible...
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  3. #3
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73
    Originally posted by blom0344
    " verified answers from certified sources "

    " not suggestions or thoughts. "

    Why post this in an open forum anyway then?
    I will give you my thoughts anyway (for what is is worth)

    Column contents will decide whether you can define a unique index, ultimately the SQL conditions will determine what is most effective.
    Accessing the greater part of a table can be even faster by doing a full table scan. There is no ultimate theory or truth here, just going for performance testing / using explain plan to get as much insight as possible...

    Did not meant to offend.

    Thanks.

    Tal.

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: is there any performance difference between unique and no unique index

    Originally posted by tal_olier
    Hi,

    We are working with both databases Oracle and MS-SQL and we have an open channel to MS-SQL server core team.

    We got the word from them that there is a difference between unique and no unique index from the optimizer perspective, i.e. the optimizer knowing that the index is unique may choose a more suitable plan for running a specific query, we're still trying to find a specific example.
    Tal (otal@mercury.co.il).
    Yes because a unique index has a better selectivity and since selectivity is one of the things the optimizer looks at... But there's more that that. You can't say for sure that it's always better... Depends on a lot of things... For instance, if your table has 2 rows, neither of these 2 indexes will be used since a table scan will be way more faster. But in 99.99% of the cases, a unique index is better than a "not unique" one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •