Can anyone give me an example where an index wouldThe obvious case would be where you had an order by clause with a matching index. With a well-clustered table, the optimizer might decide to do an index full scan to collect 100% of the data in order rather then doing a tablescan and sort.
be the best option to query 99% of data.
Can anyone give me an example where a FTS is theConsider a table in a tablespace with an 8Kb block size - with short rows (roughly 80 bytes) you can get 100 rows per block. Run a query that wants to get one row from each block (i.e. 1% of the data). A tablescan with a db_file_multiblock_read_count of just 2 would be more efficient than an indexed access. For a more realistic example consider picking one row from every 16th block when your db_file_multiblock_read_count is 128.
best option to query 1% of data.