10 ways to optimize Oracle database queries

However, when the user uses the like statement in a large table, it is found that the query statement is running very slowly. What is the cause of this? In fact, whether it is like keyword, if the use of matches keyword, if in a large number of data to find a record of the conditions, then its operating efficiency is also relatively low. This is mainly due to its technical characteristics.
Like and matches two keywords that support wildcard matching. This is called "formal expression" in some professional books. However, because of the use of these keyword queries, the database system is not indexed to query, but the use of sequential scanning methods to query. Obviously, this technical characteristic, has caused the like and mateches two keyword query efficiency low. Especially in the complex query or large table query, users can obviously feel the speed is relatively slow.
An index is an important data structure in a database. If the index is used reasonably, it can greatly improve the query performance of the database. In general, when we design the database, we should make full use of the index to improve the efficiency of database operation. For some of the query functions that are often needed, we need to index columns that do not have a foreign key specified, and you need to set up a composite index on those columns if you are querying large table data, and you want to sort it by the values of several fields. Especially on some application systems, this field can often be sorted by the name of the following field. In this case, it is more necessary to establish indexes on these frequently sorted columns to increase the efficiency of reordering. Visible, if the query, if you can not use the index to improve query efficiency, it is like a sports car loses four-wheel drive, the speed will be greatly affected.
Therefore, in the database system design, try to avoid using like or matche keywords. Sometimes, we can use other operational symbols instead. If we can use the (greater than) or < (less than) symbol to achieve similar requirements. If you really want to use these two keywords, then you need to do a good job of query optimization. If you don't use this two keyword directly in the underlying table, you can reduce its negative impact by querying the report view, or temporary tables, and so on.
　　 Nineth: Use annotations to improve the readability of query statements.
In database design, there is a very strange phenomenon. Some expert database designers, when writing query statements, do not make detailed comments on the statement. Sometimes, the content of a comment goes well beyond the length of the query code itself. However, often some of the novice database designers do not like to write comment statements. This is a very unusual phenomenon.
When I first contacted the database, I did not like to write comment statements. I think it's a waste of time writing an annotation statement. However, an accidental incident let the author get rid of this bad habit. At that time, the author in the observation of an expert design database, was stunned by its dense comments. After reading its code, I can easily read all of its code. Deserves to be the expert-level figure. After reading his notes, he looked at the code he had written, and it was the wizard who saw the wizard. Since then, the author is also slowly cultivating their own code writing habits. Now the author in the database design, the annotation has been written in detail. At least the author's colleagues see my comments, do not look at the source code to know the purpose of the author to achieve.
When writing a note, you should pay attention to a few questions.
First, the more detailed the annotation, the better. In fact, annotations sacrifice a bit of disk space at most, without any adverse impact on the performance of the database. On the contrary, the comments in detail, for the follow-up database maintenance and management, system two development, etc., will provide a lot of help.
Second, it is best to use English annotation. If you use the Chinese annotation, sometimes the database language is improperly set up, when the database instance installs, does not bring the Chinese annotation to the past. Therefore, as a database administrator, to have a certain foundation of English, learn to use English to write notes. In fact, this is not very difficult. Just take a look at the database annotations that are designed by others and copy their commonly used annotations. The task can be accomplished by selecting the appropriate copy and pasting.
The third is not only in writing query statements, to do a good job of code annotation. Add detailed code when writing other code, such as procedures, functions, and so on. To increase the readability of these complex features.
Four is the content of the annotation. A general comment should contain the following aspects. The first is the function that this code wants to implement, the second is the parameter that this code needs to call, and the third is the result of the output of this piece of code. If multiple tables associated with the query, it is best to explain the relationship between the tables. If the function is called directly in the query, it is better to indicate the function of the function; Terminating a principle is to let others see this annotation, no longer need to query other information, you can understand the meaning of this piece of code.
The detailed annotation content, not only will not reduce the database performance, but also can improve the database management and maintenance efficiency, but also can speed up the design of the foreground application development. And because the query statement is the most commonly used statement in the system, it is also the most frequently referenced statement. Therefore, in the query statement, more need to do the relevant comments.
　　 The Tenth method: Limit the lines that users use when necessary.
When using a number of large management systems, such as the ERP system. If we query product information, by default, if the record is more, it will not all the records are queried. As in the ERP system's database, it has 20,000 product information. and the terminal class product information occupies 1000. At this point, we are in the query conditions, if the product category is limited to "terminal", then its query results may not be all terminals. The default display may only be the front 100 terminal products. If users need to see all of the product information, you need to click the "Show All" button to display all the information.
In fact, whether some applications are so designed, there are limitations in the Oracle database itself. If you query the data directly in the Pl/sql client, the record displayed by default is also limited, and does not query all eligible statements. If the user needs to query all eligible records, you need to click the "Continue" button to have the database display all the records.
Why do similar restrictions? This is mainly to improve the performance of database queries. We are directly on the database server, in the millions of records query, show hundreds of records and the reality of thousands of records of the time spent significantly different. The former may take only 3 seconds. And the latter may take 1 minutes. Therefore, in order to reduce the time that users wait, we often need to limit the number of records that are displayed by default for the first query.
We often use top 100来 in query statements to let 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 chronological order in which records are created. The record that was created at the latest is displayed at the top. Analogy
When the user needs data in the front 100, there is no need to look at other records. On the contrary, if not in the case, you will need to query all the record information.

