storage parameters for large table

From: Heiko <heiko.kuest_at_ch.abb.com>
Date: 4 Feb 2002 10:20:03 -0800
Message-ID: <b6ef798d.0202041020.ee29f9b@posting.google.com>

I'm going to (re-) setup a database for storing measurement data. There is one critical table holding the meassurement data, critical in the sense that this table is going to be very large. Up to now (in the old database) the table has approx. 290 million rows and the increase is about 5 million rows per day. The average row size is about 25 bytes.There will occur almost no updates and deletions to the table, the data will just be inserted and kept.

Do who have any recommendations concerning

storage parameters (initial, next, minextents, maxextents, pctincrease, etc.)
usage of datafiles (how many, how big, autoextend, etc.?)
anything else to care about (rollback segments, etc.?)

The setup of the current database (which I didn't setup) is like follows:

4 data files:

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ----------
---------
         7 DATASPACE                      6996099072    1708032
AVAILABLE
         8 DATASPACE                      4563402752    1114112
AVAILABLE
         9 DATASPACE                      3933208576     960256
AVAILABLE
        10 DATASPACE                      5908725760    1442560

AVAILABLE RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY ------------ --- ---------- ---------- ------------

           7 NO           0          0            0
           8 NO           0          0            0
           9 NO           0          0            0
          10 YES 1.7180E+10    4194302         1280

tablespace:
TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ -------------- ----------- -----------
DATASPACE                               20480       20480           1

MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING ----------- ------------ ---------- --------- --------- ---------

249 50 0 ONLINE PERMANENT LOGGING Any advice would be most welcome.

Thanks a lot in advance.

Heiko Received on Mon Feb 04 2002 - 12:20:03 CST