I'd be very surprised if he can find a statement in the Oracle docs that
states that scattered reads (or indeed any single metric) must be reduced.
I'd challenge him on that.Since the requirement seems to be Oracle
documentation I'd start with the Performance Tuning Guide - here is 11g
(oldest currently supported version)
https://docs.oracle.com/cd/E11882_01/server.112/e41573/perf_overview.htm#sthref13
1. "TunSing is driven by identifying the most significant bottleneck and
making the appropriate changes to reduce or eliminate the effect of that
bottleneck " If your "scattered reads" aren't the biggest time component
of the thing you are tuning then you are probably looking at the wrong
thing.
2. " During the evaluation process, the query optimizer reviews
statistics gathered on the system to determine the best data access path
and other considerations". If the optimizer is picking an FTS then it
considers it the most appropriate access path, if it is picking it over an
index then there's a statistical reason for that. If the FTS was *always
*bad then we'd stick with our good old RBO.
3.
https://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#PFGRF94600
This expands on the point above illustrating exactly why the CBO considers
an FTS to be efficient and when.
On Tue, Apr 10, 2018 at 5:13 PM, Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi
I have a Dinosaur stating we must always use index range scan no matter
OLTP or DWH systems. This guys still lives in the late 90's and Oracle 7
(although he claims he is 18 years veteran expert from 7 up to 12c), the
thing is I cannot find any Oracle WP or similar in the docs which states
Full Table Scan is not evil except Tom Kyte (asktom). I have even demo-ed
him with a very simple two table join example with FTS and index range
effect when a large amount of data need to be accessed, still he says in
Oracle docs states that scattered reads (FTS) mist be reduced!
Does anyone know any official pointer, in WP form or MOS support note
which talks about FTS vs index scans?
Cheers
