Re: index

On Jul 29, 10:17 am, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> "Bob Badour" <bbad..._at_pei.sympatico.ca> wrote in message
>
> news:488e5d9a$0$4041$9a566e8b_at_news.aliant.net...
>
>
>
>
>
> > Evan Keel wrote:
>
> >> "Brian Selzer" <br..._at_selzer-software.com> wrote in message
> >>news:%Vpjk.34162$ZE5.24396_at_nlpi061.nbdc.sbc.com...
>
> >>>"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.
>
> >>>This is bunk. Badour is an idiot. 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.
>
> > Reading is fundamental.http://rif.org/get-involved/alumni/retro.mspx
>
> > If one stores the data twice, one can have two clustered indexes. Hence
> > "duplicating the data" and "incurs a cost"...
>
> If one stores the data twice in two clustered indexes, then one stores the
> same data in two different tables. You can't have two clustered indexes on
> the same table.

You’re assuming a table is a physical construct. Actually what makes a table a table is defined at the level of the database schema.

A DBMS that physcally stores two clustered indexes on the same data is by definition abstracting the physical so that it appears logically as a single table. Updates to the one logical table will update both clustered indexes. This is analogous to many other examples of physical redundancy to increase read performance. Received on Tue Jul 29 2008 - 04:48:25 CEST