Hi John,
I am creating a new 9i database. I plan to
pre-create the tables, then import the user data from an 8i database.
I don't have the application details such as rate of growth or the
frequency of the insert, update and delete.
BTW, there are no chained rows. I have
gathered the following information for all tables: table size (sum
bytes of the segment), # or rows, blocks, empty blocks, average space
and average row length. Table sizes vary from 3 gig, 2 gig, 1 gig ...
40960.
Under 9i, I would like to know if I should
have tablespaces created with different uniform size.
Once I pre-create the table, I also have the
opportunity to adjust the table extent (by dropping and recreating the
table prior to the import). Should I specify initial and next or use
the default uniform size for all the tables?
Answer:
You should build your new tablespaces using
locally managed tablespaces with ASSM. You can create then with
auto-allocate extents or fixed size extents, I almost always opt for
fixed uniform sized extents. This way all the extents are the same
size in the tablespace and can be reused by any object. As far as
sizing, you have to remember that an extent can only hold one
object. If you create large extents and place indexes in them,
there could be a lot of wasted space.
On the other hand, locally managed tablespaces do not hit the data
dictionary for extent information and have many extents will not
impact performance. There is no need to try and import a table into
one extent. My recommendation is to create one tablespace for
indexes and another for tables. Make the table tablespace extent
size equal to the smallest table you are going to place in it (if
you have some large tables with very small tables you might need to
make larger extents and have some wasted space with the small
tables). Make the index tablespace extents smaller since the
objects are smaller. Don't worry about a table that ends up with
100 extents, it will not impact performance.
Another alternative is to create small,
medium, and large tablespaces and divide your objects into these
tablespaces depending on object size.
and include the URL for the page.
