RE: Full scan vs index
Date: Thu, 10 Oct 2013 15:19:07 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF021C1023_at_WIN02.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_at_freelists.org
[mailto:oracle-l-bounce_at_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
Received on Thu Oct 10 2013 - 22:19:07 CEST