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

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