Re: Full scan vs index
- From: Jared Still <jkstill@xxxxxxxxx>
- To: "RStorey@xxxxxxxxxxxxxxxxxx" <RStorey@xxxxxxxxxxxxxxxxxx>
- Date: Sun, 27 Oct 2013 21:11:32 -0400
Why?
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.
Jared
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?
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
