Re: index

"David BL" <davidbl_at_iinet.net.au> wrote in message news:f9c11064-fb5a-4aff-8d38-cbb55801213e_at_m36g2000hse.googlegroups.com...
> On Jul 29, 4:24 am, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> > "Bob Badour" <bbad..._at_pei.sympatico.ca> wrote in message
> >
> > news:488de63f$0$4041$9a566e8b_at_news.aliant.net...
> >
> >
> >
> >
> >
> > > aark..._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.
> >
>
> > 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).
>
> Agreed
>
> > Although it is possible to have multiple
> > indexes that cover the entire heading, that is not the same thing as
> > having
> > multiple clustered indexes.
>
> Why?
>
> As you said a non-clustered index is characterised by some indirection
> to the actual rows that are stored elsewhere. If we physically store
> multiple indexes and each index records all the data in the table in
> the manner of a clustered index, then don’t we by definition have
> multiple clustered indexes?
>

A clustered index is a table recorded in a particular way. If there is more than one clustered index, then there is more than one table. I would argue against storing the exact same information in more than one table.

> Physical duplication of data can be appropriate to increase read
> performance at the expense of write performance. Indeed any secondary
> index is a form of redundancy that hurts write performance.
>

Boosting read performance can be accomplished just as well with a covering non-clustered index as with a clustered index. Nothing requires the optimizer to dereference the pointer or look up the row in the clustered index if the information is already available in the index key. On the other hand, for updates, it is imperative that there be only one primary location for the data in the table--a root of the tree if you will--especially where locking is used to serialize updates.

> In specialised applications there could be considerable merit in
> multiple clustered indexes.
Received on Tue Jul 29 2008 - 04:45:29 CEST