Re: Full scan vs index

From: Jared Still <>
Date: Sun, 27 Oct 2013 21:11:32 -0400
Message-ID: <>

Because the optimizer knows that reading the table to get those 87k rows back will be faster than reading a ROWID , fetching a row, etc.

There are other gotchas as well.

Try this, first of all forget explain plan for now.

Using your test table, use a hint to get your index used by oracle.

Trace both SQL statements and trace them to see where the time goes.


On Thursday, October 10, 2013, Storey, Robert (DCSO) 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?
> --

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog:
Home Page:

Received on Mon Oct 28 2013 - 02:11:32 CET

Original text of this message