Re: Full scan vs index
From: David Ramírez Reyes <dramirezr_at_gmail.com>
Date: Thu, 10 Oct 2013 09:11:15 -0500
Message-ID: <CAJt=wvV=2x=_KnbMrXV_pRf3W1BOF6y+iKvnrR0dTqEnAtoN7w_at_mail.gmail.com>
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-?
Date: Thu, 10 Oct 2013 09:11:15 -0500
Message-ID: <CAJt=wvV=2x=_KnbMrXV_pRf3W1BOF6y+iKvnrR0dTqEnAtoN7w_at_mail.gmail.com>
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-lReceived on Thu Oct 10 2013 - 16:11:15 CEST