Three moves make Oracle table column management easier

Sometimes, to improve the performance of database tables, you need to reorganize the tables in the Oralce database. For example, in an Oracle database

Sometimes, to improve the performance of database tables, you need to reorganize the tables in the Oralce database. For example, in an Oracle database

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.