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.

Datarows clustered index requires order by?

6 posts in General Discussion Last posting was on 2011-02-18 23:11:02.0Z
rjlpedrosa Posted on 2011-02-16 20:36:05.0Z
Sender: 7dc8.4d5c2ef6.1804289383@sybase.com
From: rjlpedrosa
Newsgroups: sybase.public.ase.general
Subject: Datarows clustered index requires order by?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d5c3534.7eca.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Feb 2011 12:36:05 -0800
X-Trace: forums-1-dub 1297888565 10.22.241.41 (16 Feb 2011 12:36:05 -0800)
X-Original-Trace: 16 Feb 2011 12:36:05 -0800, 10.22.241.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29976
Article PK: 79205

Hi,

I have an allpages table that we are going to change to
datarows to resolve the deadlocks problems.

Before changing the lock schema we are reviewing the
code, specifically for pieces with 'set rowcount 1', to see
if an order by has to be added as the order is no guarantee.

However the idea I have is that if an index is being
used, the data will be ordered by the index and the order by
is only needed if a table scan is being made.

The question I have is if I need to force an order by if
the clustered index is being used by the query (for table
scan I know I need the order by).
For the queries that are using the nonclustered indexes,
I have confirmed that the data is coming sorted by the index
columns.

Thanks


Bret Halford Posted on 2011-02-16 21:08:09.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Datarows clustered index requires order by?
References: <4d5c3534.7eca.1681692777@sybase.com>
In-Reply-To: <4d5c3534.7eca.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: <4d5c3cb9$1@forums-1-dub>
Date: 16 Feb 2011 13:08:09 -0800
X-Trace: forums-1-dub 1297890489 10.22.241.152 (16 Feb 2011 13:08:09 -0800)
X-Original-Trace: 16 Feb 2011 13:08:09 -0800, vip152.sybase.com
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29977
Article PK: 79206


On 2/16/2011 1:36 PM, rjlpedrosa wrote:
> Hi,
>
> I have an allpages table that we are going to change to
> datarows to resolve the deadlocks problems.
>
> Before changing the lock schema we are reviewing the
> code, specifically for pieces with 'set rowcount 1', to see
> if an order by has to be added as the order is no guarantee.
>
> However the idea I have is that if an index is being
> used, the data will be ordered by the index and the order by
> is only needed if a table scan is being made.
>
> The question I have is if I need to force an order by if
> the clustered index is being used by the query (for table
> scan I know I need the order by).
> For the queries that are using the nonclustered indexes,
> I have confirmed that the data is coming sorted by the index
> columns.

One issue is ensuring that the expected index will always be
used. What happens if the index became corrupt and had to
be dropped and isn't rebuilt yet? Or another index is added
that the optimizer decides is a bit better given the current
statistics?

While I don't think it is an issue while using "rowcount 1", if
the server is configured to use parallelism (worker processes), the
worker processes may not return the rows in index order even if
they are following an index (due to timing in getting scheduled on
engines, processing different pages, one getting a bit ahead
on the chain than the others, etc.) without an order by. Can
you ensure that nobody will turn on parallel processing at
some point in the future?

-bret


rjlpedrosa Posted on 2011-02-16 21:29:35.0Z
Sender: 17b.4d5c3fb3.1804289383@sybase.com
From: rjlpedrosa
Newsgroups: sybase.public.ase.general
Subject: Re: Datarows clustered index requires order by?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d5c41bf.1c9.1681692777@sybase.com>
References: <4d5c3cb9$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Feb 2011 13:29:35 -0800
X-Trace: forums-1-dub 1297891775 10.22.241.41 (16 Feb 2011 13:29:35 -0800)
X-Original-Trace: 16 Feb 2011 13:29:35 -0800, 10.22.241.41
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29978
Article PK: 79212


> On 2/16/2011 1:36 PM, rjlpedrosa wrote:
> > Hi,
> >
> > I have an allpages table that we are going to change
> > to datarows to resolve the deadlocks problems.
> >
> > Before changing the lock schema we are reviewing the
> > code, specifically for pieces with 'set rowcount 1', to
> > see if an order by has to be added as the order is no
> guarantee. >
> > However the idea I have is that if an index is being
> > used, the data will be ordered by the index and the
> > order by is only needed if a table scan is being made.
> >
> > The question I have is if I need to force an order
> > by if the clustered index is being used by the query
> > (for table scan I know I need the order by).
> > For the queries that are using the nonclustered
> > indexes, I have confirmed that the data is coming sorted
> > by the index columns.
>
> One issue is ensuring that the expected index will always
> be used. What happens if the index became corrupt and had
> to be dropped and isn't rebuilt yet? Or another index is
> added that the optimizer decides is a bit better given the
> current statistics?
>
> While I don't think it is an issue while using "rowcount
> 1", if the server is configured to use parallelism (worker
> processes), the worker processes may not return the rows
> in index order even if they are following an index (due to
> timing in getting scheduled on engines, processing
> different pages, one getting a bit ahead on the chain than
> the others, etc.) without an order by. Can you ensure
> that nobody will turn on parallel processing at some point
> in the future?
>
> -bret

