The form in question does have a lot of fields. 10 Attachment fields, a diary field, and probably around 100 mixed fields (character, integer, menu's). We are thinking about redesigning this form as it has become congested with information.
Patrick and Axton: Those sound like great Idea's and I think when we do redesign this form in Q3 that I add a ddl or something to specify it's relationship properties (parent, child, loner). Correct me if I'm wrong, but from just a quick search, lobs in oracle are text fields greater than 4kb right? So, we wouldn't want a lot of text fields that have a length of 0 correct? On Thu, May 22, 2008 at 2:12 PM, Axton <[EMAIL PROTECTED]> wrote: > 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" > -- "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"
