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 12:36:56 -0000
Message-ID: <1186058216.119913.106280@r34g2000hsd.googlegroups.com>


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? Received on Thu Aug 02 2007 - 07:36:56 CDT

Original text of this message

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