Identify two situations in which full table scans will be faster than index range scans.

Identify two situations in which full table scans will be faster than index range scans.

A.
A query with a highly selective filter fetching less than 5 percent of the rows from a table.

B.
A highly selective query on a table having high clustering factor for an index.

C.
A query fetching less number of blocks than value specified by
DB_FILE_MULTIBLOCK_READ_COUNT.

D.
A query executing in parallel on a partitioned table with partitioned indexes.

E.
A query on a table with sparsely populated table blocks.

Explanation:

D: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O
during table scans. It specifies the maximum number of blocks read in one I/O operation during a
sequential scan. The total number of I/Os needed to perform a full table scan depends on such
factors as the size of the table, the multiblock read count, and whether parallel execution is being
utilized for the operation.
Online transaction processing (OLTP) and batch environments typically have values in the range
of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from
maximizing the value of this parameter. The optimizer is more likely to choose a full table scan
over an index if the value of this parameter is high.
Note:
* See 6) and 7) below.
The oracle optimizer choose the best plan and execute the query according the plan. It is common
to hear that my table has indexes but why oracle does not use indexes rather it is using full table
scan. There are several reasons behind choosing optimizer full table scans. 1)The table has no
indexes within it.
2)Table has indexes but they are not appropriate to queries. For example in the table there is
normal B-tree indexes but in the query the column used in the WHERE clause contains function.
3)Query access large amount of data. The table has indexes but query against it select almost all
of the rows. In that case optimizer might choose to full access of table.
4)Index creation order may not appropriate. You have composite indexes on a table but in the
where clause the leading column inside indexes are not used rather trailing columns are used.
5)The table is skewed. For example column gender contains value ‘M’ 10,000 times but value ‘F’
only 10 times.6)The table is small. If a table can read in a single I/O call, then a full table scan
might be cheaper than an index range scan. Single I/O call is defined by
DB_FILE_MULTIBLOCK_READ_COUNT parameter and value defined by blocks.Check it
by,SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNTNAME TYPE VALUE———————————— ——————————— ——————————db_file_multiblock_read_count
integer 16

7)High degree of parallelism. High degree of parallelism skews the optimizer toward full table
scans.
8)In the query if there is no filtering then full table scan is the choice.
* If an index has poor cardinality (ie. more than 4% rows with the same index key) then it will
perform poorly. It will usually be faster to perform a full table scan. eg. Table SALES has an index
on the column PAYMENT_METHOD which can contain values such as COD, CREDIT, CHEQUE,
CASH. The statement
SELECT *
FROM sales
WHERE payment_method = ‘CASH’
will probably perform so badly that you are better off without the index.
* Oracle uses the full table scan as it assumes that it will have to read a certain part of the table.
Reference: Oracle Database Reference, DB_FILE_MULTIBLOCK_READ_COUNT