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

From: Ls Cheng <>
Date: Sun, 15 Apr 2018 21:22:10 +0200
Message-ID: <>


He sticked with late 90's theory however he actually pointed me some oracle 11.2 documentation links which talks about reduce I/O, use index when possible, then I started searching some stuff from Oracle and I cannot find a lot which says index scan is not always the best (I found the explanation in asktom only). So I question if Oracle documentation is actually promoting index scans! :-)

I also looked for why FTS is preferable in DWH where massive data need sto be accessed without much luck neither.

I gave him examples, I explained why CBO is introduced to make decision when FTS is better etc and I think he started to believe me but because of his ego he does not want to stop discuss.


<> Virus-free.
<> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sat, Apr 14, 2018 at 5:19 PM, Frits Hoogland <> wrote:

> My question would be: why an index range scan MUST be used? What is the
> reason for this person to deem it mandatory?
> This would also discard unique index access, which is a different access
> path than an index range scan.
> I question the knowledge of the person if he/she can not see the point of
> a full table scan or any other different access path.
> Even if the documentation would state that an index must be used, and does
> not provide a valid reason for why it shouldn’t be used, I would discard
> the advise in the documentation. There are lots of examples where the
> official documentation is incorrect, and even experts might be wrong; for
> something to be written in a book doesn’t make it the truth.
> Frits Hoogland
> Mobile: +31 6 14180860
> On 10 Apr 2018, at 18:13, Ls Cheng <> 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

Received on Sun Apr 15 2018 - 21:22:10 CEST

Original text of this message