Three strokes make Oracle table column management simpler ____oracle

Oracle database is one of the most complex databases so far. It is this complexity that allows Oracle databases to face a wide variety of business needs. But most of the time, database administrators have some skills to make database maintenance work easier. In this paper, the author takes the management of table columns in Oracle database as an example to discuss this problem.

　　first, the column is set to unused state, in lieu of deletion.

When the database is deployed, the database administrator can still maintain the tables of the database, such as deleting columns, even during the database use process. Deleting a column deletes the value of the corresponding column for each record in the table, while releasing the storage space it occupies. So if you want to delete a column in a large table, the deletion of the column will take a long time because it must be processed appropriately for each record. If a database administrator in the design of a personnel management system, the first is to put the person's address and personnel information table placed in the same table. But it turned out that the design was wrong. On the one hand because the field of the person's address is relatively long, and some because the address is difficult to determine, this field is still empty. On the other hand, a person may have changed his or her address several times, and for the sake of management, it is necessary to preserve the previous address information. This last person may also correspond to multiple address information. So then the database administrator adjusts the design, creates a table, and then associates them with the person number. Since there are already more records in the table, if you want to delete the column, it may take a long time to execute the deletion job.

To avoid consuming too much system resources (and long time) because of the operation of deleting columns during peak database usage, it is recommended that you do not immediately delete columns by using the DROP keyword. You can set a column to be inactive by using the unused keyword first. If you can use the command alter TABLE adress set unused, set a column to inactive. After this setting, from the user's point of view, there is no difference between the columns that are set to the unused state and those that are directly deleted. Users cannot see these columns through the query or in the data dictionary. And even in a table, you can insert columns of the same column name. Simply put, for the user, the column that is set to unused seems to be deleted. But from a database standpoint, it's not the same. This column is just not set to the Ununsed column, but it still exists in the database. That is, the storage space occupied by this column has not been freed. For this reason, even when the database is running at peak times, adding this tag to the column will not take up much of the time and system resources.

For this reason, the author's opinion is that when the database is busy and the records in the database table are more, you can not physically delete the column, and first of all, the column marked as unused state. This can reduce the negative impact on the normal use of the database as a result of deleting columns. Although this will sacrifice a little hard disk space, but now the hard drive is cheap. For the performance of the database, sacrificing this hard disk space is worthwhile. Wait until the database is empty, and then delete the columns. This will not be affected by any of the users. It's just a matter of technical processing.

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 the columns. By querying this view, the database administrator can understand which tables in the database have unused columns. The database administrator can then clear it for one months or a year, depending on the actual situation. This not only improves the efficiency of the database, but also does not affect the normal use of the user because of the operation of deleting columns.

　　Add related comments to the list.

As the saying goes, a good memory is worse than a bad pen. When designing a database, adding some necessary annotations to a table or column can improve its readability and facilitate subsequent maintenance and upgrades. Like the author of such a professional database design and development staff, often a person needs to be responsible for many projects, that is, simultaneously responsible for multiple enterprise database software. When you have a large number of projects, one year later you may not know what a table in a database is intended for. Even if the use of a more appropriate table name encoding or column name coding rules, but with these simple code, still can not intuitively reflect the meaning of the code. To do this, when necessary, we often need to add relevant comments to the table or column to facilitate the subsequent maintenance and upgrade.

To achieve this, we can use the comment keyword. such as comment on table name is ' annotation content '. When using this command, it is important to note that the contents of the annotation must be enclosed in single quotes instead of using double quotes. Because if the annotations contain special characters, such as single quotes, wildcard characters, and so on, it is best to replace them with other characters. If you want to use these characters, then you need to use the escape character, so that the database system that this is a normal character. In general, the content of the annotation can be as large as 4,000 bytes, enough for the database administrator to describe the purpose of the table. In addition, you can include punctuation marks, empty characters, special characters (not recommended), and so on in the contents of the annotation. It can also span multiple physical rows to improve readability, and so on. However, although it provides a 4,000-byte description, the database administrator should be able to say short words when writing comments. Otherwise, it's not a good job to spend a lot of time reading the notes later. I do not think that in writing this form of comments, it is better to be able to point to, rather than to write the project specification as written in all directions.

In addition to adding comments to a table, you can add comments to a column in an Oracle database. This added method is similar to the above, simply change the keyword on table to on COLUMN. When you have more than one person working together to develop a database, it is a good idea to add the necessary annotations to the table or column. Even if you do not add a description here, you will need to make a detailed explanation in your stand-alone design documentation. Under normal circumstances, the author thinks that need to explain the following content. One is the creator (who created it or who has it updated), the second is the time of creation (when it is created or when it is updated), the third is to indicate the purpose of creating or updating, and four is to indicate some of the constraints of this column. Also note that if someone later updates the table or column, such as adjusting the size of a field, it is best not to overwrite the original comment. Instead, add a new annotation directly after the original annotation. These measures are conducive to subsequent maintenance and database upgrades.

　Iii. re-organizing the table.

Sometimes the table of the Oralce database needs to be organized in order to improve the performance of the database tables. In an Oracle database, a frequent DML operation on a table causes the table to produce more space debris and row migrations, which negatively impacts the performance of the database. This situation is more common in some transaction-type databases. At this point, you need to rearrange the tables, such as moving one location to reduce fragmentation and improve database performance.

In most cases, the author uses the ALTER TABLE move statement to rearrange the table. If you can use this statement, move a table to a new segment of data in the same table space. This has no effect on the user after moving. But you can rebuild 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 table space. However, when using this statement, the database administrator still needs to pay attention to a few limitations.

One is to note that there is enough free space in the use of this statement, and if you move in the same table space, you need the primary tablespace to hold the data. This is due to the fact that this operation is an operation that is done in conjunction with two operations. It first copies the table to the specified location, and then deletes the original table. That is, the database will not delete the original data segment until the table is completely moved to the new data segment. So this free space must be guaranteed, otherwise, it may lead to the operation can not be successfully completed.

Second, in the process of using this statement to rearrange the table, the rowid of the original table will change. The maximum effect of this value change will invalidate the index in the table. To do this, after you use this statement to rearrange the table, you need to index the table again. This means that in a short period of time users will be affected by the use of this table. To do this, it is a good idea to choose a database that is more idle. If you can temporarily interrupt a user's connection while executing this statement, then the best.

The database administrator can query the fragmentation status of the storage space through the data dictionary. If the fragment is indeed more, the author recommends using this statement to rearrange the table to reduce fragmentation space. If there is a large amount of fragmentation in the entire database storage space, rather than an individual table problem, then the data pump tool needs to be used to rearrange all the tables to minimize fragmentation.