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.

Placement Indexes

4 posts in General Discussion Last posting was on 2010-10-15 17:20:00.0Z
RGS Posted on 2010-10-13 03:00:56.0Z
Sender: 7951.4cb51ebb.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Placement Indexes
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cb520e8.798d.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 12 Oct 2010 20:00:56 -0700
X-Trace: forums-1-dub 1286938856 10.22.241.41 (12 Oct 2010 20:00:56 -0700)
X-Original-Trace: 12 Oct 2010 20:00:56 -0700, 10.22.241.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29648
Article PK: 78878

Hi!

Placement indexes are CLUSTERED INDEX created on tables with
DOL locks

I read that a Placement index doesn't guarantee that the
data are ALWAYS ordering by the key of the index and I read
that really a Placement index has pointers to the data

Then, what is the advantage of create a PLACEMENT INDEX
besides to create a normal NUNCLUSTERED INDEX on a DOL
table?

Thanks!


Cory Sane [TeamSybase] Posted on 2010-10-13 04:01:51.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4cb520e8.798d.1681692777@sybase.com>
In-Reply-To: <4cb520e8.798d.1681692777@sybase.com>
Subject: Re: Placement Indexes
Lines: 48
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18263
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cb52f2f$1@forums-1-dub>
Date: 12 Oct 2010 21:01:51 -0700
X-Trace: forums-1-dub 1286942511 10.22.241.152 (12 Oct 2010 21:01:51 -0700)
X-Original-Trace: 12 Oct 2010 21:01:51 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29649
Article PK: 78875

Here is Jeff Tallman's response in May'09...
See the thread..."DOL and APL clustered inedes"

A very common misunderstanding. The context of that simplistic
description of "same structure" is that it has separate leaf nodes -
much like a non-clustered index - but that is where the similarity ENDS.
A clustered index on a DOL table is STILL a placement index and
therefore behaves EXACTLY as a clustered index on APL. The only
difference (and all of this is documented) that in the "placement", we
do not escalate locks to strictly enforce order - so the row may be put
at the end of the page, or in the middle of page - where there is space
- or on a new page in same extent - the goal is to place the row NEAR
where it needs to go. If we tried to enforce strict data clustering, we
would have to escalate to page locks and re-write the page(s) involved,
do page splits, etc.

The point - unlike what some have stated in news groups in the past - a
clustered index on a DOL table is STILL a clustered index and a DOL
table is NOT a heap table. Sometimes you have to work with a large
enough table to see the behavior as a table with only a few rows is
likely still working within the same extents.....


Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database


--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"RGS" wrote in message news:4cb520e8.798d.1681692777@sybase.com...
> Hi!
>
> Placement indexes are CLUSTERED INDEX created on tables with
> DOL locks
>
> I read that a Placement index doesn't guarantee that the
> data are ALWAYS ordering by the key of the index and I read
> that really a Placement index has pointers to the data
>
> Then, what is the advantage of create a PLACEMENT INDEX
> besides to create a normal NUNCLUSTERED INDEX on a DOL
> table?
>
> Thanks!


Rob V [ Sybase ] Posted on 2010-10-15 17:20:00.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Placement Indexes
References: <4cb520e8.798d.1681692777@sybase.com>
In-Reply-To: <4cb520e8.798d.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: <4cb88d40$1@forums-1-dub>
Date: 15 Oct 2010 10:20:00 -0700
X-Trace: forums-1-dub 1287163200 10.22.241.152 (15 Oct 2010 10:20:00 -0700)
X-Original-Trace: 15 Oct 2010 10:20:00 -0700, vip152.sybase.com
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29651
Article PK: 78879


On 13-Oct-2010 05:00, RGS wrote:
> Hi!
>
> Placement indexes are CLUSTERED INDEX created on tables with
> DOL locks
>
> I read that a Placement index doesn't guarantee that the
> data are ALWAYS ordering by the key of the index and I read
> that really a Placement index has pointers to the data
>
> Then, what is the advantage of create a PLACEMENT INDEX
> besides to create a normal NUNCLUSTERED INDEX on a DOL
> table?
>
> Thanks!

For queries that can benefit from the ordered nature of the index leaf
level, like range scans, placement indexes provide better I/O efficiency
since the data rows are as much in the order of the index key as
possible. This means this in most cases, though perhaps not in all, rows
that are next to each other in terms of the index key, are also next to
each other physically. This means less physical I/O is needed to resolve
the query and therefore things will be faster.
Now, the rows may not always be next to each other, but it will likely
be *often* the case (here, 'often' depends on the update activity on
that table, and how often things like 'reorg' are run).
In a nonclustered index, there is no attempt to store two subsequent
rows close to each other, meaning more physical I/O would be needed for
things like range queries.

HTH,

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

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"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
-----------------------------------------------------------------


Bret Halford Posted on 2010-10-13 15:11:46.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Placement Indexes
References: <4cb520e8.798d.1681692777@sybase.com>
In-Reply-To: <4cb520e8.798d.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: <4cb5cc32$1@forums-1-dub>
Date: 13 Oct 2010 08:11:46 -0700
X-Trace: forums-1-dub 1286982706 10.22.241.152 (13 Oct 2010 08:11:46 -0700)
X-Original-Trace: 13 Oct 2010 08:11:46 -0700, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29650
Article PK: 78880


On 10/12/2010 9:00 PM, RGS wrote:
> Hi!
>
> Placement indexes are CLUSTERED INDEX created on tables with
> DOL locks
>
> I read that a Placement index doesn't guarantee that the
> data are ALWAYS ordering by the key of the index and I read
> that really a Placement index has pointers to the data
>
> Then, what is the advantage of create a PLACEMENT INDEX
> besides to create a normal NUNCLUSTERED INDEX on a DOL
> table?
>
> Thanks!

Rows with similar key values will be as near each other on
disk as ASE can manage, meaning fewer I/Os to read in
all the data for a range query on the keys (as compared to
a nonclustered index).