Uniqueness checking overhead

[jefftyzzer]

Friends:

Does anyone have an informed idea of how much typical/average/ball-
park overhead is added to an UPDATE or INSERT to a table when that
table has a unique index defined on a column or set of columns that is
present in the UPDATE or INSERT?

I expect the overhead to be very low because of fast disk drives,
index buffering, and the fact that the index entries are already
ordered, but am wondering if we're talking about 1ms, 2, 5, etc., and
what factors other than those I mention above will affect this
overhead cost.

Thanks,

--Jeff

[Mark A]

Re: Uniqueness checking overhead

"jefftyzzer " <jefftyzzer@sbc global.netwrote in message
news:656c4a41-5cc0-4332-9e0f-321a8fe2637f@d1 0g2000pra.googl egroups.com...

Friends:
>
Does anyone have an informed idea of how much typical/average/ball-
park overhead is added to an UPDATE or INSERT to a table when that
table has a unique index defined on a column or set of columns that is
present in the UPDATE or INSERT?
>
I expect the overhead to be very low because of fast disk drives,
index buffering, and the fact that the index entries are already
ordered, but am wondering if we're talking about 1ms, 2, 5, etc., and
what factors other than those I mention above will affect this
overhead cost.
>
Thanks,
>
--Jeff

If you are talking about a column that would be indexed anyway, but not
necessarily a unique index, then probably almost no difference.

You could set up a test yourself (probably need at least 100 executions) and
do a snapshot for dynamic SQL to see the execution times of the various
scenarios. But it does depend on your bufferpool hit ratio, which depends on
how you set up your tablespaces and bufferpools, and how much memory you
have for them.