Thanks for the question, Sing.
Asked: July 12, 2000 - 5:40 pm UTC
Last updated: March 17, 2005 - 8:32 am UTC
Version: 8.0.6
Viewed 1000+ times
You Asked
Tom,
I have a table Table_A with the following columns:
Col1 VARCHAR2(200),
Col2 VARCHAR2(50),
Col3 NUMBER(38),
Col4 NUMBER(38),
Col5 NUMBER(38),
Col6 DATE,
Col7 DATE,
Col8 FLOAT,
Col9 FLOAT
Currently, data is loaded into Table_A every 5 minutes with 38 records through a script created from sqlloader throughout the day. It is 24X7 test facility. But when the facility goes into
production, 10,000 records are loaded once everyday through the
script from sqlloader. Data is always inserted into this table and never updated. Data deletion cycle start after one year of inserting data into the table.
I am trying to figure out the right storage parameters for my
table if 10,000 records are loaded at once into the table.
These are the storage parameter I came up with:
pctincrease 0
pctfree 5
pctused 80
minextents 1
maxextents unlimited
The storage parameters I am unable to come up with are the
initial and next extents. How do I determine these parameters?
Also, if the loaded increases from 10,000 records to 90,000 records what would be my right storage parameters for my table?
Thanks very much,
Sing
and Tom said...
Its mostly a matter of opinion. I myself prefer to have uniformly sized extents (all extents = same size in a given tablespace). Makes it so much easier to manage. The myth that a table should have as few extents as possible is that -- a myth. Many extents affect 2 operations:
o ddl on the table. a table with 1,000 extents will take a while to "drop"
o certain parallel operations on a highly active table. Again, it takes lots of extents to see this.
For full scans and (especially) indexed reads -- 1, 10, 100, 1000 -- its OK.
So, that aside -- Your row width looks to be about ~300 bytes give or take. 10,000 rows is 3,000,000. A months worth of data is about 90meg give or take. Try an extent size of about 100meg (you want the extents to be multiples of your multi block read count -- say about 64k typically so make it be a multiple of that). That should give you 12 extents at the end of the year.
You might consider partitions if you are going to age data out of here at the end of the year. It would make it really easy to get rid of a months worth of data and slide in the new month. You could convert the oldest partition to a "table" and archive it easily.
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment