Re: Full scan vs index
Date: Thu, 10 Oct 2013 12:39:54 -0700 (PDT)
Message-ID: <1381433994.48892.YahooMailNeo_at_web121605.mail.ne1.yahoo.com>
I don't see the version of Oracle anywhere in this thread ...
Also, how recent are the statistics, what was the sample size and are histograms created/maintained? David Fitzjarrell
On Thursday, October 10, 2013 9:20 AM, "Storey, Robert (DCSO)" <RStorey_at_DCSO.nashville.org> wrote:
To follow up, Chris pointed out my error in my hint. Correctly formatted but wrong index. When I used the correct index in the hint, the index was used and the plan cost jumped from 1152 to 1840.
Thanks all for the responses. Its been a learning morning
From: David Ramírez Reyes [mailto:dramirezr_at_gmail.com]
Sent: Thursday, October 10, 2013 9:11 AM
To: Storey, Robert (DCSO)
Cc: Uzzell, Stephan; Laimutis.Nedzinskas_at_seb.lt; Oracle L; oracle-l-bounce_at_freelists.org
Subject: Re: Full scan vs index
In some way yes, but more than the number of records on the table is the average of records on the results; the first query gives you as result the 14.5% (87,000 of 600,000) of the total records, which make the optimizer evaluate as a better option the Full Table scan; if the number of records were less (probably less than 5%), it may probably use Index instead of Full table scan.
Just one more questions:
Is the ordernum column defined (the PK) autoincrement -with a sequence and trigger, of course-?
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 10 2013 - 21:39:54 CEST