Re: index
Date: Mon, 28 Jul 2008 20:21:50 -0700
Message-ID: <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.
> 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.
-- 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 5D10F27324B7DB711BB222E4CC62BE7AReceived on Tue Jul 29 2008 - 05:21:50 CEST