Re: Is this a good case for an IOT?

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 02 Aug 2007 07:31:58 -0700
Message-ID: <1186065117.689926@bubbleator.drizzle.com>

dean wrote:

> On Aug 2, 7:45 am, DA Morgan <damor..._at_psoug.org> 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.

>> 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 tohttp://tahiti.oracle.comand 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
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -

>>

>> - Show quoted text -

> 
> We are reaading in one row at a time in the client app, reading 5
> different fields, and loading them into an in-memory container. They
> all have to be loaded in at the start of the application.
> 
> How do you explain the differential between A and B, if all other
> things are equal?
> 
> -Dean
> 
> ps What's inside your fast_way procedure?

There are hundreds of possible explanations and Oracle isn't a black box. You need to use the available tools to determine what is happening. The first test I would do is go to the server and run the two SQL statements in SQL*Plus using 'set timing on'. Every microsecond after how long that takes is your network and your application.

BTW: Caching 175K rows in a client after dragging them across the network is about as inefficient a methodology as I can imagine. Is there an architect on the project?

With respect to FAST_WAY ... the code is in Morgan's Library at www.psoug.org under "ARRAY PROCESSING."

-- 
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 - 09:31:58 CDT