Re: Full scan vs index

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Fri, 11 Oct 2013 07:40:21 +0700
Message-Id: <314FA31F-BB7F-4469-A9EF-CC6B73049175_at_gmail.com>

One thing to note that has not been mentioned.

It seems to me you are comparing the cost of your 3 queries, and the third being worse because the cost is higher.

Cost is not meant to be used like this.

Cost of 2 different queries is not related. Cost is used to find the best plan for a single query only.

Stefan

On 10 Oct 2013, at 20: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 Fri Oct 11 2013 - 02:40:21 CEST