RE: IOT Tuning Question

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).