Reorganizing Tables
As a DBA, one of the tasks that you will need to perform periodically is monitoring the number of chained rows in a table, as well as its fragmentation. If either or both of these become too high, you will need to reorganize that table to ensure that you have good performance.

Prior to Oracle 8i, the two most common methods of doing this were to EXPORT, DROP and then IMPORT the table back into the database, or issue a CREATE TABLE & AS SELECT to create a new copy of the table, and then drop the original and rename the new one to the name of the original table. Both of these methods had some drawbacks, such as the requirement to either re-establish security or re-create indexes that were affected. The worst thing was that, while the reorganization was taking place, you were not allowed to access the tables, causing downtime for the database, or that portion of it.

In Oracle 8i a new command option was introduced to the ALTER TABLE syntax to address this issue specifically the MOVE option. With the ALTER TABLE <tablename> MOVE syntax it is now possible to reorganize the table while ensuring that security information and indexes are retained, and the table is accessible to users while the MOVE is being performed!! If your table was created on the ACCOUNTING tablespace, for example, and you wanted to move it to the new ACCOUNTING2 tablespace you just created on a new set of disks, you can issue the following command:

ALTER TABLE Customers MOVE TABLESPACE Accounting2

If you simply wanted to re-organize the table and keep it on the same tablespace, you can do that too by issuing the command:

ALTER TABLE Customers MOVE TABLESPACE Accounting

In either case, Oracle will rebuild the table in the target tablespace, allow users to query the existing table while the move is taking place, keep the existing permissions assigned to the table, and rebuild the indexes after the operation is completed. Optionally, you can also specify storage clause characteristics to change the extent sizes and other parameters (with the exception of FREELISTS or FREELIST GROUPS) if you find that the pervious storage parameters are no longer compatible with the table access and storage patterns.

Of course, there is a downside as well. Because the old copy of the table is kept until the move completes to allow queries to take place, this means that you will need sufficient disk space for both the old and new versions of the table typically double what was required before.

Note that the MOVE option of the ALTER TABLE syntax only applies to non-partitioned or index-organized tables. If you want to move partitions or sub-partitions to another tablespace, or reorganize them, you need to issue the ALTER TABLE & MOVE PARTITION or ALTER TABLE & MOVE SUBPARTITION commands for each partition or sub-partition.

Despite its main drawback the requirement for double the disk space this one command can make it extremely easy to reorganize tables for better performance without all the headaches of EXPORT and IMPORT or CREATE TABLE & AS SELECT.

Reorganizing Columns
While it has been possible to add new columns to an existing table in Oracle for quite a while now, until Oracle 8i it was not possible to drop or remove a column from a table without dropping the table first and then re-creating it without the column you wanted to drop. With this method, you needed to perform an EXPORT before dropping the table and then an IMPORT after creating it without the column, or issue a CREATE TABLE ... AS SELECT statement with all of its associated headaches (see above).

In Oracle 8i, we now have a way of marking columns UNUSED and then dropping them at a later date. Oracle is a little behind the times here compared to SQL Server, which does not require a complete rebuild of the table after dropping the column, but I'm just happy that I have the feature and hope that they'll improve it in Oracle 9i.

To get rid of columns with this new method, the first step is to issue the ALTER TABLE <tablename> SET UNUSED COLUMN <columnname>, which sets the column to no longer be used within the table but does not change the physical structure of the table. All rows physically have the column's data stored, and a physical place is kept for the column on disk, but the column cannot be queried and, for all intents and purposes, does not exist. In essence, the column is flagged to be dropped, though you cannot reverse setting the column to UNUSED.

It is possible to set a number of columns UNUSED in a table before actually dropping them. The overhead of setting columns UNUSED is fairly minimal and allows you to continue to operate normally, except that any actions on the UNUSED columns will result in an error. The next step, when you have configured all the columns you want to get rid of as UNUSED, is to actually physically reorganize the table so that the data for the UNUSED columns is no longer on disk and the columns are really gone. This is done by issuing the command ALTER TABLE & DROP COLUMN.

Physically dropping a column in an Oracle table is a process that will prevent anyone from accessing the table while the removal of the column(s) is processed. The commands that will affect an actual removal of a column are:

ALTER TABLE <tablename> DROP COLUMN <columnname>
ALTER TABLE <tablename> DROP UNUSED COLUMNS

The commands will always do the same thing. This means that if you mark two or three columns as UNUSED in a table, if you decide you want to drop one of them using the ALTER TABLE & DROP COLUMN command, you will drop ALL columns marked as UNUSED whether you want to or not. The ALTER TABLE & DROP COLUMN can also be used when a column has not previously been marked as UNUSED but you simply want to drop it right away, but you will also drop any UNUSED columns because that's the way it works.

If constraints depend on the column being dropped, you can use the CASCADE CONSTRAINTS option to deal with them; if you also want to explicitly mark views, triggers, stored procedures or other stored program units referencing the parent table and force them to be recompiled the next time they are used, you can also specify the INVALIDATE option.

A problem could arise if you issue the DROP COLUMN command and the instance crashes during the rebuild of the table. In this case, the table will be marked as INVALID and will not be available to anyone. Oracle forces you to complete the DROP COLUMN operation before the table can be used again. To get out of this situation, issue the command ALTER TABLE & DROP COLUMNS CONTINUE. This will complete the process and mark the table as VALID upon completion.

To Be Continued...
These are just a couple of the useful features that Oracle 8i provided us and are hidden in Oracle's documentation and courses. Use them they're really great. I'll tell you about some others in future columns.