Experience and skills in Oracle Database View Management

View is one of the most basic and important functions for databases. The design of the database view is directly related to the database performance. In addition, in the design of large databases, the division of labor and cooperation, the design of basic tables and the design of report views are often the responsibility of different people. Therefore, the design management of views is similar to the design management of basic tables. In this article, I will talk about some little-known skills in View Design Management Based on my work experience.

Tip 1: remove the basic table from the view.

Generally, views are created on the base table. That is to say, you must first have a base table and then a view. However, during the design of large databases, the design of basic tables and views is usually carried out at the same time due to project time considerations. For example, some people are responsible for the establishment of basic tables, while others are responsible for the design and establishment of views. In this process, when the basic table does not exist, you need to create some views to speed up the project progress.

In order to make the creation and modification of Basic Tables not necessarily related to the creation and modification of views, so as to facilitate the synchronization of work between employees and improve work efficiency, in the Oracle database, the concept of "Force create View" is proposed. That is to say, under normal circumstances, if the basic table does not exist, the creation of the view will fail. However, we can add a parameter during the process of creating a view. If the syntax for creating a view is correct, the table can still be created even if the basic table does not exist. This useful parameter is the force option. For example, when creating a VIEW, create force view text only needs to add the FORCE parameter before the keyword VIEW. In this case, the system does not consider whether the basic table exists when compiling the view.

However, note that if the base table does not exist, the status of the view after compilation is "invalid" and some operations such as query operations cannot be performed on the basis of the view. When you access this view next time, the database recompiles the view. If the base table exists, the base table becomes valid. If the base table does not exist, the view will become invalid.

The reason why Oracle databases are so set is mainly due to the need to work collaboratively in the database design process. With this function, you can create related objects in the database by designing the basic database table and view during database creation. Otherwise, you need to create a view after the basic table is created, which will significantly affect the database creation progress. Therefore, in the process of database establishment, especially for medium and large database systems, this is a very practical function.

Tip 2: the ideal step for creating a view.

Whether it is a simple view or a complex view, I think it is best for the database administrator to follow certain steps when creating the view. This is because changes to the view are relatively troublesome. Therefore, when creating a view, we must ensure the view accuracy. On the other hand, view is a manifestation of the basic table. If we do not follow the steps, we may not be able to meet our expected needs.

Of course, this step does not have an official version. It is simply summarized by the database administrator based on actual experience. This step is not only effective for Oracle databases, but also for other databases.

Generally, you can create a view in five steps,

Step 1: Write the Select statement first. We know that a view is actually a set of Select statements. Therefore, the first step for creating a view is to consider how to write the Select statement. Whether the Select statement is compiled properly and the execution efficiency directly affects the performance of this view. In addition, the Select statement may also contain format control, content orchestration, and so on. For example, in a Select statement, some fields can be combined into one field, or the relevant content can be put upside down. These functions are completed by the Select statement. Therefore, writing a Select statement is the basis for creating a view.

Step 2: Test the Select statement. After we have compiled the Select statement, we need to execute this statement in the database to check whether it can query the value we want. When testing a Select statement, you need to pay attention to one problem. Sometimes the Select query statement can find accurate data, but it may fail when creating a view using this statement. For example, if two tables have the same field name during connection query between tables. In addition to field names, they also have table names to define this field. For example, A. name and B. name. This is not a duplicate name. However, when creating a view, this will be considered as a duplicate listing, and a column name needs to be redefined. Note This when creating a database view.

Step 3: Consider the accuracy of the query results. After the query statement is used to query the desired result, we need to check whether the result meets our needs. In this process, we mainly pay attention to two points. The first is whether the formal fields are complete. In some application systems, if the database view is to be called by the foreground application, it must contain some form fields. For example, when I used to design an ERP system, if the front-end system needs to call the view in the database, it must contain information such as the Record Update Time, Updater, record creation time, creator, and so on. If this information is not provided, an error occurs when the foreground calls this view. Therefore, when considering the accuracy of the query results, we should consider the needs of the front-end application to see whether these form fields are complete. Second, the integrity of the object content. What fields do we need to display in the table? We need to confirm here. If too much content is displayed, the execution efficiency of the view is affected, and the security of the view is also reduced. However, if the field content is insufficient, you need to add a field later, it will be troublesome and have a certain amount of work. Therefore, you need to determine the content to be displayed based on the actual function of the view.

Step 4: Modify the view. Sometimes, to facilitate reading, we need to modify the query results. For example, there are two tables, one is the basic information table of employees, which includes the employee name and employee position number, and the other is the basic information table of positions, the table contains the job ID and job name. We want to display the following information in the View: "position: employee name", for example, Database Engineer: Victor. That is to say, combine the two fields and add a colon in the middle. These formatted content is implemented during query. Therefore, after we confirm that the query results are correct, we need to confirm the format problem. If you can standardize these formats in the view, the foreground program design will be relatively simple.

Step 5: Create a view. After the preceding four steps are confirmed to be correct, we need to create a view based on the preceding query statement. However, you need to pay attention to some problems during this step. First, the name specification of the view name. In addition to the mandatory naming format of databases, for example, the name cannot start with a number, and so on, we also need to follow some soft rules. For example, it is better that a view can start with V and be separated from the basic table. In view naming, the view can be classified based on the application module and reflected in the view name. This is of great practical significance for subsequent view searches. Second, although the basic table can be directly updated in the view, we generally do not recommend that you directly update the data in the basic table through the view for the sake of security and data unification. Although the database provides similar functions. If you want to change the relevant data, it is better to directly change the content of the basic table. When creating a view, the basic table cannot be directly updated through the view by default.

1. Five tips for Oracle Database Query
2. Ten skills for connecting JDBC to Oracle databases
3. Oracle Data Segment compression technology