Re: Is this a good case for an IOT?

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 02 Aug 2007 04:45:31 -0700
Message-ID: <1186055129.802632@bubbleator.drizzle.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.

I can. And I can think of a zillion reasons to never make statements such as I think my table is badly fragmented. I'd suggest taking a trip to http://tahiti.oracle.com and reading the Concepts books.

But while I'm being a bit unkind here ... what does it mean to load 10K records to memory? Just for comparison, on my IBM T43 notebook I can read more almost 400,000 records into memory and insert them into a table in about 1 second. Your issues may be many but table fragmentation, whatever that is, isn't one of them.

SQL> SELECT COUNT(*) FROM child;

   COUNT(*)

---

          0

SQL> set timing on
SQL> exec fast_way

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56
SQL> SELECT COUNT(*) FROM child;

   COUNT(*)

---

     200000

Elapsed: 00:00:00.01
SQL>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Aug 02 2007 - 06:45:31 CDT