Re: cluster index on a table

Yes, it seems as though the whole tuple is entirely in the index if it is
clustered.

>From :
http://msdn.microsoft.com/en-us/library/ms177484.aspx

" Each index row in the nonclustered index contains the nonclustered key
value and a row locator. This locator points to the data row in the
clustered index or heap having the key value."

That sort of model should work with MVCC and even HOT with the same
restrictions that HOT has now.

On 7/16/09 11:35 AM, "Justin Pitts" <justinpitts(at)gmail(dot)com> wrote:

>
>
> According to the books online
> http://msdn.microsoft.com/en-us/library/ms177443.aspx
> :
>
> "In a clustered index, the leaf nodes contain the data pages of the
> underlying table."
>
>
> Which agrees with your assertion.
>
> From a performance perspective, it DOES work very well. Which is why
> I keep hoping for it to show up in PostgreSQL.
>
> On Jul 16, 2009, at 2:21 PM, Scott Carey wrote:
>
>> I could be wrong, but I think MSSQL only keeps the data specified in
>> the
>> index in the index, and the remaining columns in the data.
>> That is, if there is a clustered index on a table on three columns
>> out of
>> five, those three columns in the index are stored in the index,
>> while the
>> other two are in a data portion. But it has been several years
>> since I
>> worked with that DB.
>>
>> They are certainly storing at least those columns in the index
>> itself. And
>> that feature does work very well from a performance perspective.
>>
>> IOT in Oracle is a huge win in some cases, but a bit more clunky for
>> others
>> than Clustered Indexes in MSSQL. Both are highly useful.
>>
>> On 7/16/09 10:52 AM, "Justin Pitts" <justinpitts(at)gmail(dot)com> wrote:
>>
>>> ISTR that is the approach that MSSQL follows.
>>>
>>>>
>>>> Storing the full tuple in an index and not even having a data only
>>>> page
>>>> would also be an interesting approach to this (and perhaps simpler
>>>> than a
>>>> separate index file and data file if trying to keep the data in the
>>>> order of
>>>> the index).
>>>
>>>
>>
>
>