In our new application, we designed the database and created the datamodel.
we even sized the tables came with storage parameters for each table.
but our DBA people tell that they will give us three tables sapces
1.data1 with uniform extent size 160K
2.data2 with uniform extent size 5MB
3.data3 with uniform extent size 160MB
now they are telling us to
1.create tables which are going to be less than 5MB in data1 with no storage
parametes for the table
2.create tables which are going to be less than 160MB in data2 with no
storage parametes for the table
3.create tables which are going to be more than 160MB in data1 with no
storage parametes for the table same case with the indexes. it doesn't look
reasonable to me.
because for a table with an estimated size of 120MB we place it in data2. as
we create there it takes tablespace's storage parameters and it has to give
5MB 24 times to make it 120 as the table grows in size which i think is
expensive. and for tables with say 2k size(even less) i have to keep in
data1 with 160k, where the rest of the space gets useless.
DBA claim that Numerous test by Oracle Corporation and others proved that
this design provides ultimate performance and prevents fragmentation. first
of all my questions are 1. are they right doing so? or am i wrong 2. if they
are not right i have to prove them with whatever they claim is wrong(either
thru documentation or thru queries which should show a solid proof to make
them convinced.)as our application so complex we don't want any performance
Ur point is quite valid but just having more number of extents does not fragment a database. Normally one tends to think that it is not right to have more number of extents, rather have a single extent of a larger size- but then I ask, what is the loss? If at any point if the data is deleted, it can be reused and with LMT, u dont even need to coalesce the tablespaces.
"Most importantly -- it is IMPOSSIBLE to have free space fragmentation in a
locally managed tablespace with uniform extents. Since every extent is the size
of every other extent -- ANY extent is the correct size for ANY objects next
extent. If you have 500m free in a locally managed uniform tablespace -- you
really do have 500meg free
In Oracle8i, use locally managed tablespaces with uniform extents. Oh yeah, if
someone says "but that'll cause our objects to have many extents" just say "so
what, who cares". It is perfectly OK to have objects with many 100's of
So if the DBA has planned for LMT with uniform extents, u dont have to worry. Hope it is clear now
But I would like to know in what way it helps.
It is going to occupy more no. of extents and if a full table scan is going to be done then it has to go through all the extents(extents need not be contiguous). So the amount of time it takes to read the data increases and hence some performance degradation will be there.