Five tips for Oracle Database Query

Preface:Data Query, yesOracle DatabaseOne of the most important functions in operations; sometimes the query performance of Oracle databases is directly related to the operating efficiency of Oracle databases and the selection of Oracle databases. I would like to summarize some of the query tips that are easy to ignore here for your convenience in the future. Maybe you may be worried about this?

First Tip: Use a connector to connect multiple fields

For example, in the employee basic information table, there are employee names, employee positions, birth dates, and so on. If the three fields in the current view are displayed in the same field, there is a delimiter in the middle. For example, I want to display the result as "Manager Victor was born in May 3, 1976 ". What should we do? In fact, this is relatively simple. We can use the connector to connect these fields in the Select query statement.

You can write a query statement as follows:

SELECT employee position | ''| employee name | 'derived FROM '| date of birth as employee information FROM employee basic information table;

You can use this statement to meet the preceding requirements. That is to say, in normal queries, we can use the | connector to connect some related fields. This is useful in the report view. For example, when I used to design a library management system, there were publishing houses and serial numbers in the basic information of books. However, when printing a report, you need to combine these fields into one field for printing. Therefore, you need to use this connector to connect these fields. Additionally, you can add descriptive text in the middle of the field to facilitate reading. For example, I added a space between the employee's position and employee's name, and added several annotated texts between the employee's name and date of birth. These functions seem relatively small, but can greatly improve the readability of the content. This is what we need to pay attention to in the database design process.

In short, the later connector can improve the readability and flexibility of our reports.

Tip 2: Cancel duplicate rows

For example, there is a basic employee information table in the personnel management system. This table may contain fields such as department, position, employee name, and ID card number. If the content is queried, duplicate rows may not exist. However, if I want to know which departments and positions have been set up in the company, and relevant personnel have been configured for these departments and positions. In this case, how can I query it?

If I directly query the Department table, I can know which departments and positions are set in the system. However, it is very likely that these departments or positions have no one due to personnel changes. Therefore, the information of all departments and positions found here cannot be ensured that there must be employees in this department or position. That is to say, this does not meet our above requirements.

If I want to query employee information directly from the employee information table, although the Department and position information can be queried, there must be employee information. However, at this time, the department and position information are displayed in duplicate rows. If the procurement department has a division of labor and cooperation, there may be a procurement team lead. At this time, there are three duplicate records in the queried department and position information.

Therefore, neither of the above two processing methods can fully meet the needs of enterprise users. In this case, we can actually use a DISTINCT function to eliminate the repeated rows queried.

For example, we can use select distinct department information, job information FROM employee basic information table. This query statement with the DISTINCT constraint can not only query information about all positions and departments with employees, but also filter out duplicate records to improve readability.

Therefore, this function is particularly useful in the database design process, especially in the use of query statements.

Third tip: frequently use WHERE statements

We all know that high database query efficiency is an important criterion for us to evaluate the database design. Undoubtedly, using the Where clause in database queries is one of the most important ways to improve the performance of database queries. Especially when designing a large table to query records that meet the conditions, the WHERE condition statement can be used to limit the query response speed.

For example, in the library management system, when someone wants to query the "CPA" counseling book, although the "CPA" is not in the category or name of the book ", it is also feasible to query all records first, and then check whether there is any relevant book information one by one. However, in this case, the system response speed will be very slow, because there are many records in it. On the other hand, the query results also seem to be a headache.

In fact, we only need to add some query parameters to the query and use the Where Condition Statement to limit them, which can speed up the database response and find the data that best meets user requirements.

In addition, I have also been familiar with some platform-based management software designed on Oracle databases. They can customize related reports. In report design, if you select "Big Table query" on the front-end design platform, the platform automatically applies the Where condition statement when generating the report, to improve the efficiency of the front-end system to query data from the database.

Therefore, I believe that in the design of the Oracle database system, the Where statement should be used frequently. Use Where statements to improve database query efficiency.

Fourth tip: flexible use of the COUNT function

During query processing, the COUNT function is one of the many functions that we have applied. For example, we sometimes need to use this function to count the number of employees and the number of types of books. However, this function may be used by many people, but there is still a gap between it and its flexible application.

Next I will talk about some of my experiences on the application skills of the COUNT function.

First, you must flexibly place the position of the COUNT function, because when using the COUNT function to COUNT the number of records, empty rows of records will be considered. For example, in a data table, there are generally sequence fields and other meaningful fields. Sometimes there may be content in the sequence field and no content in other fields, this empty record will be taken into account when COUNT function is used to COUNT the number of records. Obviously, a statistical error occurs. Therefore, where should the COUNT function be placed. In general, I suggest not placing it on the serial number field, but on some key object fields. For example, when counting the number of employees, you can put them on the employee's name or number.

Second, flexible use with other functions. For example, in the above example, I mentioned that sometimes users need to know which departments and positions have their employees. We can use the DISTINCT function to find specific departments. However, I only want to know the specific number of departments and positions. At this time, we can also use the COUNT and DISTINCT functions to work out the data we need. In the COUNT function, you can specify the ALL and DISTINCT options. By default, the ALL option is used to count ALL rows, including duplicate rows. While DISTINCT indicates only counting non-repeated rows. It can be seen that using the COUNT function together with other functions can simplify our query statements and improve query efficiency.

Fifth tip: only required fields for query

Sometimes, you need to use the same table for different query requirements. For example, the employee information table contains a lot of content. Sometimes users want to know the number of formal employees, the number of management employees, the number of production line employees, or the number of employees whose contract is about to expire. To this end, I encountered a problem, because these contents are basically in the same table, which is implemented in the same view, but different views are designed according to different requirements?

Technically, both can be implemented and won't be difficult. However, considering the database performance, it is better to use different views to achieve different requirements.

On the one hand, in terms of security, access permissions can be controlled based on different views. It can be seen that the view is refined, and the permission control is more flexible.

On the other hand, data query efficiency is closely related to the amount of data content. For example, when querying the employee contract expiration information, the employee's address information is generally not required. If you query this information, it will take a long time because this field is relatively long. Therefore, in database design, we must learn to design different views based on different user needs. Although it may take some time to design the database, it can indeed improve the performance and security of the database. This business is still worthwhile.

You should pay attention to these tips when querying Oracle databases in the future. Using these five skills in your database query will greatly simplify your operations. Why not?