Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 8bit
Subject: RE: Full scan vs index
Date: Thu, 10 Oct 2013 15:19:07 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF021C1023@WIN02.hotsos.com>
Thread-Topic: Full scan vs index
References: <FE4C2B093843BB4B873D754E5E0BE4DB6CCD76F0@DCSOSVMS02.dcso.org>
From: "Ric Van Dyke" <ric.van.dyke@hotsos.com>
To: <RStorey@DCSO.nashville.org>,
 "Oracle L" <oracle-l@freelists.org>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: ric.van.dyke@hotsos.com

It's not about ROWS it's about BLOCKS.  How many blocks are being
accessed?  

Where are all the rows in the last query?  If the data is sparsely
populated then the FULL scan is a good idea. Generally speaking if the
data is in about 20% or more of the blocks of the table, then a FULL
scan is a good idea.  Oracle uses the CLUSTER_FACTOR of the index to
give it a clue about the density of the data. 

Oh, and the cost is relevant. 

And it's not the "CHOOSE" optimizer either, it's either IO or CPU.  The
setting of CHOOSE in 10 and above means CPU. 

Ric Van Dyke
Education Director 
Hotsos Enterprises, Ltd


-----Original Message-----
From: oracle-l-bounce@freelists.org
[mailto:oracle-l-bounce@freelists.org] On Behalf Of Storey, Robert
(DCSO)
Sent: Thursday, October 10, 2013 9:25 AM
To: Oracle L
Subject: Full scan vs index

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