Hi, Jay:
Since your table is just a table with 1M records and you have only three
columns(all of them are pk), so using IOT is really an good candicate.
The space save is not important because 1M records with three columns typically
consumes several megabytes, which is not important at all these days. If your table is
heavily DMLed, then using IOT reduced the DML to the base table, so less IO generated
and less redo.
I do not think SF's words are correct. IOT is indices, right. But Regular tables
with indexes also consumes memory in SGA, and the index on the regular do the same
thing as IOT table does. And the base table itself also consumes SGA memory.
Overflow in IOT(oracle 8i) is just heap organized, in 9i it is also index
organized(from my test), so if your table has overflow segment, and you insert more
and more data into the table, IOT *WILL* be less efficient and you need to move the
overflow segment to make the table efficient.
Regards.
zhu chao.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, November 21, 2003 12:30 AM
> Jay,
>
> On the paper, your table is indeed a good candidate for an IOT - it will save you
> the space used by the table (you will only have the primary key index). However,
> there may be gotchas. I have noticed in the past that IOTs, being primarily indices,
> have a tendency to be a bit 'sticky' in the SGA. I have seen massive processes
> wading through enormous amounts of data significantly slowing down over time with an
> IOT, and my interpretation was that the IOT was slowly filling up the SGA, letting
> fewer and fewer space to the rest.
> Also, think carefully about partitioning; it depends on how you query your table,
> mostly. It will be beneficial during inserts if you insert your rows in a random
> fashion in all partitions. Using a reverse key is also something you may want to
> consider if you have no range scan, it will help with contention.
>
> I don't think that there is an obviously good solution; it needs testing.
>
> HTH,
>
> SF
>
>
> >----- ------- Original Message ------- -----
> >From: "Jay Wade" <[EMAIL PROTECTED]>
> >To: Multiple recipients of list ORACLE-L
> ><[EMAIL PROTECTED]>
> >Sent: Thu, 20 Nov 2003 07:44:59
> >
> >Hello:
> >
> >I'm looking at trying to tune a 3rd party app and
> >was wondering if anyone
> >could tell me if my assumptions are on base. The
> >table contains three
> >columns, each is part of the primary key, with
> >about 1 million + rows. I
> >figured that it would be an ideal candidate for
> >using a partitioned IOT, but
> >since records are frequently inserted am I correct
> >in assuming that it would
> >be better to use regular partitioned table using a
> >primary key? Since this
> >is a 3rd party application I can't change much of
> >the layout, if anyone has
> >any ideas it would be greatly appreciated.
> >
> >Regards,
> >Jay
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Stephane Faroult
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: zhu chao
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
