Table Scan and Index Scan in SQL

Question

What is the difference between Table scan and Index scan in SQL and where it is used specifically?

Answer 1

Table scan means iterate over all table rows.

Index scan means iterate over all index items, when item index meets search condition, table row is retrived through index.

Usualy index scan is less expensive than a table scan because index is more flat than a table.

They are lot of bibliografy about this issue. Sample:

• Microsoft: Which is Faster: Index Access or Table Scan?:

Index access is an access method in which SQL Server uses an existing index to read and write data pages. Because index access significantly reduces the number of I/O read operations, it often outperforms a table scan.

• Oracle: The Query Optimizer

In this method, a row is retrieved by traversing the index, using the indexed column values specified by the statement. An index scan retrieves data from an index based on the value of one or more columns in the index. To perform an index scan, Oracle searches the index for the indexed column values accessed by the statement. If the statement accesses only columns of the index, then Oracle reads the indexed column values directly from the index, rather than from the table.

• MySql: How to Avoid Table Scans