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?

Re: Is this a good case for an IOT?

From: Brian Peasland <>
Date: Thu, 02 Aug 2007 10:39:19 -0500
Message-ID: <46b1ee8f$0$11761$>

dean wrote:
> On Aug 1, 9:57 pm, DA Morgan <> 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
>> (replace x with u to respond)
>> Puget Sound Oracle Users 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.



Brian Peasland

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
Received on Thu Aug 02 2007 - 10:39:19 CDT

Original text of this message