Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
778 views
in Technique[技术] by (71.8m points)

sql server - Why/when/how is whole clustered index scan chosen rather than full table scan?

IMO, please correct me...
the leaf of clustered index contains the real table row, so full clustered index, with intermediate leaves, contain much more data than the full table(?)
Why/when/how is ever whole clustered index scan chosen over the full table scan?

How is clustered index on CUSTOMER_ID column used in SELECT query which does not contain it in either SELECT list or in WHERE condition [1]?

Update:
Should I understand that full clustered scan is faster than full table scan because "Each data page contains pointers to the next and previous leaf node page so the scan does not need to use the higher level pages in the index"?
Are there any other reasons like (non-participating in query) clustered index is used in sorting?

Update2:
As afterthought, consecutive access cannot give performance boost while loading table through IAM pointers can be parallelized.
Does clustered index scan imply consecutive page reading?
Does clustered table imply absence of IAM pointers (impossibility of full table scan)?
Why cannot clustered table be full table scanned?
I still do not understand how/why clustered index full scan can be "better" over full table scan.
Does it mean that having clustered index can result in performance worsening?

The question is about clustered table not heap (non-indexed) table.

Update3:
Is "full clustered index scan" really synonym to "full table scan"?
What are differences?

[1] Index Covering Boosts SQL Server Query Performance
http://www.devx.com/dbzone/Article/29530

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

The clustered index - or more precisely: its leaf pages ARE the table data - so a clustered index scan really is the same as a table scan (for a table with a clustered index).

If you don't have a clustered index, then your table is a heap - obviously, in this case, if you need to look at all the data, you cannot do a clustered index scan since there is no clustered index, so you'll end up with a table scan which just touches all data pages for that heap table.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...