Re: Full scan vs index

From: David Ramírez Reyes <dramirezr_at_gmail.com>
Date: Thu, 10 Oct 2013 08:32:41 -0500
Message-ID: <CAJt=wvXfvT0GqZGH1R3r5LxYYE+ZKYxiZFEbsCa2fpaU+qohsA_at_mail.gmail.com>

Have you tried the query with a hint forcing to use the Index?, what is the cost?
David Ramírez Reyes
Profesión: Padre de Familia

On 10 October 2013 08:25, Storey, Robert (DCSO) <RStorey_at_dcso.nashville.org>wrote:

> 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?
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Thu Oct 10 2013 - 15:32:41 CEST