What extent size would your recommend here (uniform tablespace)? [message #336216] |
Fri, 25 July 2008 04:20 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
salkawari
Messages: 16 Registered: April 2008
|
Junior Member |
|
|
Hello,
1. Can you tell me how to calculate / estimate the extent allocation for the following tables please?
Let us assume uniform tablespace allocation is required.
I also assume a number requires 5 bytes and a date 8 bytes.
Here is an example table, it will have 500,000 rows initially.
table1:
col1 NUMBER(16) NOT NULL PK
col2 VARCHAR2(12 BYTE) NOT NULL,
col3 VARCHAR2(200 BYTE) NOT NULL,
col4 VARCHAR2(10 BYTE) NOT NULL,
col5 VARCHAR2(200 BYTE) NOT NULL,
col6 NUMBER(10) NOT NULL,
col7 VARCHAR2(3 BYTE) NOT NULL,
col8 DATE NOT NULL,
col9 DATE,
col10 DATE,
col11 NUMBER(3) NOT NULL DEFAULT 0,
col12 VARCHAR2(1 BYTE) ,
col13 VARCHAR2(80 BYTE),
col14 VARCHAR2(80 BYTE),
col15 NUMBER(10),
col16 VARCHAR2(200 BYTE) ,
col17 VARCHAR2(200 BYTE) ,
col18 CHAR(1 BYTE) NOT NULL,
space estimate =(5+12+200+10+200+5+3+8+8+8+5+1+80+80+5+200+1)*500000/1024/1024 = 397 MB
There are other tables with have the following sizes (I used the same calculation as described above)
1960MB, 138MB, 25MB, 16MB, 5MB (and 5 other tables around 1MB is size). They all have primary keys over the 1st or 1st and 2nd columns.
All the tables are expected to grow annually by 20% for the next 3 years. All tables will be updated and inserted into fairly regularily.
2. Would you recommend an extent size of 100M for the big table? (it will grow from 1960MB to 3390MB).
3. What about the other tables? Would you recommend putting the 138MB table into that same tablespace? (it will grow from 138MB to 239MB).
4. Would you put the other remaining tables into a tablespace with the default extent size of 1M?
5. What about the indexes, how do I calculate /estimate thier size? The will just be normal primary key indexes without any functions etc.
Thanks for your help!
|
|
|
|
|
|
|
|
|
|
|