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

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_at_gmail.com> 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
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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