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

A full tablescan can do much less IO by it’s multiblock reads than a high selectivity index range scan that does a single block read per row (which can easily do multiple reads to the same block).

I bet the demo you shared from before can be easily extended to show the amount of IO it did. If this is the metric that matters then that should at least start the conversation. Most likely, the real metric that people really care about is time between user wanting to do something and user having achieved that thing - the time may be spent doing IO, it may be spent on cpu, it may be spent on some obscure wait event that you need to google.

The whole DWH vs OLTP comparison is full of rules of thumb (ROT). In truth, the lines are completely blurred but the rules of physics apply to them both with exactly the same prejudice. The one difference is that sometimes no one cares how long a query takes in a DWH, so long as it completes today/without erroring.

In my 16 Tb datawarehouse we still have many known queries that only need to see small amounts of data to get a result and there are indexes to support this. We also have plenty of situations where someone will want to see trends over huge data sets and a full tablescan to read the data will be considerably faster than index range scans and reads. In our OLTP system there are plenty of business processes that require reading the majority of a table - batch processes, healthcheck reports, Luckily we can trust Uncle Optimizer to decide what needs to be done.

Just a reminder, it doesn’t matter who writes it or where you found information, it’s only as trustworthy as it is demonstratable. Facts should stand up well enough on their own.

And if you are looking to improve the performance of something, start by looking where the time is going rather than work through a list of guesses as to what could be the problem. Unfortunately, this may show a full table scan being the problem, but that doesn’t mean it’s always the problem.

Hope that helps,
Andrew

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