Three methods to simplify the management of Oracle table columns

Oracle Database is one of the most complex databases so far. This complexity allows Oracle databases to meet various requirements of enterprises. However, most of the time, the database administrator has some skills to simplify database maintenance. Here I will take the management of table columns in the Oracle database as an example to talk about this issue.

1. Set the column to the UNUSED State instead of deleting it.

After the database is deployed, the database administrator can maintain the tables of the database, such as deleting columns, even during database usage. Deleting a column will delete the values of the corresponding columns of each record in the table and release the storage space it occupies. Therefore, if you want to delete a column in a large table, it takes a long time to delete the column because each record must be processed accordingly. If a database administrator designs a person-to-person management system, the database administrator first places the person's address and personnel information table in the same table. However, it was proved wrong later. On the one hand, because the personnel address field is long, and some are difficult to determine due to the address, this field is still blank. On the other hand, a person may have changed his or her address several times, and the previous address information must be kept for management purposes. For this reason, the last person may correspond to multiple addresses at the same time. Later, the database administrator adjusted the design, created a table, and associated them with the personnel number. Since there are already many records in the table at that time, if you want to delete a column, it may take a long time to delete the job.

To avoid occupying too much system resources (and taking a long time) due to the column deletion operation during database usage peaks, we recommend that you do not immediately use the DROP keyword to delete columns. You can use the UNUSED keyword to set a column to inactive. For example, you can use the alter table adress set unused command to SET a column to inactive. After this setting, from the user's perspective, there is no difference between the columns that are set to the UNUSED status and the columns that are directly deleted. You cannot query or view these columns in the data dictionary. You can insert columns with the same column name even in the table. To put it simply, the columns set to UNUSED are like deleted. But from the database perspective, it is different. This column is not set as the UNUNSED column, but it still exists in the database. That is to say, the storage space occupied by this column is not released. Therefore, adding this label to the column does not occupy much time and system resources even during the peak hours of database operation.

Therefore, the author's opinion is that when the database is busy and there are many records in the database table, you can not delete this column physically, first, mark this column as UNUSED. This reduces the negative impact of column deletion on normal database use. Although it sacrifices a little hard disk space, hard disks are cheap now. It is worthwhile to sacrifice this hard disk space for database performance. When the database is empty, delete these columns. This will not affect the user. This is only a technical issue.

To facilitate the management of these UNUSED columns, A View (named USER_UNUSED_COL_TABS) is provided in the Oracle database to help the database administrator manage these columns. By querying this view, the database administrator can know which tables in the database have the UNUSED column. Then, the database administrator can clear the data for one month or one year based on the actual situation. This not only improves the database operation efficiency, but also does not affect the normal use of users because of the column deletion operation.

2. Add comments to the list.

As the saying goes, good memory is worse than bad writing. When designing a database, you can add necessary comments to tables or columns to improve readability and facilitate subsequent maintenance and upgrade. Professional database design and development personnel such as the author often have to take charge of many projects by one person, that is, the database software of multiple enterprises at the same time. When the number of projects is large, you may not know the purpose of a table in a database after one year. Even if appropriate table name encoding or column name encoding rules are adopted, these simple codes cannot intuitively reflect the meaning of these codes. Therefore, when necessary, we often need to add comments to tables or columns to facilitate subsequent maintenance and upgrade.

To achieve this goal, we can use the COMMENT keyword. For example, the comment on table name IS 'comment content. When using this command, note that the content in the comment must be enclosed by single quotation marks rather than double quotation marks. If a comment contains special characters, such as single quotes and wildcards, it is best to use other characters instead. If you need to use these characters, you need to use escape characters to make the database system think this is a common character. Generally, the Comment content can contain 4000 bytes, which is sufficient for the database administrator to express the purpose of the table. In addition, the comments can contain punctuation marks, space characters, and special characters (not recommended. It can also span multiple physical rows to improve readability and so on. However, although it provides a description of up to 4000 bytes, it is best for the database administrator to make short remarks when writing comments. Otherwise, it will take a lot of time to read the remarks in the future. The author may not think that it is best to write a comment on this table until it is clicked, rather than writing a project description.

In addition to adding annotations to tables, you can also add annotations to columns in Oracle databases. This method is similar to the preceding method. You only need to change the keyword on table to on column. When multiple people develop a database together, it is best to add necessary comments to tables or columns. Even if you do not add a description here, you need to describe it in detail in an independent design document. In general, I think it is necessary to describe the following content. The first is the creator (who created or who updated); the second is the creation time (when or when to update); and the third is the purpose of creation or update; 4. Note the constraints of this column. In addition, it should be noted that if someone updates the table or column later, such as adjusting the size of a field, it is best not to overwrite the original comment. Instead, add a new comment directly after the original comment. These measures are conducive to subsequent maintenance and database upgrades.

3. reorganize the table.

Sometimes, to improve the performance of database tables, you need to reorganize the tables in the Oralce database. For example, frequent DML operations on a table in an Oracle database may lead to a large amount of space fragments and row migration, negatively affecting the database performance. This situation is common in some transactional databases. In this case, you need to re-organize the table. For example, you can move a location to reduce fragments and improve database performance.

In most cases, I use the ALTER TABLE MOVE statement to reorganize the TABLE. You can use this statement to move a table to a new data segment of the same tablespace. This will not have any impact on users after the move. However, you can recreate the table's storage structure to reduce fragmentation and optimize performance. Of course, you can also use this statement to move an existing table to another tablespace. However, when using this statement, the database administrator still needs to pay attention to several restrictions.

First, you must note that there is sufficient free space when using this statement. If you move in the same tablespace, the primary tablespace must be able to accommodate this data. Note that this operation is like an operation completed by combining two operations. It first copies the table to the specified position and then deletes the original table. That is to say, the database will not delete the original data segment until the table is completely moved to the new data segment. Therefore, the free space must be guaranteed. Otherwise, the job may fail to be completed smoothly.

Second, the ROWID of the original table will change when the table is reorganized using this statement. The biggest impact of this value change will invalidate the index in the table. Therefore, after using this statement to re-organize the table, you need to re-create an index for the table. This means that using this table in a short period of time will be affected. Therefore, when performing this operation, it is best to select when the database is relatively idle. If the user's connection can be temporarily interrupted during execution of this statement, it would be best at that time.

The database administrator can use the data dictionary to query the shard status of a bucket. If there are indeed many fragments, I recommend using this statement to reorganize the table to reduce the shard space. If a large number of fragments are distributed throughout the database storage space, rather than individual tables, you need to use a Data Pump tool to re-organize all the tables to minimize fragmentation.

1. Summary of Oracle data block damage recovery
2. Character Processing skills in Oracle databases
3. What Oracle DBA must understand in the new environment