One is to place the count function flexibly, because a blank row is taken into account when the Count function is used to count the number of records. For example, in a datasheet there are usually two categories of sequence fields and other meaningful fields. Sometimes it is possible to have content in the sequence field and there is no content in the other fields, the empty record is taken into account when the Count function is used to count the number of records. Obviously, there will be a statistical error. So, the count function should be placed in which position, or more exquisite. Generally speaking, the author suggests not to put on the serial number field, but to put in some key entity fields. If you count the number of employees, you can put them on the employee's name or number, and so on.
Second, flexible with other functions with the use. As in the above example, the author said that sometimes the user needs to know what the department and the position have now, we can use the distinct function to find the specific department. However, I just want to know how many departments and positions there are, and at this point, we can use count and distinct function to find the data we need. In the Count function, you can specify all and distinct options. By default, is the All option, which indicates that all rows are counted, including duplicate rows. Distinct, however, indicates that only rows that are not duplicates are counted. Thus, the Count function can be used with other functions to simplify our query and improve query efficiency.
The Fifth method: only fields that are required when querying.
Sometimes, users need to use the same table for different query requirements. Include a lot of content in the Employee Information table. Sometimes the user wants to know how many employees there are, how many management staff there are, what the production line staff has, or what the employees are about to expire. To do this, there is a problem, because the content is basically in the same table, which is implemented in the same view, but according to different requirements, design different views?
If the single from the technical considerations, both of which can be achieved, will not have much difficulty. However, if you consider the database performance, it is better to use different views to achieve different requirements.
On the one hand, if you are talking about security, you can control the relevant access rights according to different views. Visible, the view refinement, in the rights control will be more flexible.
On the other hand, the query efficiency of the data has a very close relationship with the content of the data. If the employee contract expiration information, generally do not need the employee's address information and so on. If this information is also queried, because this field is relatively long, it will take a long time. So, in database design, we should learn to design different views according to the different needs of users. Although this may be more time-consuming in design, it can improve the performance and security of the database. The deal is still in the row.
　　 The sixth method: properly handle null fields.
A null field is a more specific field in the database. Null fields represent unknown values or missing data, and note that if a field has a null value, the field is not a space or 0. When the record is inserted, if the field is not assigned, and there is no default value, the default value for the field system is "Null".
Because this value is more special, in the query, timely and experienced database administrator, sometimes in the process, there will be errors. To this end, the author here summarized some, in the database query, about this empty field query some of the need to pay attention to places.
One is to pay attention to the problem of numeric operations of NULL fields.
As in a payroll management system now, there is a salary scale, which has two fields of basic salary and overtime pay. If a user's base salary is 2000, and his overtime pay is not. When entering this record, because of the overtime pay in this field, there is no input data, and in the database design, there is no this field set 0 default value. So, when this record is saved, the database system automatically assigns the field a value of NULL.
If we use the SELECT statement to query this record, its overtime pay this field shows the data is empty. It looks like a space, but it doesn't actually store a space. At this point, if we use the query statement to want to know, this employee's total salary (that is, overtime pay plus basic salary) for how much when will have what result?
We can take advantage of select Employee name, base salary, overtime pay, base salary + overtime pay as total salary from employee salary form; We can use this statement to find out how much the employee's total salary is. But does this statement query the results we want? We execute this statement, and we find that the results are quite different from what we imagined. The last displayed total salary is in the column, which is a space.
Originally, in the Oracle database design, if a null field is arithmetic with other fields, the result is null. Therefore, if a field is Nuu, the null value is returned by either the subtraction or the end result. This is obviously different from what we imagined.

