Re: index

"David BL" <davidbl_at_iinet.net.au> wrote in message news:1032f0f7-ceff-4318-86d9-1663356c1aa3_at_c58g2000hsc.googlegroups.com...

[snip]

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

I don't follow.

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

How about: a heap is one way to organize a table; a clustered index is another.

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

What then would be the benefit of having two clustered indexes over one clustered index and a covering non-clustered index? Received on Tue Jul 29 2008 - 14:43:45 CEST