Re: Create table for append only
From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 26 May 2008 14:11:44 -0700
Message-ID: <1211836316.953786@bubbleator.drizzle.com>
>> On May 23, 7:45 pm, DA Morgan <damor..._at_psoug.org> wrote:
>>
>>
>>
>>> Andrea 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
>>> A few points of clarification. A few hundred rows every day is so
>>> insignificant I would gladly put that workload on my laptop without
>>> a performance concern. Partitioning such a small table is meaningless.
>>> The decision as to whether to use an IOT is complex and involves many
>>> factors of which the lack of updates and deletes certainly weighs in
>>> that direction. But how is the table going to be access? What percentage
>>> of those 40 columns are required to define the primary key? What version
>>> of Oracle do you have? There is a lot of relevant information you have
>>> not posted.
>>> --
>> sorry for misleading, with "append" i would mean insert, this table is
>> used for records employee's access (we have a honeywell program)
>> My version of oracle DB is 10g R3 and there are four primary key on 40
>> columns, the type of data is varchar2 (only 2 columns are date
>> format).
>>
>> Does IOT table add complexity on manage database?
>>
>> thanks
Date: Mon, 26 May 2008 14:11:44 -0700
Message-ID: <1211836316.953786@bubbleator.drizzle.com>
Andrea wrote:
> On May 26, 9:47 am, Andrea <netsecur..._at_tiscali.it> wrote:
>> On May 23, 7:45 pm, DA Morgan <damor..._at_psoug.org> wrote:
>>
>>
>>
>>> Andrea 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
>>> A few points of clarification. A few hundred rows every day is so
>>> insignificant I would gladly put that workload on my laptop without
>>> a performance concern. Partitioning such a small table is meaningless.
>>> The decision as to whether to use an IOT is complex and involves many
>>> factors of which the lack of updates and deletes certainly weighs in
>>> that direction. But how is the table going to be access? What percentage
>>> of those 40 columns are required to define the primary key? What version
>>> of Oracle do you have? There is a lot of relevant information you have
>>> not posted.
>>> --
>> sorry for misleading, with "append" i would mean insert, this table is
>> used for records employee's access (we have a honeywell program)
>> My version of oracle DB is 10g R3 and there are four primary key on 40
>> columns, the type of data is varchar2 (only 2 columns are date
>> format).
>>
>> Does IOT table add complexity on manage database?
>>
>> thanks
> > ops, i would say four constraint (not null) on 40 columns and one > primary key.
That makes more sense. Assuming the primary key is only one or two columns might first instinct would not be an IOT.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Mon May 26 2008 - 16:11:44 CDT