Ten Tips for Oracle Query

Oracle databases query ten tips data queries, which are one of the most important functions in database operations. Sometimes the performance of database queries is directly related to the efficiency of database operations and database selection. I will not talk about the principle below, but I will summarize some of the tips that are easy to ignore. Maybe everyone is worried about it?

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: Use the where statement frequently.
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: Use the count function flexibly.
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 the fields required 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.
Tip 6: properly process the null field.
The null field is a special field in the database. Null indicates an unknown value or missing data. Note that if the value of a field is null, this field is neither a space nor 0. When a record is inserted, if this field is not assigned a value and there is no default value, the default value of this field is "null ".
Because this value is special, experienced database administrators can perform queries in a timely manner. Sometimes, errors may occur during processing. For this reason, I would like to summarize some notes for querying this empty field during database queries.
First, pay attention to the numeric operation of the null field.
For example, in a salary management system, there is a salary table with two fields: basic salary and overtime salary. If the basic salary of a user is 2000, The Overtime salary does not exist. When you enter this record, no data is input in the overtime payment field, and no default value of 0 is set for this field during database design. Therefore, when this record is saved, the database system automatically assigns a value to this field, and the value of this field is null.
If we use the SELECT statement to query this record, the data displayed in the "overtime pay" field is empty. It looks like a space, but in fact it is not stored as a space. At this point, if we use the query statement to know the total salary of this employee (that is, the Overtime salary plus the basic salary), what will happen?
We can use the select employee name, basic salary, Overtime salary, basic salary + Overtime salary as total salary from the employee salary table. We can use this statement to query the total salary of this employee. However, will this statement query the expected results? When we execute this statement, we will find that the result is quite different from what we think. The total salary column is displayed as a space.
Originally, in Oracle database design, if a null field performs four operations with other fields, the displayed results are empty. Therefore, if a field is Nuu, a null value is returned regardless of addition, subtraction, multiplication, division, and final result. This is obviously different from what we think.
In this case, what should we do? In the database design process, there are two main processing methods.
First, when designing a table, set the default values for these fields that need to be involved in calculation. For example, you can set the default value of this field to 0. When this record is added, even if the foreground user does not set a value for it. When saving data, the system also gives it the default value 0. In this case, we can obtain the expected value only when performing the four arithmetic operations.
The second is to consider the impact of this null value during the query. Sometimes, if there are records in the database, the default value of the database field cannot be changed. In this case, what should we do if we need to perform four arithmetic operations on the null field and the numeric field? In this case, we need to assign a value of 0 to the null field during the query. We can define this in the query statement. Select employee name, basic salary, Overtime salary, basic salary + nvl (Overtime salary, 0) as total salary from employee salary table; in this case, when the value of overtime pay is null, the system treats it as 0 during calculation. In this way, we can get the desired results. However, in general, this method is a last resort. It is best to set the default value of 0 for related fields during database table design.
In addition, the nvl2 function is similar to the nvl function, except that the nvl2 function has an additional parameter, and its expression is nvl2 (parameter 1, parameter 2, parameter 3 ). It means that when parameter 1 is not null, the returned value is parameter 2; When parameter 1 is null, the range is parameter 3. If you use this function to implement the nvl function, you need to rewrite the function parameter in the above case: nav2 (overtime pay, basic salary + overtime pay, basic salary ). It can be seen that the two functions are similar. The specific function is better, you need to choose according to the hobby of the database administrator.
The second is how to query null fields.
If there is an employee basic information table, there is an ID card number field. What Should users do if they want to know which employees have not recorded the ID card number information? The null field is not a space or 0. If we use '0' or '(Space) as the query condition in the query Condition Statement, we cannot find the expected result. In this case, a function is provided in the database for users to query null field records. If you want to know which employees do not indicate the ID card information, you can use the following statement.
Select employee name, ID number from employee basic info table where ID number is not null;
The preceding statement can be used to search for employee information with an empty ID card.
7 tips: Use Fuzzy search more.
When designing an application system, if users are allowed to enter all the query conditions completely, this requirement is too harsh for common users. When training or implementing software projects, for various reasons, users often only enter some input conditions during queries. In this case, you need to implement fuzzy query during the query design. In this case, you can query related content even if the query conditions entered by the user are incomplete.
For example, when a user queries a product information, he or she may not remember the specific name of a product, but only knows that it is called a terminal. Other specific information is unclear. At this point, you only need to enter the terminal in the Name field or specification field, you can query from the system that these two fields contain "terminal" records. In this case, you only need to continue searching in the query results.
This instructs our database management personnel to use the fuzzy query function when designing the database system.
Pay attention to the following aspects.
First, do not distinguish between uppercase and lowercase. By default, the Case sensitivity is used in database queries. That is to say, if there is a field whose content is ABC, this record cannot be found when we query ABC. It is case-insensitive. As a user of the previous application, when entering the query conditions, the query conditions are not case sensitive. Although it can be implemented in front-end applications without case sensitivity, it is much easier to implement in databases than in front-end systems. Therefore, we recommend that you enable automatic case conversion in the background database. Do not make the query case insensitive. This may be a special national condition in China.
The second is to implement prefix/suffix-free fuzzy search. For example, if the content of a field is "good learning", if I enter the query condition as "Learning", "good learning", and "good learning", I can check the condition record. How can we design the query condition statements? This is the concept of prefix/suffix-free fuzzy search. In fact, it is easy to implement this function. When designing a query statement at the front-end, when the query conditions are passed to the back-end database system, add the fuzzy query parameter % before and after the query parameters. That is, if the input query Condition Statement is "studious", the parameter passed to the database is "% studious % ". In this way, we can implement the fuzzy query we need. Generally, fuzzy queries must be implemented between the foreground application and the background database. In this way, the workload will be much less, and the database operation efficiency will be improved.
The third is to use the beteen function to implement fuzzy search. For example, if an employee wants to know his/her overtime work in February September, he/she only needs to enter the time range from January 1, September 1-9 to 30 in the query condition, you can query the information you need. This is achieved through the beteen function. I have met a platform-type ERP system before, and he has done well in this regard. This product can design related reports by itself. In report design, a date-based Query condition allows you to query a single date and records in a certain range. This makes use of this function. In addition, there are also in functions that can implement fuzzy queries. As a database administrator, you need to choose these fuzzy query functions from the perspective of improving the database query performance. In addition, you also need to work with the front-end application design to process the fuzzy query function.
Tip 8: use like and other wildcards with caution.
The like keyword is technically a friendly wildcard. With this wildcard, we can implement many fuzzy queries. For example, if you want to know the personnel information starting with "339005" for your ID card number in the personal archive system, you can use the like statement. We can use the following condition statement to meet our needs: "Where ID card number like '000000 '". With this condition, you can find all the numbers starting with 339005 for your ID card.
However, if you use the like statement in a large table, you will find that the query statement runs very slowly. What is the cause? In fact, no matter the like keyword, if the matches keyword is used, if the matching record is found in a large amount of data, the running efficiency is relatively low. This is mainly caused by its technical characteristics.
The like and matches keywords support wildcard matching. In some professional books, this is called a regular expression ". However, when using these keywords for query, the database system does not query through indexes, but uses sequential scanning. Obviously, this technical feature leads to a low query efficiency for the like and mateches keywords. Especially in complex queries or large table queries, You can obviously feel that the speed is slow.
Index is an important data structure in the database. If the index is used properly, the query performance of the database can be greatly improved. In general, we should make full use of indexes during database design to improve database operation efficiency. For some frequently-used query functions, we need to create an index for columns without a specified foreign key. If you have large table data queries, in addition, you need to sort the values of several fields, and you also need to create a composite index on these columns. Especially in some application systems, this field can be sorted by the names of the following fields. In this case, you need to create indexes on these columns that are frequently sorted to improve the efficiency of re-sorting. It can be seen that if the index cannot be used to improve the query efficiency during query, it is as if the sports car loses four-wheel drive, and the speed will be greatly affected.
Therefore, we should avoid using the like or matche keyword in the database system design. Sometimes, we can use other operators instead. For example, we can use the> (greater than) or <(less than) symbol to achieve similar requirements. To use these two keywords, You need to optimize the query. For example, you should not directly use the two keywords in the basic table, but query them through the report view or temporary table to reduce their adverse effects.
Tips 9: Use annotations to improve the readability of query statements.
There is a strange phenomenon in database design. Some expert database designers do not describe the statements in detail when writing query statements. Sometimes, the Comment content exceeds the length of the query code. However, some database designers who have been getting started do not like to write comment statements. This is an abnormal phenomenon.
When I first started to access the database, I did not like to write comment statements. I think writing a comment statement is too time-consuming. However, an accidental event gave me a break from this bad habit. At that time, when I was watching an expert design database, I was stunned by its dense comments. After reading the code, I can easily read all the code. It is worthy of being an expert. After reading his comments, I watched the code I wrote. That's really a little cool. Since then, I have been gradually developing my own coding habits. At the time of database design, I have already written a detailed comment. At least my colleagues will know what I want to achieve without looking at the source code after seeing my comments.
When writing comments, pay attention to several issues.
First, the more detailed the comment, the better. In fact, annotations can sacrifice at most a bit of disk space without any adverse impact on database performance. On the contrary, detailed comments will be of great help for subsequent database maintenance and management, and secondary system development.
Second, it is best to use English comments. If you use Chinese annotations, sometimes the database language is improperly set. When the database instance is installed, the Chinese annotations are not carried. Therefore, as a database administrator, you must have a basic understanding of English and learn to write comments in English. In fact, this is not very difficult. Just look at other people's database comments and copy the comments they commonly use. You can complete the task by selecting the appropriate copy and paste options.
Third, not only do you need to annotate the code when writing a query statement. When writing other code, such as procedures and functions, you must also add detailed code. To enhance the readability of these complex functions.
4. The content of the comment. The general comment should include the following content. The first is the functions to be implemented in this Code, the second is the parameters that need to be called in this Code, and the third is the output results of this Code. For multi-table join queries, it is best to describe the correspondence between tables. If a function is called directly in a query statement, you 'd better specify the function of the function. The principle of terminating this code is to let others see this comment and understand the meaning of this code without querying other information.
The detailed comment content not only does not reduce the running performance of the database, but also improves the efficiency of database management and maintenance. It also accelerates the development and design of foreground applications. Query statements are the most commonly used statements in the system and the most frequently referenced statements. Therefore, you need to make comments in the query statement.
Tip 10: When necessary, restrict the rows used by the user.
When using some large management systems, such as ERP systems. If we query product information, all records will not be queried by default when there are many records. For example, the database of the ERP system contains 20000 product information. Terminal products occupy 1000 pieces of information. In the query condition, if the product category is limited to "terminal", the query results may not be all terminals. Only the first 100 terminal products are displayed by default. To view all product information, you must click "show all" to display all information.
In fact, no matter how many applications are designed, there are also restrictions in Oracle databases. For example, if you query data directly in the PL/SQL client, the displayed records are restricted by default, instead of all statements that meet the conditions. To query all qualified records, click "continue" to display all records in the database.
Why do we have to impose similar restrictions? This is mainly to improve the performance of database queries. We directly query millions of records on the database server, and it shows that the time spent on hundreds of records is significantly different from the time spent on thousands of records. The former may only take 3 seconds. The latter may take 1 minute. Therefore, to reduce the waiting time, we often need to limit the default number of records displayed for the first query.
For example, we often use top 100 in the query statement to make the database display only the first 100 records. In this case, the user's waiting time can be significantly shortened. By default, records are displayed based on the time sequence of record creation. The records created at the latest are displayed at the beginning. And so on.
When the user needs less than the first 100 data records, you do not need to view other records. If not, you need to query all the records.