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

Question

Options

• AA query with a highly selective filter fetching less than 5 percent of the rows from a table.
• BA highly selective query on a table having high clustering factor for an index.
• CA query fetching less number of blocks than value specified by DB_FILE_MULTIBLOCK_READ_COUNT.
• DA query executing in parallel on a partitioned table with partitioned indexes.
• EA query on a table with sparsely populated table blocks.

• A
  3% (1)
• B
  12% (4)
• C
  76% (26)
• E
  9% (3)

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. * 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' 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. SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT ------------------------------------ --------------------------------- ------------------------------ db_file_multiblock_read_count integer 16 7)High degree of parallelism. High degree of parallelism skews the optimizer toward full table 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 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.

No community discussion yet for this question.