Re: index


"Jonathan Leffler" <jleffler@xxxxxxxxxxxxx> wrote in message
news:2KydneqLIv5GERPVnZ2dnUVZ_q_inZ2d@xxxxxxxxxxxxxxxx
Brian Selzer wrote:


"Bob Badour" <bbadour@xxxxxxxxxxxxxxxx> wrote in message
news:488de63f$0$4041$9a566e8b@xxxxxxxxxxxxxxxxxx
aarklon@xxxxxxxxx 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@xxxxxxxxxxxxx, jleffler@xxxxxxxxxx
Guardian of DBD::Informix v2008.0229 -- http://dbi.perl.org/

publictimestamp.org/ptb/PTB-3845 tiger128 2008-07-29 03:00:03
5D10F27324B7DB711BB222E4CC62BE7A


.

Relevant Pages

  • Re: index
    ... have only one clustered index per table ... Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. ...
    (comp.databases.theory)
  • Re: index
    ... It is my understanding that a table is the physical analog of a ... you're ignoring the fact that a clustered index ... obtains a lock on a row in ci#1 and then attempts to obtain a lock on ... one of the clustered indexes can be designated as primary as far ...
    (comp.databases.theory)
  • Re: index
    ... Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. ... A table that does not have a clustered index, on the other hand, is physically stored as a heap. ... Although it is possible to have multiple indexes that cover the entire heading, that is not the same thing as having multiple clustered indexes. ...
    (comp.databases.theory)
  • Re: index
    ... which of course incurs a cost. ... You can't have two clustered indexes ... you're ignoring the fact that a clustered index /is/ a ... obtains a lock on a row in ci#1 and then attempts to obtain a lock on the ...
    (comp.databases.theory)
  • Re: index
    ... we can have several non clustered indexes on a table, ... have only one clustered index per table ... one can have multiple clustered indexes at the cost of ... which of course incurs a cost. ...
    (comp.databases.theory)