RE: Anyone know any WP which explains about FTS vs Index Scan?

  • From: Paul Houghton <Paul.Houghton@xxxxxxxxxxxxx>
  • To: Ls Cheng <exriscer@xxxxxxxxx>
  • Date: Mon, 16 Apr 2018 16:49:31 +0000

Hi

To answer the original question, here are some links in the documentation. I 
hope they help. I think the documentation expects one to read and understand 
it, which takes a lot of time, and help from the experts on the list. If you 
already (think you) know it all you might not bother! These quotes are mostly 
from the SQL Tuning guide for version 12.1.

https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#TGSQL193

  """
Purpose of the Query Optimizer

The optimizer attempts to generate the most optimal execution plan for a SQL 
statement.

The optimizer choose the plan with the lowest cost among all considered 
candidate plans. The optimizer uses available statistics to calculate cost. For 
a specific query in a given environment, the cost computation accounts for 
factors of query execution such as I/O, CPU, and communication.

For example, a query might request information about employees who are 
managers. If the optimizer statistics indicate that 80% of employees are 
managers, then the optimizer may decide that a full table scan is most 
efficient. However, if statistics indicate that very few employees are 
managers, then reading an index followed by a table access by rowid may be more 
efficient than a full table scan.

Because the database has many internal statistics and tools at its disposal, 
the optimizer is usually in a better position than the user to determine the 
optimal method of statement execution. For this reason, all SQL statements use 
the optimizer.
  """


https://docs.oracle.com/database/121/TGSQL/tgsql_optop.htm#GUID-461E7071-2229-4F60-82E6-BC4F6FC8D23B

  """
If the optimizer determines that the query requires most of the blocks in the 
table, then it uses a full table scan, even though indexes are available. Full 
table scans can use larger I/O calls. Making fewer large I/O calls is cheaper 
than making many smaller calls.
  """

Later in the same section:

  """
If a table contains fewer than n blocks under the high water mark, where n 
equals the setting for the DB_FILE_MULTIBLOCK_READ_COUNT initialization 
parameter, then a full table scan may be cheaper than an index range scan. The 
scan may be less expensive regardless of the fraction of tables being accessed 
or indexes present.
  """

https://docs.oracle.com/database/121/TGSQL/tgsql_indc.htm#TGSQL860

  """
Guidelines for Writing SQL Statements That Avoid Using Indexes

In some cases, you might want to prevent a SQL statement from using an index 
access path. For example, you know that the index is not very selective and a 
full table scan would be more efficient.
  """


The 10.2 manual is more direct, from the administration guide.

https://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i44851

  """
13.5.1.1 Why a Full Table Scan Is Faster for Accessing Large Amounts of Data

Full table scans are cheaper than index range scans when accessing a large 
fraction of the blocks in a table. This is because full table scans can use 
larger I/O calls, and making fewer large I/O calls is cheaper than making many 
smaller calls.
  """

I can't find this text in the 12.1 manuals. It also doesn't discuss the 
additional cost of accessing the index which has been pointed out.

I hope this is helpful!

PaulH
--
http://www.freelists.org/webpage/oracle-l


Other related posts: