All three fields are separate indexes. Guess it will just be one of those searches that won't be instantaneous. Funny thing though. Right now, since we clear the statistics, the search is pretty instantaneous. By adding the statistics, the search is fairly slow. Go figure.
Any answers on the first part? On Thu, May 22, 2008 at 10:49 AM, 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___ > -- "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 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
