Object Creation parameters

[prosenjit]

CREATE TABLE DEPOSIT (
DEPOSITINTTRANID NUMBER (20) NOT NULL,
INTAMNT NUMBER (10,4) NOT NULL,
INTCALCDATE DATE NOT NULL,
DEPOSITTRANID NUMBER (20) NOT NULL,
PRIMARY KEY ( DEPOSITINTTRANID )
USING INDEX
TABLESPACE USR PCTFREE 10
STORAGE ( INITIAL 50K NEXT 50K PCTINCREASE 0 ))
TABLESPACE USR
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 51200
NEXT 51200
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;

I have a script as this . I know what each parameter such as
<<
TABLESPACE USR PCTFREE 10
STORAGE ( INITIAL 50K NEXT 50K PCTINCREASE 0 ))
TABLESPACE USR
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 51200
NEXT 51200
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
>>
means.

Somebody elaborating them in a lay man's language would be great anyway.

But how does one calculate the values for these parameters so that it really makes some difference in the performance of the database. I think one should know these deciding factors so that she/he can appreciate the use of it.

.....Thanks In Advance.....

 

[Kavy]

Hi Prosenjit,

firstly, one has to decide what kind of table it is. wether it is a lookup table(master table) or any custom table.
Based in this we can go to the next question : What kind of data will be stored, ie. how often data will be written to the table(insert), how often updated and how often deleted. So, based on the above three(insert, update, delete), u have to calculate the PCUFREE and PCTUSED.
As the data in the table grows, more space will be occupied. so, if u know that the dataload will be high, then u can allocate a large of chunk of space, so this factor is decided by PCTINCREASE, ie, by what percent the next extent needs to be increased.
usually, initrans, maxtrans are not disturbed.

I hope this helps!!

Kavy

 

[prosenjit]

Hi kavi,

Yes it has. But a bit more on this is expected.

Regards
Prosenjit