Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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: dean <deanbrown3d_at_yahoo.com>
Date: Thu, 02 Aug 2007 06:36:51 -0000
Message-ID: <1186036611.775705.42650@b79g2000hse.googlegroups.com>


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:

  1. total records 175K
  2. total records 125K
  3. number of fields: 5
  4. number of fields: 20 (various types)
  5. time to load 10K records to memory: 35s
  6. 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 used a server-side cursor to scroll through the records. This is over a 2000 mile network, with client and server separated this distance. I tried varying the fetch size in the connection string, and the cache and block read sizes on the ADO front, but its as optimal as I have been able to get it. Received on Thu Aug 02 2007 - 01:36:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US