SEO

#1 (**permalink**) 04-08-2008, 02:39 PM

Performance and Tuning Guide
Chapter 8 - Indexing for Performance --> Choosing Indexes --> Index keys
and logical keys ( page 176 )

"A common error is to create the clustered index for a table on the
primary key, even though it is never used for range queries or ordering
result sets."

However, when you create a primary key constraint on a table, the
default is to create a clustered index:

Reference Manual
Chapter 2 - alter table ( page 270 )

clustered | nonclustered
"specifies that the index created by a unique or primary key constraint
is a clustered or nonclustered index. clustered is the default (unless a
clustered index already exists for the table) for primary key
constraints; nonclustered is the default for unique constraints. There
can be only one clustered index per table. See create index for more
information."

Arent't the 2 statements contradicting ?

#2 (**permalink**) 04-08-2008, 02:39 PM

John Salvo wrote:
>
> Performance and Tuning Guide
> Chapter 8 - Indexing for Performance --> Choosing Indexes --> Index keys
> and logical keys ( page 176 )
>
> "A common error is to create the clustered index for a table on the
> primary key, even though it is never used for range queries or ordering
> result sets."

Its not really clear but its telling you to put the clustered index
on the columns you would most frequently use for range queries or
ordered result sets. That's where the clustered index is most
effective. For a single keyed access or single row access, a non-
clustered index is OK. Note that this only really applies to APL
tables (since the concept of clustered isn't applicable to DOL
tables).

> However, when you create a primary key constraint on a table, the
> default is to create a clustered index:
>
> Reference Manual
> Chapter 2 - alter table ( page 270 )
>
> clustered | nonclustered
> "specifies that the index created by a unique or primary key constraint
> is a clustered or nonclustered index. clustered is the default (unless a
> clustered index already exists for the table) for primary key
> constraints; nonclustered is the default for unique constraints. There
> can be only one clustered index per table. See create index for more
> information."
>
> Arent't the 2 statements contradicting ?

Not really. The primary key would most likely be unique but it
needed be clustered.

-am © 2003

#3 (**permalink**) 04-08-2008, 02:39 PM

Joseph Weinstein wrote:
>
> John Salvo wrote:
>
>>
>>Arent't the 2 statements contradicting ?
>
>
> Not really. The first warning has to do with the circumstance
> where your queries will never need to order your returns by primary
> key, or select a range of rows defined by a range of primary keys.
> In that case, there is a risk of some needless overhead if you insert
> into the table in an order that is random with respect to the primary
> key. (what you have printed of this warning seems overblown)

Okay ... so I know of a few tables where the application that inserts
into that row does not necessarily insert the rows so that the primary
key is in order. In that case, a non-clustered primary key should be used.

> The second just gives you the choice of clustered or non-clustered
> primary index, with the default being clustered. For most situations,
> the clustered index *is* useful.
>
> Joe Weinstein at BEA
>

#4 (**permalink**) 04-08-2008, 02:39 PM

Anthony Mandic wrote:
> John Salvo wrote:
>
>>Performance and Tuning Guide
>>Chapter 8 - Indexing for Performance --> Choosing Indexes --> Index keys
>>and logical keys ( page 176 )
>>
>>"A common error is to create the clustered index for a table on the
>>primary key, even though it is never used for range queries or ordering
>>result sets."
>
>
> Its not really clear but its telling you to put the clustered index
> on the columns you would most frequently use for range queries or
> ordered result sets. That's where the clustered index is most
> effective. For a single keyed access or single row access, a non-
> clustered index is OK. Note that this only really applies to APL
> tables (since the concept of clustered isn't applicable to DOL
> tables).
>

Care to elaborate ?

Chapter 9, page 190 of Performance and Tuning Guide -- "Types on
Indexes" shows that clustered index are applicable to DOL tables as well.

#5 (**permalink**) 04-08-2008, 02:39 PM

John Salvo wrote:

> > Note that this only really applies to APL
> > tables (since the concept of clustered isn't applicable to DOL
> > tables).
>
> Care to elaborate ?
>
> Chapter 9, page 190 of Performance and Tuning Guide -- "Types on
> Indexes" shows that clustered index are applicable to DOL tables as well.

You can still create them but they are supposed to be functionally
the same (since DOL storage is different to APL, the B-Tree layout
is deprecated hence clustered becomes a no op). However, I once
found this to not quite be the case. The manuals should give you
the details (perhaps in the New Functionality manual for ASE 11.9.2
if not in the SAG or P&T Guide).

-am © 2003

#6 (**permalink**) 04-08-2008, 02:50 PM

Joseph Weinstein <joe.remove_this@bea.com.remove_this> writes:

>>"A common error is to create the clustered index for a table on the
>>primary key, even though it is never used for range queries or ordering
>>result sets."

>The first warning has to do with the circumstance where your queries
>will never need to order your returns by primary key, or select a
>range of rows defined by a range of primary keys. In that case,
>there is a risk of some needless overhead if you insert into the
>table in an order that is random with respect to the primary key.

I thought the problem was if you inserted in sequential primary key
order - this can create a hot spot at the last page of the table and
hurt concurrency. Inserting in random order wrt the clustered index,
however, is okay, provided the fill factor isn't set too high. Is
that correct?

--
Ed Avis <ed@membled.com>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Clustered Indexes --- for primary keys or not