Oracle Performance Optimization Operation 18: Decide whether to use full table scan or index

As with all the tips, the last move will go back to the beginning, and finally we'll talk about whether or not you need to index, perhaps a full table scan faster.

In most cases, full table scans may result in more physical disk input and output, but full table scans can sometimes be performed faster because of the existence of a high degree of parallelism.

If the queried table is completely out of order, a query that returns fewer than 10% records may read most of the data blocks in the table, so using an index can make the query more efficient.

However, if the table is very sequential, it may be faster to use a full table scan if the number of records queried is greater than 40%.

Therefore, the overall principle of having an index range scan is:

1) for the original sorted table, only a query that reads fewer than 40% of the table records should use an index range scan.

Conversely, a query that reads 40% more records than the number of table records should use a full table scan.

2) for unordered tables, only queries that read fewer than 7% of the table records should use index range scanning.

Conversely, a query that reads 7% more records than the number of table records should use a full table scan.

Oracle Performance Optimization Operation 18: Decide whether to use full table scan or index