0

THE SITUATION

I Have a table with only one index, a Clustered index (two columns). I do a 'SELECT * FROM TABLE' and the optimizer decides a Table scan.

I get the rows kinda sorted by clustered index. I say kinda because it doesn't look randomly sorted, but it has a lot of glitches.

If I force Using the clustered index SELECT * FROM TABLE (index 1 MRU) I get exact the clustered table order.

QUESTIONS

how can the table scan result be different in order than clustered index scan if the data in a clustered table is sorted by its index?

Is the table scan in a clustered index a scan to the leaf level of the table, aren't those sorted?

Is the clustered index scan a scan to all the possible paths of the b-tree in an ordered manner?

excuse my possible lack of knowledge, I'm trying my best to undestand the underlying concepts.

HOW DID I TESTED THIS

I achived this inconsistent ordering results by testing two different clustered indexes (one with two columns and other with one column). creating and dropping the constraint and check the select statement.

after truncating the table and creating the index, the data is correctly sorted, but after dropping the index and creating a different one, that data is not perfectly sorted with a table scan. I need to force index use.

WHY IS THIS IMPORTANT

Because I want to garantee order without using an order by clause in a clustered table.

Nelssen
  • 1,023
  • 1
  • 17
  • 42

2 Answers2

1

On 15.0 and upwards ALWAYS specify an order by if you want a specific order as the structure of the data and index varies between allpages and data only locked (DOL) tables.

The optimizer may choose to do parts of the query retrieval in parallel under the covers for example depending on your parallelism settings which is why the order by is important. Just saying select * hasn't requested any specific order.

Just add the order by and you'll be fine because the select * is going to tablescan anyway as you're asking for the whole table and therefore no need for index hints.

Rich Campbell
  • 566
  • 2
  • 9
  • that makes sence and I've also tried to do a serial processing by parameterizing parallel degree to 1, expecting to get something align with the table order. but it also failed. How can we explain this? if there is a single thread and the table is clustered (data sorted) how can he present the results unsorted? – Nelssen Jan 12 '18 at 16:02
  • 1
    Whats the locking scheme of the table in question? I suspect its DOL in which case the data isn't sorted in strict index key order like it on an allpages locked table. See : http://infocenter-archive.sybase.com/help/index.jsp?topic=/com.sybase.34982_1250/html/mig_gde/mig_gde61.htm 'Clustered indexes on data-only-locked tables may not return rows in clustered key order if there is no order by clause. Bulk copy is not guaranteed to copy out a table in clustered key order.' This is one of the key differences of DOL vs allpages locking. – Rich Campbell Jan 17 '18 at 13:33
  • 1
    Hi @Rich Campell, thanks for the Help, I was able to find out what the problem was. It was a pillar of my knowledge that was wrong. Clustered indexes are not physically ordered but logically ordered. Cheers – Nelssen Feb 09 '18 at 10:51
-1

THE EXPLANATION

Clustered indexes are logically ordered but not physically ordered.

This means that a table scan if it's done in physical order will return different results than clustered index scan, which is sorted logically.

This logical-physical mapping is controlled by OAM (Object Allocation Map)

Nelssen
  • 1,023
  • 1
  • 17
  • 42
  • 1
    Yes and no. If you're using allpages locking then the clustered index *does* physically order the data. If you're using datapages/datarows locking then the clustered index would fall under what you've termed 'logically ordered'. – markp-fuso Feb 09 '18 at 13:32
  • Hi Mark I'm using datarows locking scheme :) thanks for the extra information. can you provide a source so that I can update the answer? – Nelssen Feb 09 '18 at 21:24
  • you could start with the [ASE P&T: Locking and Concurrency Control manual](https://help.sap.com/viewer/a08646e0736e4b4b968705079db4c5f5/16.0.3.3/en-US) – markp-fuso Feb 09 '18 at 23:06