Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

data/index clustering

6 posts in Performance and Tuning Last posting was on 2012-09-05 08:36:19.0Z
vtpcnk Posted on 2012-09-03 03:26:50.0Z
Sender: 66df.50441b97.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.performance+tuning
Subject: data/index clustering
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5044237a.67af.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 2 Sep 2012 20:26:50 -0700
X-Trace: forums-1-dub 1346642810 172.20.134.41 (2 Sep 2012 20:26:50 -0700)
X-Original-Trace: 2 Sep 2012 20:26:50 -0700, 172.20.134.41
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13236
Article PK: 155111

when a table is created an extent is allocated to it.

let us suppose right after the table creation, an index is
also created on the table.

so when data is added, the index pages are created from the
available extent, right? and data pages and index pages are
seperate?

if this is true, is there any specific pattern the ASE uses
to create index pages? 1 page data and 1 page index and so
on ...?

and is there any difference in the above method when it
comes to clustered and non clustered indexes? and between in
APL/DOL/DRL tables?

or is my understanding totally wrong and index pages are
created on totally different extents?

btw i understand that with user defined segments one can
have specific space dedicated to indexes - so my questions
about applies to tables which do not use user defined
segments.

appreciate the insights.


Rob V Posted on 2012-09-03 07:19:58.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:15.0) Gecko/20120824 Thunderbird/15.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: data/index clustering
References: <5044237a.67af.1681692777@sybase.com>
In-Reply-To: <5044237a.67af.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50445a1e$1@forums-1-dub>
Date: 3 Sep 2012 00:19:58 -0700
X-Trace: forums-1-dub 1346656798 172.20.134.152 (3 Sep 2012 00:19:58 -0700)
X-Original-Trace: 3 Sep 2012 00:19:58 -0700, vip152.sybase.com
Lines: 63
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13238
Article PK: 91735


On 03-Sep-2012 05:26, vtpcnk wrote:
> when a table is created an extent is allocated to it.
>
> let us suppose right after the table creation, an index is
> also created on the table.
>
> so when data is added, the index pages are created from the
> available extent, right? and data pages and index pages are
> seperate?
>
> if this is true, is there any specific pattern the ASE uses
> to create index pages? 1 page data and 1 page index and so
> on ...?
>
> and is there any difference in the above method when it
> comes to clustered and non clustered indexes? and between in
> APL/DOL/DRL tables?
>
> or is my understanding totally wrong and index pages are
> created on totally different extents?
>
> btw i understand that with user defined segments one can
> have specific space dedicated to indexes - so my questions
> about applies to tables which do not use user defined
> segments.
>
> appreciate the insights.
>

From a storage perspective, an index is a separate object and its pages
ar enot mixed with the table's data pages or other indexes. When an
index is created, an extent is allocated for that index even when the
table is empty.
So when you create a empty table and an index, two extents are
allocated. When you create an empty table and 5 indexes, 6 extents are
allocated.

Segments have nothing to do with this: they just determine on which
*device* extents can be allcated for tables/indexes.

This is all described in detail in the system admin guide (for segments)
and the performance & tuning guide (for storage basics such as space
allocation,e xtents, and allocation units).


--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


vtpcnk Posted on 2012-09-03 10:20:16.0Z
Sender: 7b90.504481ca.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: data/index clustering
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50448460.7be9.1681692777@sybase.com>
References: <50445a1e$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 3 Sep 2012 03:20:16 -0700
X-Trace: forums-1-dub 1346667616 172.20.134.41 (3 Sep 2012 03:20:16 -0700)
X-Original-Trace: 3 Sep 2012 03:20:16 -0700, 172.20.134.41
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13240
Article PK: 91738

rob, thanks so much for the clarification.

what i was actually getting at is trying to understand what
data page cluster ratio for index is. what the relationship
*in terms of clustering* between data pages and index pages?

appreciate the insights.

> From a storage perspective, an index is a separate object
> and its pages ar enot mixed with the table's data pages
> or other indexes. When an index is created, an extent is
> allocated for that index even when the table is empty.
> So when you create a empty table and an index, two extents
> are allocated. When you create an empty table and 5
> indexes, 6 extents are allocated.


Rob V Posted on 2012-09-03 15:24:07.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:15.0) Gecko/20120824 Thunderbird/15.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: data/index clustering
References: <50445a1e$1@forums-1-dub> <50448460.7be9.1681692777@sybase.com>
In-Reply-To: <50448460.7be9.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5044cb97$1@forums-1-dub>
Date: 3 Sep 2012 08:24:07 -0700
X-Trace: forums-1-dub 1346685847 172.20.134.152 (3 Sep 2012 08:24:07 -0700)
X-Original-Trace: 3 Sep 2012 08:24:07 -0700, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13243
Article PK: 91739


On 03-Sep-2012 12:20, vtpcnk wrote:
> rob, thanks so much for the clarification.
>
> what i was actually getting at is trying to understand what
> data page cluster ratio for index is. what the relationship
> *in terms of clustering* between data pages and index pages?
>
> appreciate the insights.
>
>> From a storage perspective, an index is a separate object
>> and its pages ar enot mixed with the table's data pages
>> or other indexes. When an index is created, an extent is
>> allocated for that index even when the table is empty.
>> So when you create a empty table and an index, two extents
>> are allocated. When you create an empty table and 5
>> indexes, 6 extents are allocated.

