Re: IOT - comment about high writes

I think, IOTs are fine with high writes (meaning 'inserts'), when new records inserted in sequential order (artificial PK, or PK of DATE type). I am using them successfully.
Otherwise, IOTs could be a performance problem with high 'updates' (if record length changes), or records inserted not in ASC/DESC order (random use of index's leaf blocks).

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, October 30, 2001 9:45 PM

> This message was sent to the list back in August and seemed to recommend
> that IOTs shouldn't be used for high write tables.
>
> Would anyone like to help explain why this might be a bad idea?
>
> Thanks,
> Bruce Reardon
>
> -----Original Message-----
> Sent: Wednesday, 29 August 2001 7:18
>
> If your grabing all rows, and your calling all columns (some in order,
some
> display) why wouldn't it do a fts?
> There is no reason to use the index.
>
> Now if you overloaded the index, it may opt to use the index, but to what
> avail? It is is the same size of the table.
>
> In overloading, I refere to puting PersonalCode at the end of the index
you
> created, this way all teh data will be available from the index, there
will
> be no need to go to the table, altough the table will be the same size as
> the index, but the index will already be sorted.
>
> Perhaps you may want to look at IOT, if this isn't high write table.
>
>
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
> Christopher R. Spence
> Oracle DBA
> Phone: (978) 322-5744
> Fax: (707) 885-2275
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>
> -----Original Message-----
> Sent: Tuesday, August 28, 2001 3:37 PM
>
> Hi,
>
> I have a table with 3 colums : PersonalCode, Name and SpecialOrder.
>
> The PersonalCode is the primary index and I have also an index on
> SpecialOrder,Name columns.
>
> However the following select:
>
> Select PersonalCode From MyTable
> Order By SpecialOrder,Name
>
> doesn't use the SpecialOrder,Name index at all.
> If I watch it in Explain Plan window I can see that :
>
> SELECT STATEMENT, GOAL = CHOOSE 1181 16378 2456700
> SORT ORDER BY 1181 16378 2456700
> TABLE ACCESS FULL ADMIN MyTable 69 16378 2456700
>
> How can I make the index work?
>
> Thank you in advance
>
> Zsolt Csillag
> Hungary
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Reardon, Bruce (CALBBAY)
> INET: Bruce.Reardon_at_comalco.riotinto.com.au
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).