by Burleson Consulting
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Oracle Tablespace Considerations
Choosing how to place tables and indexes into tablespaces has a
great impact on the performance of your data warehouse. Since the
designer has many choices, it is a good idea to explore the
available options. In general, the following characteristics apply
for data warehousing:
* Group tables with similar characteristics in a tablespace.
For example, all tables that are read-only could be grouped into a
single, read-only tablespace. Tables with random I/O patterns could
also be grouped together; all small tables should be grouped
together, and so on.
* Create at least two tablespaces for use by the TEMP
tablespaces. This approach has the advantage of allowing the
designer to dedicate numerous TEMP tablespaces to specific classes
of users. As we know, the TEMP tablespace is used for large sorting
operations, and assigning appropriately sized TEMP tablespaces to
users depending upon their sorting requirements can enhance
performance. Remember, in a distributed SQL query, the rows are
fetched from the remote database and sorted on the Oracle that
initiated the request. The use of multiple TEMP tablespaces has the
added advantage of allowing the developer to switch TEMP tablespaces
in case of disk failure.
* Use many small, manageable tablespaces. This approach makes
it easier to take a single tablespace offline for maintenance
without affecting the entire system. Oracle highly recommends that
no tablespace should ever become greater the 10 GB, and placing all
tables into a single tablespace also reduces recoverability in case
of media failure. However, this approach does not advocate creating
a single tablespace for each table in a system. For example, Oracle
recommends that the system tablespace contain only systems tables,
and that a separate tablespace be created for the exclusive use of
the rollback segments.
* Place the rollback segments in a separate tablespace. This
isolates the activity of the rollback segments (which tend to have a
high I/O rate) from the data files belonging to the application.
* If you have Oracle8, Partition large table and indexes into
separate tablespaces. For a discussion of this method, see Chapter
14, Oracle8 for the Warehouse.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.