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
