Sybase Database - Clustered Index

Author

Clustered Index

MJZ

2006-04-06, 3:23 am

I have a table with 12,000,000 tuples each day around 9,000 are added the
Large table has a clustered index while the smaller table has no index. This
process takes about four hours.

I think this is because of the index and the need to create new pages when a
page is full.

Is this a disadvantage of using Clustered Indexes?

Mike

Carl Kayser

2006-04-06, 7:23 am

"MJZ" <u20558@uwe> wrote in message news:5e5a6a1447fae@u
we...
>I have a table with 12,000,000 tuples each day around 9,000 are added the
> Large table has a clustered index while the smaller table has no index.
> This
> process takes about four hours.
>
> I think this is because of the index and the need to create new pages when
> a
> page is full.
>
> Is this a disadvantage of using Clustered Indexes?
>
> Mike

"It depends." Is the table APL or DOL? The inserted data is "random" with
respect to the clustered index? Or the inserts are always at the beginning
or end? BTW, which ASE version and what is the index like? How often is
maintenance done on the table and what fillfactor is used? If APL clustered
is the index unique? If not, then there are overflow pages.

Jack Miller

2006-04-06, 9:23 am

Do you have a choice of dropping the index before inserting your rows?

Following text is copied from
http://www.synametrics.com/Synametr...WPTop10Tips.jsp

*Clustered index* - A clustered index determines the physical order of
data in a table - meaning the actual data is sorted according to the
fields in the index. This is similar to a telephone directory, which
arranges data by last name. There can be only one clustered index per
table. These indexes are particularly efficient on columns that are
often searched for range of values.

Regards.

MJZ wrote:
> I have a table with 12,000,000 tuples each day around 9,000 are added the
> Large table has a clustered index while the smaller table has no index. This
> process takes about four hours.
>
> I think this is because of the index and the need to create new pages when a
> page is full.
>
> Is this a disadvantage of using Clustered Indexes?
>
> Mike

MJZ via DBMonster.com

2006-04-06, 8:23 pm

Carl

I've been in the Sybase team a week, and do not go on a sybase course until
June so I do not understand APL or DOL. The version is 11.0.3.3. The
version is going up to 12.5 in September/October

Mike

Carl Kayser wrote:
>[quoted text clipped - 8 lines]
>
>"It depends." Is the table APL or DOL? The inserted data is "random" with
>respect to the clustered index? Or the inserts are always at the beginning
>or end? BTW, which ASE version and what is the index like? How often is
>maintenance done on the table and what fillfactor is used? If APL clustered
>is the index unique? If not, then there are overflow pages.

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...sybase/200604/1

Frank Hamersley

2006-04-06, 8:23 pm

MJZ via webservertalk.com wrote:
> Carl
>
> I've been in the Sybase team a week, and do not go on a sybase course until
> June so I do not understand APL or DOL. The version is 11.0.3.3. The
> version is going up to 12.5 in September/October

Subject to corrections from more informed types ...

APL - all pages locking - only option in 11.x I think - in this case
clustered index tables are physically stored as implied - i.e. index and
data intertwined in a single structure.

DOL - row locking - new option you will encounter with 12.x - clustered
index whilst apparently behaving like APL is actually stored like a
conventional index (uses page splits etc to reduce IO rework when
inserting rows in the middle of the tree). This reduces the lock
contention but adds an extra layer of indirection. Index scans are
likely to be cheaper tho!

BTW - don't be sucked in to changing to DOL in Sep/Oct just because you
can - without some _serious_ testing.

Cheers, Frank.

>
> Mike
>
> Carl Kayser wrote:
>

MJZ via DBMonster.com

2006-04-06, 8:23 pm

Thanks

I will do some more research, the table maybe dropped as the Business do not
know if they use it, this is being investigated. I just thought I'd look at
some of the long running proccesses to get a handle on what was happening.

Mike

Frank Hamersley wrote:[color=darkred
]
>
>Subject to corrections from more informed types ...
>
>APL - all pages locking - only option in 11.x I think - in this case
>clustered index tables are physically stored as implied - i.e. index and
>data intertwined in a single structure.
>
>DOL - row locking - new option you will encounter with 12.x - clustered
>index whilst apparently behaving like APL is actually stored like a
>conventional index (uses page splits etc to reduce IO rework when
>inserting rows in the middle of the tree). This reduces the lock
>contention but adds an extra layer of indirection. Index scans are
>likely to be cheaper tho!
>
>BTW - don't be sucked in to changing to DOL in Sep/Oct just because you
>can - without some _serious_ testing.
>
>Cheers, Frank.
>
>[quoted text clipped - 7 lines]

--
Message posted via http://www.webservertalk.com

Mark A. Parsons

2006-04-07, 3:23 am

Could you post the following for both tables in question:

sp_helpindex <table_name>
go
sp_spaceused <table_name>,1
go

And if you happen to have it, or can obtain it, a copy of the insert
statement and it's query plan (sp_showplan <spid>,null,null,null).

MJZ wrote:
> I have a table with 12,000,000 tuples each day around 9,000 are added the
> Large table has a clustered index while the smaller table has no index. This
> process takes about four hours.
>
> I think this is because of the index and the need to create new pages when a
> page is full.
>
> Is this a disadvantage of using Clustered Indexes?
>
> Mike

Sponsored Links