Re: Performance Issue

Hi Susan,

    What you say is true of hash indexes -- but not of the default b-tree indexes. The default indexes are maintained as balanced trees. Inserting into a b-tree index will take the same amount of time regardless of whether or not the index is unique.

    B-tree indexes, and their derivatives, are based on the AVL algorithm which minimizes the search path through the index by adjusting the index so that it is always balanced, i.e., all search paths through the index are the same length + or - one.

regards

Jerry

Susan wrote in message <35ef56fd.5111986_at_news.dancris.com>...
>Unique indexes ultimately take longer when massive updates are being
>done, because they need to check the entire index for a non-match
>before the record can be commited, else they error. On extremely
>large tables, that might make a difference.
>
>Unique indexes, of course will speed up the performance of "access" or
>simply selecting data. That's what they're used for. Indexes are
>essentially a hash table that the db engine uses to directly address
>data (on disk or in memory). Another thing ... if the data fields you
>are selecting are all part of an index (in the same order, that's
>important), then the db engine doesn't even have to go to the table to
>get the data ... it's already in the index it just found.
>
>- sooz
>
>On Thu, 3 Sep 1998 22:42:08 -0400, "Roman Gelfand"
><rgelfand_at_masmid.com> wrote:
>
>>Could somebody tell me if unique as opposed to non-unique index will slow
>>down updates and/or access and why?
>>
>>
>>
>
Received on Fri Sep 04 1998 - 07:45:14 CDT