Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Is this a good case for an IOT?
dean wrote:
> On Aug 2, 11:39 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:
>> dean wrote: >>> On Aug 1, 9:57 pm, DA Morgan <damor..._at_psoug.org> wrote: >>>> dean wrote: >>>>> 9.2i, 10g, Windows platforms. >>>>> Hello all, >>>>> We have a table USER_PREFERENCE, which gets updated frequently by >>>>> various users, and appears to be badly fragmented (takes relatively >>>>> large per-record timespan to load into memory, compared to other >>>>> tables). It has 5 fields: >>>>> Name Null? Type >>>>> ----------------------------- -------- -------------------- >>>>> USER_I NOT NULL VARCHAR2(30) >>>>> FORM_I NOT NULL VARCHAR2(100) >>>>> CATEGORY_I NOT NULL VARCHAR2(100) >>>>> KEY_I NOT NULL VARCHAR2(100) >>>>> VALUE_X VARCHAR2(2000) >>>>> The primary key is on the first 4 fields. There are no NULL values for >>>>> the last field, VALUE_X. >>>>> Is this a good candidate for an index organized table? The hope being >>>>> that this would then maintain itself over time and solve the loading >>>>> issue. >>>>> Thanks for any tips! >>>>> DeanB >>>> Possibly but how is it accessed and on what basis do you make the >>>> statement "appears to be badly fragmented," what does that mean, and >>>> what has that got to do with anything? Extents are never laid down >>>> on disk contiguously ... not even in an IOT. >>>> -- >>>> Daniel A. Morgan >>>> University of Washington >>>> damor..._at_x.washington.edu (replace x with u to respond) >>>> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text - >>>> - Show quoted text - >>> Well, I am comparing it (table A) to another table (B) which loads up >>> into memory much faster - compare the following properties: >>> A: total records 175K >>> B: total records 125K >>> A: number of fields: 5 >>> B: number of fields: 20 (various types) >>> A: time to load 10K records to memory: 35s >>> B: time to load 10K records to memory: 10s >>> I can't think of any other reason why B should load so much faster >>> than A; from the structure it should load slower. >> Have you looked at the Explain Plan for the two queries? Have you looked >> at a 10053 trace to see how the CBO is handling this? These are but two >> of the many tools that Daniel is alluding to in this thread. >> >> HTH, >> Brian >> >> -- >> =================================================================== >> >> Brian Peasland >> d...@nospam.peasland.nethttp://www.peasland.net >> >> Remove the "nospam." from the email address to email me. >> >> "I can give it to you cheap, quick, and good. >> Now pick two out of the three" - Unknown >> >> -- >> Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text - >> >> - Show quoted text -
Do you work with anyone that has Oracle experience?
Tracing SQLNET is not what we are talking about.
Explain Plan:
http://www.psoug.org/reference/explain_plan.html
Trace:
http://www.psoug.org/reference/trace_tkprof.html
Look at 10053 and 10132.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Aug 02 2007 - 18:47:35 CDT