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

>
> "Using an index is plain stupid if the filter is highly selective" ..
> that's not necessarily true. sorting and aggregation can be supported by
> appropriate indexes.

Doing a full table scan, sorting the data and then returning all the results can still be considerably faster than reading the entire table in order via the index block by block by block. If you are doing a top N style query then that is a different story, but I wouldn't class that as highly selective.

Sure, finding a min/max value or counting all rows that match a predicate (but not projecting any other column) can really take advantage of an index. I'll rephrase my original comment: Using an index is probably not a good idea if the filters are highly selective and you need the data from unfiltered columns.

On the subject of documentation not calling full table scans evil, the SQL Tuning Docs (12.1:
https://docs.oracle.com/database/121/TGSQL/tgsql_optop.htm#TGSQL231 ) has plenty to say, including:

> 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.

I have a feeling that it doesn't matter what supporting evidence is produced, 18 years is a long time to develop stubbornness. If they've managed to get away with it for so long, it's hardly a surprise they'd want to continue to ignore the facts.

Andrew

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