Re: Is this a good case for an IOT?

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 02 Aug 2007 10:39:19 -0500
Message-ID: <46b1ee8f$0$11761$88260bb3@free.teranews.com>

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
dba_at_nospam.peasland.net
http://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 from http://www.teranews.com

Received on Thu Aug 02 2007 - 10:39:19 CDT