Clustered INdex - Strange Behavior

[JeanNiBee]

Hi

I have a table that has a clustered index on the ID field.

If I do a select *, the optimizer returns all the rows, BUT, with the following 'weird' behaviour:

1- The returned list is NOT sorted. Actually 80% is sorted, the rest is not.
2- The Optimizer does a table scan EVEN if I specify a 'where' clause and limit the the ID to a range. (I.E. ID >= 10001)

My questions, why is the info NOT sorted and why the table scan ALL the time?

If I force the query optimizer to use the index, it omits the table scan, BUT, the info is still not sorted.

Is this normal? Is it possible that the Datapages are sorted but the data on each page is not 100% guaranteed to be sorted?

I await your wisdom.





-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[sybaseguru]

Can you please do a sp_help on table and also do a sp_spaceused on this table as well and send the output. If you have a chance send the showplan output as well. Please do dbcc traceon(3604,302,311) in showplan

 

[qyllr]

hello,

1. if you have a DOL table, you may see this due to page splitting, inserts and deletes... i think sybase does not guarantee that the results be sorted...
2. i believe the optimizer will choose the best scenario with less i/o... you may be specifying a big range that forces the optimizer to choose the table scan...

hope this helps,
q.

 

[JeanNiBee]

Well seems I have lost access to that particular machine.

I'll re-post my request and info if/when I get it back.

Thanks.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[paragkathale]

Sybase had removed this very guarantee of returned result in sorted order even if table has clustered index in version 11.5 ( it is mentioned in official sybase reference book .. that sybase no longer gaurantees that result will be sorted because of clustered index). and I faced same problem 3 years ago when migrating from 11.5 to 11.9.2.

we had to put order by clause.

But I am not Sure whether it Still persist in sybase 12.5 as well .. may be official sybase reference books may have something.

Please let me also know also.

 

[markmar1]

It still persists on v12+ since when at data-only locking, and datarow locking, the actual leaf pages are not physically in clustered index order. WIth DOL and DRL, the clustered index acts similar to non-clustered since its not the actual data page such as with allpages locking....

So, if its allpages locking, I havent seen any ordering issues. With DOL, and DRL locking schemes, I have seen results not being returned in sorted order...

Just my $.02