RE: Re: IOT Tuning Question

Zhu Chao,

   You are right to say that with a heap organized table you also have the index to encumber the SGA and indeed you are right to say that, as I put it, what I said is not totally correct. I should have been more specific.  The reference to _partitioned_ IOTs implicitly associated them to full partition scans in my mind, because the case I was referring to was some massive swoop among a lot of data, with many scans. In such a case, then indexes in general, and IOTs in particular, tend to stay much longer than required in memory, which may become a problem over time with long running processes (while table blocks are prime candidates for replacement after full scans). Quite obviously, if you are doing mostly indexed accesses, the picture may be different.   I don't think that with 3 columns, unless they are well-filled VARCHAR2(4000) columns (you never know, with 3rd party software ...) overflow will be much of a problem. I'd rather fear contention, but of course it depends on the level of concurrency.

SF

>----- ------- Original Message ------- -----
>From: "zhu chao" <chao_ping_at_vip.163.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Thu, 20 Nov 2003 17:50:11
>
>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).