Re: Anyone know any WP which explains about FTS vs Index Scan?
- From: Andy Sayer <andysayer@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Apr 2018 23:53:05 +0100
"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
Other related posts:
