Skip to Content
author's profile photo Former Member
Former Member

Table SCAN on Clustered Index Table


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











Thanks for your time and expertise on this!

Regards, William

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Nov 06, 2015 at 03:54 PM

    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.


    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.