RE: Full scan vs index

From: Storey, Robert (DCSO) <"Storey,>
Date: Thu, 10 Oct 2013 15:19:27 +0000
Message-ID: <FE4C2B093843BB4B873D754E5E0BE4DB6CCD7936_at_DCSOSVMS02.dcso.org>

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

Received on Thu Oct 10 2013 - 17:19:27 CEST