Re: Full scan vs index

provided I've correctly understood your data schema this book will answer your question: Relational Database Index Design and the Optimizers by Tapio Lahdenmaki (Author), Mike Leach (Author)

in short: table touch to get lastname for each ordernum which satisfies your condition is a random read. It takes 3 to 7 ms. Serial read - may be 30-50MB/sec per spindle ?

Please consider the environment before printing this e-mail

                                                                                                                                               
  From:       "Storey, Robert (DCSO)" <RStorey_at_DCSO.nashville.org>                                                                             
                                                                                                                                               
  To:         Oracle L <oracle-l_at_freelists.org>                                                                                                
                                                                                                                                               
  Date:       2013.10.10 16:26                                                                                                                 
                                                                                                                                               
  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 - 15:35:55 CEST