RE: Full scan vs index

From: Storey, Robert (DCSO) <"Storey,>
Date: Thu, 10 Oct 2013 13:57:26 +0000
Message-ID: <FE4C2B093843BB4B873D754E5E0BE4DB6CCD77C0_at_DCSOSVMS02.dcso.org>



Yep, still produces the exact same plan as without the hint.

But, I understand what is being said. In this case it's the need to read data from the blocks that prompts the optimize to just read the data blocks vs both index and datablocks.

Perhaps the decision point would be different if the table record count were higher?

-----Original Message-----

From: Uzzell, Stephan [mailto:SUzzell_at_MICROS.COM] Sent: Thursday, October 10, 2013 8:54 AM To: Storey, Robert (DCSO); Laimutis.Nedzinskas_at_seb.lt Cc: Oracle L; oracle-l-bounce_at_freelists.org; Uzzell, Stephan Subject: RE: Full scan vs index

Select /*+ INDEX( A onum_idx1) */ lastname from A where ordernum > 600000;

Stephan Uzzell

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Storey, Robert (DCSO) Sent: Thursday, 10 October, 2013 09:52
To: Laimutis.Nedzinskas_at_seb.lt
Cc: Oracle L; oracle-l-bounce_at_freelists.org Subject: RE: Full scan vs index

Assuming I got the index hint in correctly, the explain plain is the same using the hint as it was without. Cost is still 1152 with a full table scan. So it would appear that even when using a HINT the optimizer still chooses to do the "best" run for accessing the data. Even with the HINT it still determined that a full data scan was the most efficient way to get the data.

Let's make sure I got the hint right, as I don't normally use them. The index name for the ordernum column is onum_idx1

Select lastname /*+ INDEX (A onum_idx1) */ from A where ordernum > 600000; Using that query, the explain plan looks exactly the same as select lastname from A where ordernum>600000;

Is that a correctly formatted single table index hint?

From: Laimutis.Nedzinskas_at_seb.lt [mailto:Laimutis.Nedzinskas_at_seb.lt] Sent: Thursday, October 10, 2013 8:36 AM To: Storey, Robert (DCSO)
Cc: Oracle L; oracle-l-bounce_at_freelists.org Subject: 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

[Inactive hide details for "Storey, Robert (DCSO)" ---2013.10.10 16:26:45---Morning, I don't understand the decision process for]"Storey, Robert (DCSO)" ---2013.10.10 16:26:45---Morning, I don't understand the decision process for the CHOOSE optimizer sometimes. Maybe I'm just

From:

"Storey, Robert (DCSO)" <RStorey_at_DCSO.nashville.org>

To:

Oracle L <oracle-l_at_freelists.org<mailto: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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 10 2013 - 15:57:26 CEST

Original text of this message