RE: Full scan vs index
- From: "Uzzell, Stephan" <SUzzell@xxxxxxxxxx>
- To: "'RStorey@xxxxxxxxxxxxxxxxxx'" <RStorey@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 10 Oct 2013 13:42:29 +0000
Meant to copy the list - just in case anyone wanted to clarify / correct what I
wrote:
Stephan Uzzell
-----Original Message-----
Generally speaking (and I'm sure someone else on the list can explain it better
than I, but I'm hoping I understand it well enough to explain :-) ):
Select count(*) from A where ordernum > 600000 - there is no need to visit the
table, all the information can be retrieved from the index.
Select count(*) from A where status = 'I' - again, the index alone is
sufficient to answer that.
Select lastname from A where ordernum > 600000 - while the PK is sufficient to
identify the rows based on ordernum, you have to visit the table for each of
those rows to retrieve the lastname. The optimizer is basically calculating
(perhaps correctly, perhaps not) that it will cost more to use the index to
identify 87000 records via index and then make 87000 trips to the table to find
the lastnames than it will to read the whole table and find the records that
way.
Stephan Uzzell
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Storey, Robert (DCSO)
Sent: Thursday, 10 October, 2013 09:25
To: Oracle L
Subject: Full scan vs index
Morning,
I don't understand the decision process for the CHOOSE optimizer sometimes.
Maybe I'm just overlooking something fundamental about query parsing. Here is
the scenario and maybe someone can enlighten me.
Table A has 12 columns I have indexes on columns 1, 2, and 3. With column 1
being the PK for the table. There are 596,785 records in the table Column 1 is
ordernum number(12) Column 2 is Status char(1) with possible values of I or A.
Column 3 is Lastname.
So, I do the following query.
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.
Select count(*) from A where Status = 'I'. again, 435000 records, plan is a
cost of about 123 and uses the status index.
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.
I would think that even though I'm pulling a column out, it would still use the
index scan to get the target rows. Why would adding the column make it decide
to scan full table vice index?
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