Thanks for the quick reply. I understand your points but
that doesn't also apply to the current configuration using
allpages? What are the differences?

Ricardo


Bret Halford Posted on 2011-02-16 23:24:11.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Datarows clustered index requires order by?
References: <4d5c3cb9$1@forums-1-dub> <4d5c41bf.1c9.1681692777@sybase.com>
In-Reply-To: <4d5c41bf.1c9.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: <4d5c5c9b$1@forums-1-dub>
Date: 16 Feb 2011 15:24:11 -0800
X-Trace: forums-1-dub 1297898651 10.22.241.152 (16 Feb 2011 15:24:11 -0800)
X-Original-Trace: 16 Feb 2011 15:24:11 -0800, vip152.sybase.com
Lines: 77
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29980
Article PK: 79208


On 2/16/2011 2:29 PM, rjlpedrosa wrote:
>> On 2/16/2011 1:36 PM, rjlpedrosa wrote:
>>> Hi,
>>>
>>> I have an allpages table that we are going to change
>>> to datarows to resolve the deadlocks problems.
>>>
>>> Before changing the lock schema we are reviewing the
>>> code, specifically for pieces with 'set rowcount 1', to
>>> see if an order by has to be added as the order is no
>> guarantee.>
>>> However the idea I have is that if an index is being
>>> used, the data will be ordered by the index and the
>>> order by is only needed if a table scan is being made.
>>>
>>> The question I have is if I need to force an order
>>> by if the clustered index is being used by the query
>>> (for table scan I know I need the order by).
>>> For the queries that are using the nonclustered
>>> indexes, I have confirmed that the data is coming sorted
>>> by the index columns.
>>
>> One issue is ensuring that the expected index will always
>> be used. What happens if the index became corrupt and had
>> to be dropped and isn't rebuilt yet? Or another index is
>> added that the optimizer decides is a bit better given the
>> current statistics?
>>
>> While I don't think it is an issue while using "rowcount
>> 1", if the server is configured to use parallelism (worker
>> processes), the worker processes may not return the rows
>> in index order even if they are following an index (due to
>> timing in getting scheduled on engines, processing
>> different pages, one getting a bit ahead on the chain than
>> the others, etc.) without an order by. Can you ensure
>> that nobody will turn on parallel processing at some point
>> in the future?
>>
>> -bret
>
> Thanks for the quick reply. I understand your points but
> that doesn't also apply to the current configuration using
> allpages? What are the differences?
>
> Ricardo

These points all apply to allpages as well - the expected index may
not be available, the optimizer might make a different
decision than expected, worker processes may serve up
rows in various orders. The only way to really be sure of
ordering is to supply an ORDER BY clause. The ORDER BY
often will not result in any additional work for ASE as the
optimizer will tend to favor index scans that avoid the sorting.

The main distinction in scan behavior is that with ALLPAGES, the
data layer (indid 0) is all in a linked list of pages with the rows
on those pages in actual index order. A table scan of
an allpages table follows the linked list and so (as a side
effect of implementation), a non-parallel table scan will
still return rows in the clustered index order.

For DOL tables, the data layer is not maintained in a
linked list. The "table scan" is based on a scan of
the entries in the objects OAM, so tends to proceed from
extents allocated early in the database space to extents
near the end. Data inserted later in time can be allocated
extents early in the database that have been deallocated from
other tables.

The DOL "clustered" index also does not force
absolute ordering of the rows in the data layer - it inserts
new rows as close as it can to rows with similar values, but
won't move rows from page to page to make room for a row
that would logically fit between two rows. So the leaf level
of the index is ordered by the keys, the actual data layer
really is not.


rjlpedrosa Posted on 2011-02-17 06:00:30.0Z
Sender: 17b.4d5c3fb3.1804289383@sybase.com
From: rjlpedrosa
Newsgroups: sybase.public.ase.general
Subject: Re: Datarows clustered index requires order by?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d5cb97e.176b.1681692777@sybase.com>
References: <4d5c5c9b$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Feb 2011 22:00:30 -0800
X-Trace: forums-1-dub 1297922430 10.22.241.41 (16 Feb 2011 22:00:30 -0800)
X-Original-Trace: 16 Feb 2011 22:00:30 -0800, 10.22.241.41
Lines: 90
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29982
Article PK: 79209