'cluster' in 'cluster ratio' is fully unrelated to 'cluster' in
clustered index, despite using the same term.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


vtpcnk Posted on 2012-09-04 04:18:00.0Z
Sender: 12d3.5045783f.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: data/index clustering
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <504580f8.1339.1681692777@sybase.com>
References: <5044cb97$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 3 Sep 2012 21:18:00 -0700
X-Trace: forums-1-dub 1346732280 172.20.134.41 (3 Sep 2012 21:18:00 -0700)
X-Original-Trace: 3 Sep 2012 21:18:00 -0700, 172.20.134.41
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13245
Article PK: 155112


> 'cluster' in 'cluster ratio' is fully unrelated to
> 'cluster' in clustered index, despite using the same
> term.

agreed that, for a apl clustered index, it is more about
physical sorting of the data pages. while for cluster ratio,
it is about packing data together as closely as possible
within the extents.

but some questions regarding cluster ratio :

1. are pages in extents necessarily contiguous? or can pages
in a single extent be seperated by other pages (of another
object)?

for example pls check this diagram :
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20023_1251/html/optimizer/optimizer93.htm

where pages of an object runs from page 2 to page 15 - which
is only 14 pages. so are the other two pages continued from
page 24 (and 25)?

2. cluster ratio is about effectiveness of large i/o. do
table scans always use 16k i/o? but what is no 16k buffer
pool is configured?

3. if a table scan or a large i/o (16k) scan with an index
happens to data as per the above diagram link, so will it
first grab (2k) pages from page 2 to page 9, and then pages
10 to 17 (where pages 16 and 17 do not belong to the object)
and then grab pages 18 to 25 (assuming it is looking for
data in only the first two extents). so it would need three
16k i/o to get the first two extents? in this dispersal of
data pages (with 2 pages not being contiguous), will the
ratio still be the optimal 1.0?

appreciate the insights.


Rob V Posted on 2012-09-05 08:36:19.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:15.0) Gecko/20120824 Thunderbird/15.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: data/index clustering
References: <5044cb97$1@forums-1-dub> <504580f8.1339.1681692777@sybase.com>
In-Reply-To: <504580f8.1339.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50470f03$1@forums-1-dub>
Date: 5 Sep 2012 01:36:19 -0700
X-Trace: forums-1-dub 1346834179 172.20.134.152 (5 Sep 2012 01:36:19 -0700)
X-Original-Trace: 5 Sep 2012 01:36:19 -0700, vip152.sybase.com
Lines: 80
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13247
Article PK: 91744


On 04-Sep-2012 06:18, vtpcnk wrote:
>> 'cluster' in 'cluster ratio' is fully unrelated to
>> 'cluster' in clustered index, despite using the same
>> term.
>
> agreed that, for a apl clustered index, it is more about
> physical sorting of the data pages. while for cluster ratio,
> it is about packing data together as closely as possible
> within the extents.
>
> but some questions regarding cluster ratio :
>
> 1. are pages in extents necessarily contiguous? or can pages
> in a single extent be seperated by other pages (of another
> object)?
>
> for example pls check this diagram :
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20023_1251/html/optimizer/optimizer93.htm
>
> where pages of an object runs from page 2 to page 15 - which
> is only 14 pages. so are the other two pages continued from
> page 24 (and 25)?
>
> 2. cluster ratio is about effectiveness of large i/o. do
> table scans always use 16k i/o? but what is no 16k buffer
> pool is configured?
>
> 3. if a table scan or a large i/o (16k) scan with an index
> happens to data as per the above diagram link, so will it
> first grab (2k) pages from page 2 to page 9, and then pages
> 10 to 17 (where pages 16 and 17 do not belong to the object)
> and then grab pages 18 to 25 (assuming it is looking for
> data in only the first two extents). so it would need three
> 16k i/o to get the first two extents? in this dispersal of
> data pages (with 2 pages not being contiguous), will the
> ratio still be the optimal 1.0?
>
> appreciate the insights.
>

Pages in an extent *always* belong to the same object (except for
allocation pages, see below). Not all pages may be used: for example the
first 3 pages of an extent can contain rows whereas the last 5 are
empty. You should not make any assumptions about which pages can be used
and which ones can be free in an extent.

An extent always has 8 pages. For some extents (the first one on an
allocation unit), the first page is an allocation page which cannot be
used by the object that has allocated the extent (in which case there
are 7 pages left to use). Also, some pages cannot contain rows, but
contain space allocation tracking info for the object, these are the
'OAM' pages. So it is very well possible to have an extent with only 6
pages usable for data if there is an allocation page and an OAM page.

Table scans or index scans try to use the largest buffer pool available.
If you do not create a 16KB pool, then it obviously cannot be used.

When the data chain is fragmented, ASE will fall back to using 2KB I/O
as soon as it finds out. Note that the linked chain applies only to APL
tables, DOL tables don't have linked data pages (except for one special
case which doesn;t matter here).

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------