Re: index

"Jonathan Leffler" <jleffler_at_earthlink.net> wrote in message news:2KydneqLIv5GERPVnZ2dnUVZ_q_inZ2d_at_earthlink.com...
> Brian Selzer wrote:
>
>
>> "Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message
>> news:488de63f$0$4041$9a566e8b_at_news.aliant.net...
>>> aarklon_at_gmail.com wrote:
>>>
>>>> Hi all,
>>>>
>>>> I read the following in a book
>>>>
>>>> 1) we can have several non clustered indexes on a table , but we can
>>>> have only one clustered index per table
>>>>
>>>> 2) index on the primary key can be clustered or non clustered
>>>>
>>>> now my question is
>>>>
>>>> 1) to what extent these statements are true ?
>>> To the extent the statements accurately describe the physical
>>> implementation of a particular dbms, they are true.
>>>
>>> Theoretically, one can have multiple clustered indexes at the cost of
>>> duplicating the data, which of course incurs a cost. Vendors typically
>>> assUme nobody would ever want to incur that cost.
>>
>> This is bunk. Badour is an idiot.
>
> Not convinced of that.
>

I've long since given up on giving him the benefit of the doubt.

>
>> Where there is a clustered index, that index /is/ the table--that is, the
>> table is physically stored using whatever data structure is in use for
>> indexes--probably b-trees. A table that does not have a clustered index,
>> on the other hand, is physically stored as a heap. The leaf nodes of a
>> clustered index /are/ the rows of the table. The leaf nodes of a
>> non-clustered index are either pointers to rows on the heap (where there
>> is no clustered index) or clustered index keys (along with a uniquifier
>> [I didn't coin the term, so don't blame me] if the clustered index isn't
>> also a unique index). Although it is possible to have multiple indexes
>> that cover the entire heading, that is not the same thing as having
>> multiple clustered indexes.
>
> I can't say that this doesn't apply to some DBMS; however, it most
> certainly does not apply to every DBMS, regardless of what you might like
> to think. As such, it makes your 'bunk' assertion into an over-statement.
> At best.
>

In the context of the original poster's question, it applies. It may be that what is called a clustered index in one implementation is something completely different from what is called a clustered index in the implementation underlying the question, but obviously Badour either doesn't recognize or doesn't acknowledge the difference. Either way, it makes him appear to be clueless on the subject.

> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
> Guardian of DBD::Informix v2008.0229 -- http://dbi.perl.org/
>
> publictimestamp.org/ptb/PTB-3845 tiger128 2008-07-29 03:00:03
> 5D10F27324B7DB711BB222E4CC62BE7A
Received on Tue Jul 29 2008 - 08:04:12 CEST