Date: Sat, 17 May 2008 14:13:28 -0700 (PDT)
I have a question regarding the tablespace size. Assume that I create the new database and now I wanted to create the Application for some company that has a feature like this:
Company: WCA Wireless Net
Numbers of orders per year: 44 million
Average number of equipment sales per phone: 2.5 Number of phone plans: 13
Number of phones: 100
And I must create tables like:
phone table: phone_id (PK), phone_name, model_no, phone_price plan table: plan_id (PK), plan_name, plan_price plan_state_table: plan_id (FK), state_id (FK) state table: state_id (PK), state_description equipment table: equip_id (PK), equip_name, phone_id (FK), equip_price
order table: order_id (PK), plan_id (FK), phone_id (FK), equip_total, order_total
order equip table: order_id, equip_id
and also I can say that:
Data sample for phone table and example for analyzing row size/extent
Insert into phone values (phone_id_seq.nextval, 'Motorola LX','M4569BL',99.89);
analyze table phone compute statistics;
select table_name, avg_row_len from user_tables where table_name = 'PHONE';
avg_row_len = 29 bytes
so I can understand that This application expects to store approx. 100 different phones at a time: 29 bytes * 100: ~3K worth of data for the phone table. And we can say that extent sizes could be 64K (very small) to store the data in this table.
I wanted to create 2 separate tablespaces for tables and for indexes.
The question is what is the best option for the sizes of these
tablespaces? And why?
How can understand that if I wanted to create the different application or managing the size of the tablespaces for the different accept.
For example for the application above I create the tablespaces with these options:
SQL> create tablespace tables
2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORCL2\tables01.dbf' 3 size 100m autoextend on maxsize 200m 4 extent management local uniform size 100k;
SQL> create tablespace indexes
2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORCL2\indexes01.dbf' 3 size 100m autoextend on maxsize 200m 4 extent management local uniform size 100k;
I know this is the just practice; I created as a part of my personal server at home but how about the real word? What is going to be the best options and why?
Thank you all Received on Sat May 17 2008 - 16:13:28 CDT