!= will typically cause a table scan of some type; it could be a partial table scan (of a small number of rows) or a full table scan, depending on how Oracle breaks down and processes the query.
Axton On Thu, May 22, 2008 at 12:49 PM, J.T. Shyman <[EMAIL PROTECTED]> wrote: > ** > > One more comment: You mentioned that all three fields are indexed. Are they > included in a compound index (i.e. all three fields in one index) or are > they in three separate indexes? You might (and I stress might) see some > improvement in performance with a single index that contains all three > fields. > > > > However, the != will still force a table scan I believe. > > > > --- J.T. Shyman > > > > ________________________________ > > From: Action Request System discussion list(ARSList) > [mailto:[EMAIL PROTECTED] On Behalf Of Robert Halstead > Sent: Thursday, May 22, 2008 12:38 PM > To: [email protected] > Subject: Oracle Index Performance > > > > ** Ladies and Gents, > > We have been constantly trying to improve performance on a form with over 1 > million records in it by analyzing indexes, or clearing the statistics for > the table in oracle. The last thing we did was clear the statistics for our > form BZ:Incident because for some reason searches in Remedy, even though we > were searching on index fields, were not using the indexes in Oracle. By > clearing the statistics, I believe we're forcing oracle the use the indexes > resulting in the searches . However, searching on a field that is not > indexed now performs a database time out error in Remedy. > > My question: Where is the happy place? When you guys analyze statistics, > to what percent? Also, how often do you find yourself analyzing indexes? > > On a related question, performing searches in Remedy using the != operator > does not utilize the index for that specific field. I'm performing a search > on a form where 'Parent' = $NULL$ and 'Link Type' = $NULL$ and 'Incident ID' > != $tmp_IncidentID$. Essentially I'm wanting to return all tickets that are > not associated with a parent and not return the incident I'm trying to > link. > > How would I change this query so that I utilize the indexes? The search > seems to perform a full table scan against the table in oracle. All three > fields are indexed on the form. > > I sure that this is covered in the Performance and Tuning class, I just have > yet to take it. > > Environment: > AR System 6.3 Patch 20 > Oracle 9i > > -- > "A fool acts, regardless; knowing well that he is wrong. The ignoramus acts > on only what he knows, but all that he knows. > The ignoramus may be saved, but the fool knows that he is doomed." > > Robert Halstead __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the > Answers Are" html___ > > __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