> On 2/16/2011 2:29 PM, rjlpedrosa wrote:
> >> On 2/16/2011 1:36 PM, rjlpedrosa wrote:
> >>> Hi,
> >>>
> >>> I have an allpages table that we are going to
> change >>> to datarows to resolve the deadlocks problems.
> >>>
> >>> Before changing the lock schema we are reviewing
> the >>> code, specifically for pieces with 'set rowcount
> 1', to >>> see if an order by has to be added as the order
> is no >> guarantee.>
> >>> However the idea I have is that if an index is
> being >>> used, the data will be ordered by the index and
> the >>> order by is only needed if a table scan is being
> made. >>>
> >>> The question I have is if I need to force an
> order >>> by if the clustered index is being used by the
> query >>> (for table scan I know I need the order by).
> >>> For the queries that are using the nonclustered
> >>> indexes, I have confirmed that the data is coming
> sorted >>> by the index columns.
> >>
> >> One issue is ensuring that the expected index will
> always >> be used. What happens if the index became
> corrupt and had >> to be dropped and isn't rebuilt yet?
> Or another index is >> added that the optimizer decides is
> a bit better given the >> current statistics?
> >>
> >> While I don't think it is an issue while using
> "rowcount >> 1", if the server is configured to use
> parallelism (worker >> processes), the worker processes
> may not return the rows >> in index order even if they are
> following an index (due to >> timing in getting scheduled
> on engines, processing >> different pages, one getting a
> bit ahead on the chain than >> the others, etc.) without
> an order by. Can you ensure >> that nobody will turn on
> parallel processing at some point >> in the future?
> >>
> >> -bret
> >
> > Thanks for the quick reply. I understand your points but
> > that doesn't also apply to the current configuration
> > using allpages? What are the differences?
> >
> > Ricardo
>
> These points all apply to allpages as well - the expected
> index may not be available, the optimizer might make a
> different decision than expected, worker processes may
> serve up rows in various orders. The only way to really
> be sure of ordering is to supply an ORDER BY clause. The
> ORDER BY often will not result in any additional work for
> ASE as the optimizer will tend to favor index scans that
> avoid the sorting.
>
> The main distinction in scan behavior is that with
> ALLPAGES, the data layer (indid 0) is all in a linked list
> of pages with the rows on those pages in actual index
> order. A table scan of an allpages table follows the
> linked list and so (as a side effect of implementation), a
> non-parallel table scan will still return rows in the
> clustered index order.
>
> For DOL tables, the data layer is not maintained in a
> linked list. The "table scan" is based on a scan of
> the entries in the objects OAM, so tends to proceed from
> extents allocated early in the database space to extents
> near the end. Data inserted later in time can be
> allocated extents early in the database that have been
> deallocated from other tables.
>
> The DOL "clustered" index also does not force
> absolute ordering of the rows in the data layer - it
> inserts new rows as close as it can to rows with similar
> values, but won't move rows from page to page to make room
> for a row that would logically fit between two rows. So
> the leaf level of the index is ordered by the keys, the
> actual data layer really is not.
>

Thanks for the clarification Bret. Right now I will only
want to change the code that can be affect by changing to
datarows as code is working now without problems (the
analysis impact found more than 300 procedures). So
resuming, even with datarows, if an index is being used and
no parallelism is set for the query, the data will be
returned by the order of index used, correct? I just need to
worry with potential table scans, right?

Thanks


Bret Halford Posted on 2011-02-18 23:11:02.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Datarows clustered index requires order by?
References: <4d5c5c9b$1@forums-1-dub> <4d5cb97e.176b.1681692777@sybase.com>
In-Reply-To: <4d5cb97e.176b.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: <4d5efc86$1@forums-1-dub>
Date: 18 Feb 2011 15:11:02 -0800
X-Trace: forums-1-dub 1298070662 10.22.241.152 (18 Feb 2011 15:11:02 -0800)
X-Original-Trace: 18 Feb 2011 15:11:02 -0800, vip152.sybase.com
Lines: 17
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29989
Article PK: 79218


>
> Thanks for the clarification Bret. Right now I will only
> want to change the code that can be affect by changing to
> datarows as code is working now without problems (the
> analysis impact found more than 300 procedures). So
> resuming, even with datarows, if an index is being used and
> no parallelism is set for the query, the data will be
> returned by the order of index used, correct? I just need to
> worry with potential table scans, right?

Yes, I think I'd expect that. I'm just not confident
the index used will always be the one you want.

Cheers,
-bret