Lobs are varchar > 4000 characters, binary data (attachments). Lobs have several implications on Oracle. Read this article for one example: http://arswiki.org/wiki/Oracle_Performance_Tuning#LOB_Storage
The above article covers storage size, which impacts many things: - physical reads to get data - physical reads associated with full/range table scans Axton On Fri, May 23, 2008 at 11:50 AM, Robert Halstead <[EMAIL PROTECTED]> wrote: > ** 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 __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"
