In the early stage of application system development, due to the lack of database data, we can't understand the performance of various writing methods of SQL statements for querying SQL statements and writing complex views. However, if the application system is submitted to practical application, with the increase of data in the database, the response speed of the system becomes one of the most important problems that the system needs to solve at present. A very important aspect of system optimization is the optimization of SQL statements. For massive data, the speed difference between inferior SQL statements and high-quality SQL statements can reach hundreds of times. It can be seen that for a system, it is not simply to realize its functions, but to write high-quality SQL statements to improve the usability of the system.

In most cases, Oracle uses indexes to traverse tables faster, and the optimizer mainly improves performance according to the defined indexes. However, if the SQL code written in the where clause of the SQL statement is unreasonable, it will cause the optimizer to delete the index and use full table scanning. Generally, this kind of SQL statement is the so-called inferior SQL statement. When writing SQL statements, it is very helpful to understand the principle of the optimizer deleting indexes, which is helpful for writing high-performance SQL statements.

The following is a detailed introduction to some problems that should be paid attention to when writing where clauses in SQL statements. In these where clauses, even if some columns have indexes, the system can't use indexes when running SQL statements because of poor SQL quality, and it also uses full table scanning, which greatly reduces the response speed.

1. is empty and not empty:

Null cannot be used as an index, and any column with null value will not be included in the index. Even if the index has multiple columns, as long as one of the columns contains null, the column will be excluded from the index. That is, if a column has a null value, even if it is indexed, it will not improve performance.

Any statement optimizer that uses is null or not null in the where clause is not allowed to use indexes.

2. Join columns:

For columns with joins, the optimizer does not use indexes even if the last join value is static. Let's look at an example. Suppose there is an employee table. For the employee's last name and first name, they are stored in two columns (first name and last name). Now we want to inquire about an employee named Bill Cliton.

The following is an SQL statement using a join query.

Select from employees *

where

First name ||' || Last name ='Beill Cliton'

The above statement can completely find out whether there is an employee named Bill Cliton, but it should be noted that the system optimizer does not use the index created based on last_name.

When the following SQL statements are used, the Oracle system can use indexes created based on last names.

Select * from employees

where

First name ='Beill' and last name ='Cliton'

How to deal with the following situation? If the name of employee Bill Cliton is stored in a variable (name), how to avoid going through the whole process and using an index? You can use the function to separate the last name from the first name in the variable name, but it should be noted that this function cannot be used for index columns. The following is the SQL query script:

Select * from employees

where

first _ name = SUBSTR(& amp； & name', 1, instrument (& name','')-1)

and

last _ name = SUBSTR(& amp； & name, instrument (& name? ,' ')+ 1)

3. like statement with wildcard (%):

Also use the above example to see this situation. The current demand is that people whose names include cliton should be queried in the employee table. You can use the following query SQL statements:

Select * from employee where last_name like '%cliton%'

Here, because the wildcard (%) appears at the beginning of the search term, the Oracle system does not use the last name index. In many cases, this situation may be unavoidable, but you must be aware of it. Using wildcards like this will slow down the query speed. However, the optimizer can use indexes when wildcards appear elsewhere in the string. This index is used for the following queries:

Select * from employees, where the last name is "c%"

4.Order by statement:

The ORDER BY statement determines how Oracle sorts the returned query results. The Order by statement has no special restrictions on the columns to be sorted, and functions (such as join or append) can also be added to the columns. Any non-index item or calculation expression in the Order by statement will slow down the query speed.

Examine the order by statement carefully to find non-indexed items or expressions that will degrade performance. The solution to this problem is to rewrite the order by statement to use an index, or create another index for the column used, and absolutely avoid using expressions in the order by clause.

5. no:

We often use some logical expressions in the where clause when querying, such as greater than, less than, equal to and unequal to. And we can also use and, or and not. NOT cannot be used to deny any logical operation symbol. The following is an example of the NOT clause:

... if not (status =' active')

If you want to use NOT, you should put parentheses before the inverted phrase and put the NOT operator before the phrase. The NOT operator is contained in another logical operator, which is the inequality () operator. In other words, even if the word NOT is NOT explicitly added to the query where clause, not is still in the operator, as shown in the following example: where status 'INVALID'

Look at the following example: select * from employee where salary 3000;

For this query, not can be rewritten as not: select * from employee where salary 3000;

Although the results of these two queries are the same, the second query scheme will be faster than the first query scheme. The second query allows Oracle to use an index on the salary column, while the first query cannot use an index.

6. Existing in:

Sometimes a column is compared with a series of values. The easiest way is to use a subquery in the where clause. You can use two types of subqueries in the where clause.

The first format is to use the IN operator: ... where is listed in (where is select * from ...);

The second format is to use the EXIST operator: ... the location of existence (choose "X" from it ... where ...);

I believe most people will use the first format because it is easier to write, but in fact the second format is far more efficient than the first format. IN Oracle, almost all in operator subqueries can be rewritten as subqueries using EXISTS.

In the second format, the subquery starts with? Select "x" to start. With the EXISTS clause, no matter what data the subquery extracts from the table, it only looks at the where clause. In this way, the optimizer does not have to traverse the whole table, but only needs to complete the work according to the index (assuming that the columns used in the where statement have indexes here). Compared with the IN clause, EXISTS uses a joINed subquery, which is more difficult to construct than the in subquery.

By using EXIST, the Oracle system will check the main query first, and then run the subquery until the first match is found, which can save time. When the Oracle system executes the IN subquery, it first executes the subquery and stores the obtained result list in the temporary table of the index. Before executing the subquery, the system suspends the main query, and then executes the main query after the subquery is completed and stored in the temporary table. This is why usINg EXISTS is faster than using in.

At the same time, we should use NOT EXISTS instead of NOTEIN as much as possible. Although both use NOT (indexes can't be used to slow down), NOT EXISTS is more efficient than NOTEIN query.