Full scan vs index

From: Storey, Robert (DCSO) <"Storey,>
Date: Thu, 10 Oct 2013 13:25:17 +0000
Message-ID: <FE4C2B093843BB4B873D754E5E0BE4DB6CCD76F0_at_DCSOSVMS02.dcso.org>

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?

Received on Thu Oct 10 2013 - 15:25:17 CEST

Original text of this message