Re: nonunique index on unique values

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 2 Sep 2007 15:47:06 +0100
Message-ID: <BaGdnWSZ6alKUUfbnZ2dnUVZ8qydnZ2d@bt.com>

<yong321_at_yahoo.com> wrote in message
news:1188730343.566384.216500_at_50g2000hsm.googlegroups.com...

> On Sep 1, 1:20 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> wrote:

>> <yong..._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 Lewishttp://jonathanlewis.wordpress.com
>>
>> Author: Cost Based Oracle:
>> Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>>
>> The Co-operative Oracle Users'
>> FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

>
>
> Which version did you test in? I did a simplistic test in 10.2.0.1
> with sqlplus. With a unique index (height=2), no constraint, select *
> from t where x=1 (which goes through the index followed by table
> access by index rowid), both consistent gets and consistent gets -
> examination go up by 3. Changing x=1 to -1 so no row is selected, both
> gets are 2.
>
> With a non-unique index (no constraint), select * from t where x=1 has
> 4 consistent gets and 1 consistent gets - examination. Changing 1 to
> -1, the stats are 2 and 1 respectively.
>
> The test result could be different if using a tool other than sqlplus,
> because I think it may try one more consistent get when everything is
> already fetched.
>
> I didn't check on which blocks the two stats are about. Did you find
> that by looking at x$bh.mode? 3 for consistent gets, 4 for cgets
> examination?
>
> Yong Huang
>

10.2.0.3

Where does your information about the mode come from ? (And did you mean the mode_held column)..

Your figures look about right - but I'm not planning to work this one out in detail.

"Consistent gets - examination" are a subset of "consistent gets", and you have height = 2 rather than height = 3, hence the differences.

The gets vs, examinations is logical inference, assisted by x$kcbsw and x$kcbwh.

-- 
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

Received on Sun Sep 02 2007 - 09:47:06 CDT