Table SCAN on Clustered Index Table

[william-paul_philibert]

Bonjour,

   I have been asked by developpers a question that I could not answer and I have been looking around for the answer but to no avail so far.

Considering that a table with a clustered index is physicaly in order on the disk, does a table scan should not return data ordered by the said index (it currently does not)?

select * from mytable

go

A

A

A

B

B

C

E

G

???

Thanks for your time and expertise on this!

Regards, William

[former_member188958 · Accepted solution]

You should always use an "order by" clause if you want the results sorted in a particular way.

For DOL tables, the "clustered" index does not put the rows in strict order, it just tries to put rows with similar values as close together as it can without moving any existing rows.

Even with an APL clustered index, ASE could perform the scan using multiple worker processes (if so configured).  Each worker process would be returning the rows it processes in index order, but the output streams are randomly mixed together unless an order by clause is used.

-bret

[william-paul_philibert · Accepted solution]

Thanks for your awesome answer Bret, it helps me understand why I do not get the result I was expecting.  I know about the "order by" it did not explain the behavior of the database based on the information I had.

One more little think, as per Sybase documentation :

With a clustered index, Adaptive Server sorts rows on an ongoing basis so that their physical order is the same as their logical (indexed) order.  The bottom of the leaf level of a clustered index contains the actual data pages of the table.  Create the clustered index before creating any nonclustered indexes, since nonclustered indexes are automatically rebuilt when a clustered index is created.

Based on that, why do you say Sybase does not put the rows in strict order?  What am I missing here?

[c_baker · Accepted solution]

ANSI SQL does not guarantee rows in any order, so an ordering statement is required.

Ever since the advent of worker processes (ASE 11.5) ASE does not guarantee rows returned in sorted order unless an ORDER BY is used.  This became more apparent when DOL tables were introduced, per Bret's answer.

Historically, ASE would table scan and return results in clustered index order without any additional sorts in a table scan, so older code was written to avoid the sort by eliminating the ORDER BY, as it was a redundant operation.  With the current and recent changes to ASE and the optimizer, that is not guaranteed unless you provide the sorting instructions.

ASE will still put the rows in strict clustered order on APL tables, but for DOL tables you must rebuild/reorg the table to ensure it is in clustered index order.  As the application should not be aware of the underlying locking mechanism of the table, the only guarantee of ordered-ness is what you specify in the SQL.

The documentation quote you reference comes from the Transact SQL Users Guide in the documentation (under Using clustered or noclustered indexes) and does not state the table type. However, the definition of an APL clustered index is "The bottom or leaf level of a clustered index contains the actual data pages of the table".  This is not true for a DOL table.

You may have found a documentation error.

Chris

[william-paul_philibert · Accepted solution]

Thanks to both of you for these answers.  Understanding is very easy after that.

Best Regards to you!

William

[former_member188958 · Accepted solution]

I think this wording in the TSQL guide predates DOL tables, it does only apply to APL tables.  It appears to have already been removed in the 16.0 TSQL Guide.

-bret