Re: Indexes and Logical design

> "David Cressey" <david.cressey_at_earthlink.net> wrote in message
> news:St2Ve.10432$9i4.2220_at_newsread2.news.atl.earthlink.net...
> [...]
>>> > Not true. In DEC Rdb/VMS a unique constraint can be declared without
>>> > creating an index, if you want to.
>>>
>
> It's unclear to what part of my first response to your original message "Not
> true" refers.
>
> Earlier, I wrote this: "One can easily imagine a unique constraint
> enforcement without any index whatsoever although such enforcement would be
> impractical". I did not claim that no database could implement a unique
> constraint without an index did I.
>
> It's interesting to note that by mentioning Rgb's ability to create a unique
> constraint without an index you actually reinforce my argument that the
> index is just a performance tool.

Indeed.

There are two "possibly unexpected" ways to implement "UNIQUE":

1. Don't bother with any extra data structure; this would, of course, mean some form of sequential scan across the whole thing to verify uniqueness.
2. Use a hash table to allow access to values. This provides O(1) access time, but with the demerit that this structure is unordered, and therefore not usable for any other purposes...

>>> For toy tables probably. In 'real world', no.
>>
>> In the real world, yes.
>
> It appears that in the "real world" you model the tables you want to have
> unique constraints on are either small, or you do not care much about
> concurrency when accessing such tables, or both. As is well known,
> accessing a table without an index will lead to a full table scan thereby
> impacting performance if the table is larger than a couple dozen
> rows.

Right.

> Besides, sequential retrieval for a read/write transaction requires locks on
> the entire table, which results in coarser locks and degraded concurrency.

Not necessarily; alternative mechanisms exist, and are actively used.

Pretty well any of the database systems still undergoing active development offer some variation of MultiVersion Concurrency Control (MVCC) where updates lead to creating new versions of tuples, which allows the reads, at least, to not require any locks...

-- 
(reverse (concatenate 'string "moc.liamg" "_at_" "enworbbc"))
http://cbbrowne.com/info/
If we were meant to fly, we wouldn't keep losing our luggage.