nonunique index on unique values
ciapecki
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
Jonathan Lewis
"ciapecki" <ciap...@gmail.com> wrote in message
news:1188285737....@l22g2000prc.googlegroups.com...
The index content will be one byte per row larger,
and there are a few cases where the arithmetic the
optimizer uses changes if you switch a unique index
to non-unique.
Neither is necessarily a problem - but you can always
find a few unlucky cases where performance changes
dramatically because an execution plan has changed.
--
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
yon...@yahoo.com
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
Jonathan Lewis
<yon...@yahoo.com> wrote in message
news:1188401673.6...@q5g2000prf.googlegroups.com...
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.
yon...@yahoo.com
wrote:
> <yong...@yahoo.com> wrote in message
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.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
Jonathan Lewis
<yon...@yahoo.com> wrote in message
news:1188730343.5...@50g2000hsm.googlegroups.com...
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