| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Is this a good case for an IOT?
On Aug 2, 11:39 am, Brian Peasland <d..._at_nospam.peasland.net> 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.
>
> Have you looked at the Explain Plan for the two queries? Have you looked
> at a 10053 trace to see how the CBO is handling this? These are but two
> of the many tools that Daniel is alluding to in this thread.
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -
>
> - Show quoted text -
This is the trace for the query running on our local development machines:
select user_preference.*, user_preference.ROWID
from
user_preference where user_i = 'DEAN'
call count cpu elapsed disk query current rows
Parse 2 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 885 0.14 0.13 0 4999 0 14142
total 888 0.14 0.13 0 4999 0 14142
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
I am still trying to get the same for the client site, on its long distance connection.... Received on Thu Aug 02 2007 - 17:24:11 CDT
 |
 |