Re: IOT Tuning Question

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" <ORACLE-L_at_fatcity.com> 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" <fish_dba_at_hotmail.com>
> >To: Multiple recipients of list ORACLE-L
> ><ORACLE-L_at_fatcity.com>
> >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: sfaroult_at_oriolecorp.com
>
> 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: ListGuru_at_fatcity.com (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: chao_ping_at_vip.163.com

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: ListGuru_at_fatcity.com (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).