Re: nonunique index on unique values

<yong321_at_yahoo.com> wrote in message
news:1188401673.656967.243330_at_q5g2000prf.googlegroups.com...
> On Aug 28, 3:22 pm, ciapecki <ciape..._at_gmail.com> wrote:
>> hi,
>>
>> is there a big lost in performance if there is a nonunique index on
>> the column that holds only unique values (like primary key)?
>>
>> thanks
>> chris
>
> If you mean a nonunique index, compared to a unique index, is used to
> enforce a unique constraint, there's performance penalty in terms of
> generated redo when you get error ORA-1 (unique constraint violated).
> See
> http://orafaq.com/papers/oracle_redo_generation_wp.pdf
>
> Yong Huang
>

This prompted me to run a couple of tests on the actual data access.

Big surprise:

    create a table with a load of data
    create an index on a few columns
    select where col1 = 'a' and col2 = 'y' and col3 = 'z';

If the index is unique, you get 'consistent gets - examination' all the way down to the table. With an index with a height of 3, that's 4 gets and 4 latch hits.

With a non-unique index (even with a unique constraint in place). You get examinations on the root and branch blocks, but full gets on the leaf and table blocks - and an extra get on the leaf block for a total of 5 gets, and 8 latch hits.

It's neither realistic nor conclusive, of course. I created a clean table and index. In real-world activity you have to allow for clean-outs, commit time checks, undo blocks and so on; and some of the examinations might have to turn into full gets. But it was an interesting surprise.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html