Q
Problem solve Get help with specific problems with your technologies, process and projects.

Pre-creating tables in 9i

I am creating a new 9i database and then pre-creating the tables, importing the user data from an 8i database. 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 default to uniform size for all the tables?

I am creating a new 9i database and then pre-creating the tables, importing 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. There are no chained rows.

I have gathered the following information for all tables: table size (sum bytes of the segment), number 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 default to uniform size for all the tables?

You should build your new tablespaces using locally managed tablespaces with ASSM. You can create them with auto-allocate extents or fixed size extents. I almost always opt for fixed uniform size 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 having 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 up your objects into these tablespaces depending on object size.

Dig Deeper on Oracle Application Server

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close