-
database planning
First, I am dba beginner.
I have to create database.Databse will contain 2 tables.
First table with average row size = 159 bytes, and second table with average row size = 86 bytes.
Every day first table will be loaded with approximate 80 000 new rows per hour, and second with approximate 10 new rows per hour.
Tables will be Index-organized tables.Is it good or bad idea?
HD where will be database is with size allocation unit (cluster size on win2000) = 64k.
Which db_block_size I have to use?
Is there any way (mathematical) to calculate it?
Which increment value for datafile extend I have to use?
Which size of extents for my tables I have to use?
How to calculate others storage parameters?
Thanks
-
Please give more info to the reader. Then "we" can take it step by step.
In what environment are you working on?
Planned usage of data?(number of concurrent end_users) etc.
All of this is important to determain what your db should look like.
OK?
-
What do you think when you asked me about environment?
Is it hardver & software environment?
I'm working on windows 2000 server,HD 20G,RAM 2G.
Number of concurrent end_users will be 50
-
I hope I can help you through this example, but first....in very basic form.
The environment questionroduction,datawarehousing etc.??
You're going to have serious HD SPACE PROBLEMS. At the rate which data is allocated to the 1st table, you're going to run out of disk space in the first 7 DAYS or so. (Approc. 3 gigs+ a day)
The 2gig memory seem to be good for 50 users. Do some reading on the SGA. You might find it necessary to change the SGA for better performance.
The db_block_size I use is 2048,which is sufficiant for MY DB.
Look at this SQL to get some direction on the datafile ext. You can change the values in a later stage with ORACLE STUDIO.
-------------------------------------------------
CREATE TABLESPACE "MM_GEO_DATA"
DATAFILE 'D:\ORACLE\ORADATA\MM\MM_GEO_DATA.ORA'
SIZE 52428800
AUTOEXTEND ON NEXT 4500M
DEFAULT STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50) ONLINE PERMANENT;
-- Create table
create table C_ALIGNMENT_LEFT
(
ID NUMBER not null,
SURVEY_ID NUMBER,
SEGMENT_NUM NUMBER,
FROM_DISTANCE NUMBER,
TO_DISTANCE NUMBER,
FROM_KM NUMBER,
FROM_M NUMBER,
TO_KM NUMBER,
TO_M NUMBER,
AG_ALLR BLOB,
NETWORK_ID NUMBER
)
tablespace MM_GEO_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 160K
next 5680K
minextents 1
maxextents 505
pctincrease 50
);
-- Create/Recreate primary, unique and foreign key constraints
alter table C_ALIGNMENT_LEFT
add constraint PKK_C_ALIGNMENT_LEFT primary key (ID)
using index
tablespace MM_DEO_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 80K
next 144K
minextents 1
maxextents 505
pctincrease 50
);
Good luck!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|