Create a flag field on the form, then use a filter to set it. The field would default to 0, then be set to 1 by a filter if 'Parent' = $NULL$ and 'Link Type' = $NULL$ and 'Incident ID' != $tmp_IncidentID$. You could then index and query on this field. It's a crappy solution, but the best available option I see with the tools available. YMMV depending on the cardinality of the resulting data.
"The search seems to perform a full table scan against the table..." Learn how to use the autotrace features of sql*plus. http://asktom.oracle.com/tkyte/article1/autotrace.html If a partial table scan on 1m records causes a timeout, you may want to evaluate the performance of your db and how much storage the table is actually using. How is the table structured? Does it contain lobs, does it have a lot of columns, etc. These are design decisions that may result in poor performance. Axton On Thu, May 22, 2008 at 12:38 PM, Robert Halstead <[EMAIL PROTECTED]> wrote: > ** 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 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
