Oracle Tablespace Table Chaining Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Features for the Data Warehouse

Tablespace Reorganization

Because they are dynamic, Oracle databases will always fragment over time and may require a periodic clean-up. In general, reorganization ensures that all tables and indexes do not have row fragmentation, and that they reside in a single extent, with all free space in a tablespace in a single, contiguous chunk. Reorganizing a tablespace can be accomplished in several ways. Rather than bring down the entire Oracle database to perform a full export/import, there are some other options.

Let’s take a look at how a tablespace may become fragmented. At initial load time, all Oracle tables within the tablespace are contiguous--that is, only one chunk of free space resides at the end of the tablespace. As tables extend and new extents are added to the tablespace, the free space becomes smaller but it still remains contiguous.

Basically, a table can fragment in two ways:

* A table extends (without row chaining)--Contrary to popular belief, this is not a problem and performance will not suffer.

* Rows fragment within the tablespace (due to SQL UPDATES)--This causes a serious performance problem, and the offending tables must be exported, dropped, and re-imported.

Tablespace fragmentation occurs when some “pockets” of free space exist within the tablespace. So, how do these pockets of free space appear? If tables are DROPPED and re-created, or if individual tables are exported and imported, space that was once reserved for a table’s extent will now be vacant.

To see the fragmentation within a tablespace, you can run the script shown in Listing 8.16.

---

This is an excerpt from "High Performance Data Warehousing", copyright 1997.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.