RE: Full scan vs index
- From: Paul Houghton <Paul.Houghton@xxxxxxxxxxxxxxx>
- To: "RStorey@xxxxxxxxxxxxxxxxxx" <RStorey@xxxxxxxxxxxxxxxxxx>, Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Oct 2013 14:43:07 +0100
Here is my guess. I am sure someone will correct me if I am wrong.
> Select count(*) from A where ordernum > 600000. It returns 87000 records.
> The explain plan shows a cost of 113 and the use of the ordernum_pk. Easy
> enough.
You only want a count, so only need to in an index range scan.
> Select count(*) from A where Status = 'I'. again, 435000 records, plan is
> a cost of about 123 and uses the status index.
As above, index range scan returns a count
> Now the part I don't get
>
> Select lastname from A where ordernum > 600000. Now I get a Full table
> Access of A to return 81K rows at a cost of 1152. So, order of magnitude
> worse and NO index usage.
Here you need data which is not in the relevant index. Therefore it needs to
scan the index and then read the table blocks. You don't tell us the clustering
factor of the index, but since you are reading about 20% of the table (If my
maths is correct) it is more efficient to do a full table scan, then you can
take advantage of multi block reads (Scattered read), normally reading about 1M
at a time, therefore the number of I/Os is likely to be less than reading all
index blocks then all relevant table blocks (normally 8k at a time, i.e. 128x
the I/Os).
My understanding of the optimizer is not detailed enough to understand how it
calculates this, but I think the above is a good enough overview...
I hope this helps
Paul Houghton
--
//www.freelists.org/webpage/oracle-l
Other related posts:
