Lesson 1

Reorganizing Oracle tables has been around since the very earliest versions of the Oracle database. Tables within Oracle tend to become more disorganized especially when they are very heavily used. However, many new DBAs falsely assume that a reorganization is necessary whenever a table extends. In reality, studies have shown the tables that are in many extensible often outperform tables that are in a single extent because the database load is been spread across more cylinders on the disk and thereby relieves additional I/O bottlenecks. This is not to say the database reorganization is a bad thing. There are many components of Oracle segments especially free lists and row sequencing factors that make table reorganization a very important tool of the Oracle DBA.

While the Oracle DBA does not have direct control over the operating environment, there are several things that the DBA can do to reduce the amount of disk I/O that Oracle must perform. This module is going to explore different tools and techniques that you can use in order to re-sequence Oracle rows, coalesce the Oracle free lists and give you tools that are needed to identify those tables that are most in need of a table reorganization. Remember, the savvy DBA does not reorganize table unless there will be some direct performance benefit from the reorganization of the table. By the end of this module, you will be able to:

1. Re-sequence rows in an Oracle table
2. Re-sequence rows using CTAS
3. Remove table extents
4. Describe the performance impact of chained rows
5. Define the performance ramifications of freelists

The next lesson gives an overview of row re-sequencing in Oracle.

Reorganizing Oracle tables can be an effective way to reduce disk I/O and improve database performance. Here are some steps you can take to reorganize Oracle tables:

1. Analyze the tables: Use the Oracle Analyze command or the Automatic Workload Repository (AWR) to collect statistics on the tables, including their size, row count, and distribution of data. This will help you identify which tables are most in need of reorganization.
2. Determine the fragmentation level: Check the fragmentation level of the tables by running queries that show the number of free blocks and the average row length. This will help you identify which tables are most fragmented and require reorganization.
3. Choose a reorganization method: There are several methods for reorganizing Oracle tables, including Export/Import, ALTER TABLE MOVE, and CTAS (Create Table As Select). Each method has its own advantages and disadvantages, and the best method will depend on the specific needs and requirements of your database.
4. Create temporary tables: Before reorganizing a table, create temporary tables to store the data. This will help ensure that no data is lost during the reorganization process.
5. Reorganize the tables: Use the chosen method to reorganize the tables, moving the data to a new tablespace or segment, or creating a new table with the same data.
6. Update indexes and constraints: After reorganizing the tables, you will need to update the indexes and constraints to reflect the new table structure.
7. Test the performance: After reorganizing the tables, test the performance of the database to ensure that the reorganization has reduced disk I/O and improved overall performance.

By following these steps, you can reorganize Oracle tables to reduce disk I/O and improve database performance. However, it's important to plan and test the reorganization carefully to ensure that it meets the needs of the database and does not introduce any new performance or stability issues.