Re: Create table for append only

On May 23, 11:45 am, "Syltrem" <syltremz..._at_videotron.ca> wrote:
> > "Mark D Powell" <Mark.Pow..._at_eds.com> a écrit dans le message de news:
> > 5373c4fb-be92-45a0-b3bf-> fa35dc867..._at_f36g2000hsa.googlegroups.com...
> > > On May 23, 9:04 am, Andrea <netsecur..._at_tiscali.it> wrote:
> > > Hi guys,
> > > sorry for my question but i would like to give some tips for create a
> > > table in 10g database.
> > > This table have 40 colums and start with few rows, about 2 thousand,
> > > but it will increase soon (it appends hundred rows every day) only
> > > through append transactions. I thinked to create a IOT table (index
> > > organized) and maybe partition it also.
>
> > > So if this kind of table has not updates but only append and select,
> > > which are the best practices in this case?
>
> >>  thanks for advice
> > > Andrew
>
> > In Oracle and with SQL the DML operations are select, insert, update,
> > and delete.  What do you mean by "append".  Unless you are using only
> > direct path insert tasks the rows will be inserted where ever in the
> > table that free space exists.  If you never delete then logically all
> > inserts will be to the end of the table.  On the other hand if you use
> > an IOT the inserts are in PK order so unless the key is sequential the
> > rows will be inserted into the proper key location.
>
> > Also what version of Oracle?
>
> > So exactly what are you asking?
>
> > -- Mark D Powell --
>
> The OP wants to know it it's a good thing for a table heavily (more or less)
> inserted into, to be organized as IOT.
>
> Syltrem- Hide quoted text -
>
> - Show quoted text -

How good a partioned IOT will work for the OP will depend on how the data is accessed. If much of the access is via secondary indexes and the PK is not sequential then data will get moved and the secondary index logical rowid will be wrong requiring extra work to access the rows. In a case like this a tradional heap table would likely be more efficient.

Also hundreds of rows per day is not much data and I am not sure partioning is necessary. For partitioning to be truely useful the majority of acess will need to be able to provide the partition key in the query.

IOT's are very useful when the table has 6 columns and 5 of the 6 make up the PK. It saves a lot of space.

IOT's are good for certain applicaitons like being used for inverted search indexes.

If I had a 40 column table and only one or two columns make up the PK I would probably use a heap table.

The choice of heap vs IOT depends on the access patterns.

HTH -- Mark D Powell -- Received on Fri May 23 2008 - 12:08:47 CDT