Oracle performance optimization operation 18: determines whether to use full table scan or index. oracle Performance Optimization

Oracle performance optimization operation 18: determines whether to use full table scan or index. oracle Performance Optimization

Like all tips, the last trick will go back to the starting point. Finally, let's discuss whether to create an index, maybe it is faster to perform a full table scan.

In most cases, full table scanning may lead to more input and output from physical disks. However, full table scanning may sometimes run faster because of high degree of parallelism.

If the queried table has no order at all, a query with the number of returned records less than 10% may read most of the data blocks in the table. Using indexes will increase the query efficiency.

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

Therefore, the general principle of an index range scan is:

1) index range scanning should be used to read only queries that are less than 40% of the number of table records in the original sorting table.

Conversely, full table scan is recommended for queries that read more than 40% of the table records.

2) index range scanning should be used for unordered tables that only read less than 7% of the number of table records.

Conversely, full table scan is recommended for queries that read more than 7% of the table records.