Re: nonunique index on unique values

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 Received on Sun Sep 02 2007 - 05:52:23 CDT