What should we do to deal with this situation? In the process of database design, there are two main methods of processing. The first is to set the default value for the fields that need to be involved in the design of the table. If 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 you save the data, the system also gives it a default value of 0. In this case, it is possible to get the value we want in the arithmetic. The second is in the query, you need to consider the effect of this null value. Sometimes, if there is already a record in the database, the default value of the database field cannot be changed. In this case, what if we need to arithmetic the null field with the Number field? At this point, we need to assign a value of 0 to the null field at the time of the query. Specifically we can define this in the query statement. Select employee name, base salary, overtime pay, base salary +NVL (overtime pay, 0) as total salary from employee payroll; In this case, when the value of the overtime pay is NULL, the system treats it as a 0来 when it is operating. In so doing, we can get the results we want. In general, however, this approach is a last resort. Best of all, when designing a database table, set a default value of 0 for the related field. In addition, there is a function similar to the function of the NVL function, but it is a NVL2, its expression is NVL2 (parameter 1, parameter 2, Parameter 3). It means that when parameter 1 is not NULL, the value returned is parameter 2, and when parameter 1 is null, the range is parameter 3. If we use this function to realize the purpose of NVL function, then we need to rewrite the function parameter of the above case: NAV2 (overtime pay, basic salary + overtime pay, basic salary). It can be seen that two functions have the same similarities. What kind of function to use is good, then you need to be based on the interests of the database administrator to choose. The second is how to query for null fields. If you now have a basic employee information sheet, which has a field with an ID number. Now if the user wants to know, which employees have not yet recorded the ID card number information, how to do? Because this null field is not a space or 0. If we use ' 0 ' or ' (space) as the query condition in the query conditional statement, we can't find the result we need. At this point, in the database, a function is null that provides a dedicated user query for NULL field records. If we now want to know which employees do not have ID information, we can use the following statements to implement. Select employee name, ID number from Employee basic information table where ID number is not null; Through the above statement, you can find the purpose of finding the employee information that the ID card is empty. 　 The seventh method: A lot of use fuzzy query. In the application system design, if the user to complete the input of all the query conditions, this requirement for ordinary users, too harsh. When the software project training or implementation, for a variety of reasons, users in the query, often only input part of the input conditions. When encountering this kind of situation, it is necessary to implement fuzzy query when query design. In this case, even if the user entered the query condition is not complete, you can query the relevant content. If the user inquires a product information, it may not remember the specific name of a product, only know it is called terminal, and other specific information is not clear. At this point, simply enter the terminal in the Name field or the specification field, and you can include the "terminal" record information from the two fields in the query in the system. In this case, the user only needs to continue searching in the results of the query. This tells us that database managers, in the database system design, need to use the function of fuzzy query. Specifically, you need to pay attention to the following aspects of the content. First, the case should not be differentiated. By default, the case is case-sensitive when querying the database. That is, there is now an ABC of the field, and we cannot find this record when we query ABC. Because the case is different. As for the users of the previous application, they are not able to distinguish the English case when they enter the query condition. Although case-insensitive can also be implemented in a front-end application, it is generally simpler to implement in a database than in a front-end system. Therefore, the author is recommended in the background of the database to achieve this case of automatic conversion function. When querying, do not be case-sensitive. This may be the country's unique national conditions. The second is to realize the fuzzy query before and after. If there is a field of content for "good study", if I now enter query conditions for learning, good, studious, etc., can be traced to this condition record. How do you design a conditional statement for a query at this point? This is the concept of a back-and-forth fuzzy query. In fact, to achieve this function is also very simple. In the foreground query statement design, when the query conditions passed to the background database system, in the query parameters before and after, add the parameters of fuzzy query respectively. That is, if we enter the 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 speaking, the fuzzy query needs the foreground application and the backstage database to realize together, such processing, the workload will be much less, also can improve the database operation efficiency. The third is to realize fuzzy query by Beteen function. If there is an attendance system, an employee wants to know their overtime in September, only in the query conditions, input time for September 1 to September 30, you can query the information they need. This is through the Beteen function to implement the fuzzy query. I have encountered a platform-type ERP system before, he did a good job in this respect. This product will be able to design their own related reports. In report design, you can query a single date on a date-based query condition, and you can also query for a range of records. This makes use of this function. In addition, the function of fuzzy query can be realized as well as in function and so on. As a database manager, we should choose these fuzzy query functions reasonably from the view of improving the query performance of the database. And, also need to cooperate with the foreground application program design, handle the function of the fuzzy query well. The eighth method: Careful use like and other wildcard characters. The LIKE keyword, technically, is a very friendly wildcard character. With this wildcard, we can implement a lot of fuzzy queries. such as in a personnel file system, users want to know the ID card number to "339005" personnel information, at this time, you can use the like statement to achieve. We can use the following conditional statement to achieve our requirements, "where ID number like ' 339,005% '". With this conditional statement, you can find the number that starts with 339005 for all IDs. However, when the user uses the like statement in a large table, it is found that the query statement is running very slowly. What is the cause of this? In fact, whether it is like keyword, if the use of matches keyword, if in a large number of data to find a record of the conditions, then its operating efficiency is also relatively low. This is mainly due to its technical characteristics. Like and matches two keywords that support wildcard matching. This is called "formal expression" in some professional books. However, because of the use of these keyword queries, the database system is not indexed to query, but the use of sequential scanning methods to query. Obviously, this technical characteristic, has caused the like and mateches two keyword query efficiency low. Especially in the complex query or large table query, users can obviously feel the speed is relatively slow. An index is an important data structure in a database. If the index is used reasonably, it can greatly improve the query performance of the database. In general, when we design the database, we should make full use of the index to improve the efficiency of database operation. For some of the query functions that are often needed, we need to index columns that do not have a foreign key specified, and you need to set up a composite index on those columns if you are querying large table data, and you want to sort it by the values of several fields. Especially on some application systems, this field can often be sorted by the name of the following field. In this case, it is more necessary to establish indexes on these frequently sorted columns to increase the efficiency of reordering. Visible, if the query, if you can not use the index to improve query efficiency, it is like a sports car loses four-wheel drive, the speed will be greatly affected. Therefore, in the database system design, try to avoid using like or matche keywords. Sometimes, we can use other operational symbols instead. If we can use the (greater than) or < (less than) symbol to achieve similar requirements. If you really want to use these two keywords, then you need to do a good job of query optimization. If you don't use this two keyword directly in the underlying table, you can reduce its negative impact by querying the report view, or temporary tables, and so on. 　　 Nineth: Use annotations to improve the readability of query statements. In database design, there is a very strange phenomenon. Some expert database designers, when writing query statements, do not make detailed comments on the statement. Sometimes, the content of a comment goes well beyond the length of the query code itself. However, often some of the novice database designers do not like to write comment statements. This is a very unusual phenomenon. When I first contacted the database, I did not like to write comment statements. I think it's a waste of time writing an annotation statement. However, an accidental incident let the author get rid of this bad habit. At that time, the author in the observation of an expert design database, was stunned by its dense comments. After reading its code, I can easily read all of its code. Deserves to be the expert-level figure. After reading his notes, he looked at the code he had written, and it was the wizard who saw the wizard. Since then, the author is also slowly cultivating their own code writing habits. Now the author in the database design, the annotation has been written in detail. At least the author's colleagues see my comments, do not look at the source code to know the purpose of the author to achieve. When writing a note, you should pay attention to a few questions. First, the more detailed the annotation, the better. In fact, annotations sacrifice a bit of disk space at most, without any adverse impact on the performance of the database. On the contrary, the comments in detail, for the follow-up database maintenance and management, system two development, etc., will provide a lot of help. Second, it is best to use English annotation. If you use the Chinese annotation, sometimes the database language is improperly set up, when the database instance installs, does not bring the Chinese annotation to the past. Therefore, as a database administrator, to have a certain foundation of English, learn to use English to write notes. In fact, this is not very difficult. Just take a look at the database annotations that are designed by others and copy their commonly used annotations. The task can be accomplished by selecting the appropriate copy and pasting. The third is not only in writing query statements, to do a good job of code annotation. Add detailed code when writing other code, such as procedures, functions, and so on. To increase the readability of these complex features. Four is the content of the annotation. A general comment should contain the following aspects. The first is the function that this code wants to implement, the second is the parameter that this code needs to call, and the third is the result of the output of this piece of code. If multiple tables associated with the query, it is best to explain the relationship between the tables. If the function is called directly in the query, it is better to indicate the function of the function; Terminating a principle is to let others see this annotation, no longer need to query other information, you can understand the meaning of this piece of code. The detailed annotation content, not only will not reduce the database performance, but also can improve the database management and maintenance efficiency, but also can speed up the design of the foreground application development. And because the query statement is the most commonly used statement in the system, it is also the most frequently referenced statement. Therefore, in the query statement, more need to do the relevant comments. 　　 The Tenth method: Limit the lines that users use when necessary. When using a number of large management systems, such as the ERP system. If we query product information, by default, if the record is more, it will not all the records are queried. As in the ERP system's database, it has 20,000 product information. and the terminal class product information occupies 1000. At this point, we are in the query conditions, if the product category is limited to "terminal", then its query results may not be all terminals. The default display may only be the front 100 terminal products. If users need to see all of the product information, you need to click the "Show All" button to display all the information. In fact, whether some applications are so designed, there are limitations in the Oracle database itself. If you query the data directly in the Pl/sql client, the record displayed by default is also limited, and does not query all eligible statements. If the user needs to query all eligible records, you need to click the "Continue" button to have the database display all the records. Why do similar restrictions? This is mainly to improve the performance of database queries. We are directly on the database server, in the millions of records query, show hundreds of records and the reality of thousands of records of the time spent significantly different. The former may take only 3 seconds. And the latter may take 1 minutes. Therefore, in order to reduce the time that users wait, we often need to limit the number of records that are displayed by default for the first query. We often use top 100来 in query statements to let 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 chronological order in which records are created. The record that was created at the latest is displayed at the top. Analogy When the user needs data in the front 100, there is no need to look at other records. On the contrary, if not in the case, you will need to query all the record information.