Re: index
Date: Tue, 29 Jul 2008 01:40:17 -0400
Message-ID: <63yjk.19343$N87.14598_at_nlpi068.nbdc.sbc.com>
"David BL" <davidbl_at_iinet.net.au> wrote in message
news:eea88a48-1dbe-4305-ac16-ae8e2c384580_at_y38g2000hsy.googlegroups.com...
> 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.
>
It is my understanding that a table is the physical analog of a relation. Is that not so?
>
> 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.
Good luck with that: you're ignoring the fact that a clustered index /is/ a table. Is one of the clustered indexes designated as primary? If not, then how will you avoid deadlocks? Suppose that you have one transaction that obtains a lock on a row in ci#1 and then attempts to obtain a lock on the corresponding row in ci#2, and you have at the same time a transaction that obtains a lock on a row in ci#2 and then attempts to obtain a lock on the corresponding row in ci#1. Do you have to have another common physical structure for the logical table to manage locks amongst the indexes? Received on Tue Jul 29 2008 - 07:40:17 CEST