initial extent allocation [message #153401] |
Mon, 02 January 2006 02:11 |
kinjal
Messages: 124 Registered: June 2001
|
Senior Member |
|
|
Hi,
how can I calculate the initial extent at the time of table creation or how do I know that for existing table whether it is appropriate or not?
And one more thing, allocating the initial extent with high value affect the performance or not?
I mean is there any impact on performance because of extent size?
Please tell me in brief.
Kinjal.
|
|
|
|
Re: initial extent allocation [message #153525 is a reply to message #153401] |
Tue, 03 January 2006 00:17 |
kinjal
Messages: 124 Registered: June 2001
|
Senior Member |
|
|
Hi,
Still I have some confusions.
I have my tablespace with 65536 bytes (64K) initial extent and 8k block size.
So is it like,
If I create a table then by default the initial extent will be of 64K?
Or can I define the initial extent with small size?
And one more thing, still I don't understand how to calculate the size of the extent while creating a table?
I mean on what basis we decide the size of the extents?
And for DML and for static tables what is proper? uniform size or nonuniform size?
Kinjal
|
|
|
Re: initial extent allocation [message #153536 is a reply to message #153525] |
Tue, 03 January 2006 01:00 |
tarundua
Messages: 1080 Registered: June 2005 Location: India
|
Senior Member |
|
|
-->If I create a table then by default the initial extent will be of 64K? Or can I define the initial extent with small size?
You can specify a smaller one but Oracle will assign a minimum 64K by default.
look here
SQL> select tablespace_name, initial_extent , next_extent
2 from dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
EXAMPLE_SCH 516096 516096
VOTER_DET 65536
SQL> create table dumm1(n1 number)
2 storage (initial 32K) tablespace voter_det;
SQL> select INITIAL_EXTENT from dba_tables
2 where table_name='DUMM1';
INITIAL_EXTENT
--------------
32768
SQL> select bytes from dba_extents
2 where segment_name='DUMM1';
BYTES
----------
65536
-->I mean on what basis we decide the size of the extents?
It depends on the expected growth of the tables.
Look, extents hold rows for a table so you can calculate the size of a row ( sum of bytes held by all the columns). And then how many rows you want to store in an extent, its up to you.
-->And for DML and for static tables what is proper? uniform size or nonuniform size?
Cant say better to wait for other's comments ( someone good in database designing). Open for debate.
|
|
|
|
|