Re: index

On Jul 29, 1:40 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> "David BL" <davi..._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?

Yes, which means you should agree with me! For each logical relation there is an associated table.

> > 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.

There are many different ways of implementing a table. One example could involve a heap plus 3 non-clustered indexes. Another could involve a single clustered index plus 2 non-clustered indexes. A third could involve the trans-relational model. I find the idea to assume that a clustered index /is/ a table rather limiting. “table” is more abstract than that.

> 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?

Yes, one of the clustered indexes can be designated as primary as far as row locking is concerned. Received on Tue Jul 29 2008 - 12:52:07 CEST