Shulou(Shulou.com)05/31 Report--

This article is to share with you what are the notes on SQL sentences, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

In the initial stage of the development of the application system, due to the lack of database data, the performance of various writing methods of SQL statements can not be realized by querying SQL statements and writing complex views, but if the application system is submitted to the practical application, with the increase of data in the database, the response speed of the system has become one of the most important problems that the system needs to solve. The following editor will explain the considerations for writing high-performance SQL statements.

What are the considerations for writing high-performance SQL statements

In most cases, Oracle uses indexes to traverse tables faster, and the optimizer mainly improves performance based on 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 a full table scan. Generally speaking, this kind of SQL statement is the so-called inferior SQL statement. When writing SQL statements, we should know what principles the optimizer uses to delete indexes, which helps to write high-performance SQL statements.

Problems needing attention in writing SQL statements

The following is a detailed introduction to the problems that should be paid attention to in the writing of where clauses of some SQL statements. In these where clauses, even if some columns have an index, the system cannot use the index when running the SQL statement because of the poor SQL, and the full table scan is also used, which greatly reduces the response speed.

1. IS NULL and IS NOT NULL

You cannot use null as an index, and any column that contains a null value will not be included in the index. Even if the index has multiple columns, as long as one of these columns contains null, the column is excluded from the index. That is, if a column has a null value, even indexing that column will not improve performance.

Any statement optimizer that uses is null or is not null in the where clause does not allow the use of indexes.

two。 Join column

For columns with joins, the optimizer does not use indexes even if the final join value is a static value. Let's take a look at an example. Suppose there is a job list (employee). For an employee whose last name and first name are stored in two columns (FIRST_NAME and LAST_NAME), we now want to query a bill. An employee of Bill Cliton.

The following is a SQL statement that uses a join query

Select * from employss

Where

First_name | |''| | last_name = 'Beill Cliton'

The above statement can well find out if there is an employee named Bill Cliton, but it is important to note that the system optimizer does not use indexes created based on last_name.

When writing the following SQL statement, the Oracle system can adopt an index created based on last_name.

Select * from employee

Where

First_name = 'Beill' and last_name =' Cliton'

How to deal with the following situations? If a variable (name) holds the name of the Bill Cliton employee, how can we avoid full traversal and use an index in this case? You can use a function to separate the first name from the first name in the variable name, but it is important to note that this function does not work on index columns. Here is the SQL query script:

Select * from employee

Where

First_name = SUBSTR ('& & name',1,INSTR ('& & name','')-1)

And

Last_name = SUBSTR ('& & name',INSTR ('& name','') + 1)

3. Like statement with wildcard (%)

Also use the above example to see this situation. The current requirement is to query the employee table for people whose names contain cliton. You can use the following query SQL statement:

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

Here, because the wildcard (%) appears at the beginning of the search word, the Oracle system does not use last_name 's index. This may not be avoided in many cases, but be sure to keep in mind that using wildcards in this way can slow down queries. However, when wildcards appear elsewhere in the string, the optimizer can take advantage of the index. The index is used in the following query:

Select * from employee where last_name like'c%'

What are the considerations for writing high-performance SQL statements

4. Order by statement

The ORDER BY statement determines how Oracle sorts the returned query results. The Order by statement does not have any special restrictions on the columns to be sorted, and you can add functions to the columns (such as joins or attachments, etc.). Any non-indexed items or expressions that are evaluated in the Order by statement will slow down the query.

Carefully examine the order by statement to find non-index items or expressions, which can degrade performance. The solution to this problem is to rewrite the order by statement to use the index, or to create another index on the column used, while absolutely avoiding the use of expressions in the order by clause.

5. NOT

When querying, we often use some logical expressions in the where clause, such as greater than, less than, equal to, not equal, and so on. We can also use and (and), or (or), and not (not). NOT can be used to reverse any logical operation symbol. Here is an example of a NOT clause:

... Where not (status = 'VALID')

If you are using NOT, you should precede the inverted phrase with parentheses and the NOT operator before the phrase. The NOT operator is contained in another logical operator, which is not equal to the () operator. In other words, even if the NOT word is not explicitly added to the query where clause, NOT is still in the operator, as shown in the following example:

... Where status' INVALID'

Take a look at the following example:

Select * from employee where salary3000

For this query, you can rewrite it to not use NOT:

Select * from employee where salary3000

Although the results of the two queries are the same, the second query scheme is faster than the first query scheme. The second query allows Oracle to use indexes on salary columns, while the first query cannot.

6. IN and EXISTS

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

The first format is to use the IN operator:

... Where column in (select * from... Where.)

The second format is to use the EXIST operator:

... Where exists (select'X' from... where...)

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

In the second format, the subquery starts with 'select' X'. Use the EXISTS clause to view only the where clause regardless of what data is extracted from the table by the subquery. In this way, the optimizer does not have to traverse the entire table and can do its work based solely on the index (here, it is assumed that the column used in the where statement has an index). Compared to the IN clause, EXISTS uses associative subqueries, which are more difficult to construct than in subqueries.

By using the EXIST,Oracle system, the main query is checked first, and then the subquery is run until it finds the first match, which saves time. When Oracle system executes in subquery, it first executes the subquery and stores the list of results in an indexed temporary table. Before executing the subquery, the system suspends the main query, waits for the execution of the subquery, and then executes the main query after it is stored in the temporary table. This is why using EXISTS is usually faster than using IN.

At the same time, you should use NOT EXISTS instead of NOT IN whenever possible, although both use NOT (indexes cannot be used to slow down), NOT EXISTS is more efficient than NOT IN query.

What are the above points for attention in SQL sentences? the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.