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

Hi,
The simple fact that the optimizer (CBO) can choose full table scan over index range scan is a proof that full table scan can be better. The introduction of adaptive join in 12c is a proof that there’s a point, in number of rows to be fetched from the table, where range scan must be avoided in favor of full table scan. That’s directly from oracle optimizer development team, so even better than a doc. Regards,
Franck.
Le mer. 11 avr. 2018 à 00:53, Andy Sayer <andysayer_at_gmail.com> a écrit :

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