Re: Is this a good case for an IOT?

From: dean <deanbrown3d_at_yahoo.com>
Date: Thu, 02 Aug 2007 08:16:40 -0700
Message-ID: <1186067800.021082.268610@x40g2000prg.googlegroups.com>

On Aug 2, 10:31 am, DA Morgan <damor..._at_psoug.org> wrote:
> 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-Hidequoted 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.comandreading 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 atwww.psoug.orgunder "ARRAY PROCESSING."
> --
> 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 -

Excellent tips there in your array processing page. Thanks for the link, I will look into this.

Dean Received on Thu Aug 02 2007 - 10:16:40 CDT