RE: Full scan vs index
- From: "Storey, Robert (DCSO)" <RStorey@xxxxxxxxxxxxxxxxxx>
- To: "Laimutis.Nedzinskas@xxxxxx" <Laimutis.Nedzinskas@xxxxxx>
- Date: Thu, 10 Oct 2013 13:51:47 +0000
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@xxxxxx [mailto:Laimutis.Nedzinskas@xxxxxx]
Sent: Thursday, October 10, 2013 8:36 AM
To: Storey, Robert (DCSO)
Cc: Oracle L; oracle-l-bounce@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx>
To:
Oracle L <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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?
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
