Re: Full scan vs index

From: David Fitzjarrell <>
Date: Thu, 10 Oct 2013 12:39:54 -0700 (PDT)
Message-ID: <>

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)" <> 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 [] Sent: Thursday, October 10, 2013 9:11 AM To: Storey, Robert (DCSO)
Cc: Uzzell, Stephan;; Oracle L; 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-?


-- Received on Thu Oct 10 2013 - 21:39:54 CEST

Original text of this message