[SQL] Notes on SQL sentence writing Efficiency

I. Question proposal
At the early stage of application system development, due to the relatively small amount of data in the development database, the performance of SQL syntax writing is not good for querying SQL syntax and writing complex views,
However, if the application system is submitted to the actual application, as the data in the database increases, the system response speed becomes one of the most important problems to be solved by the system.
An important aspect of system optimization is the optimization of SQL syntax.
For massive data, the speed difference between inferior SQL syntax and superior SQL syntax can reach hundreds of times. It can be seen that a system can not simply implement its functions, instead, we need to write high-quality SQL syntaxes to improve system availability.

In most cases, Oracle uses indexes to traverse tables faster. The optimizer improves performance based on the defined indexes.
However, if the SQL code written in the WHERE clause of SQL syntax is unreasonable, the optimizer will delete the index and use the full table scan, this SQL syntax is generally called an inferior SQL syntax.
When using explain SQL syntax, we should be clear about the principle that the optimizer uses to delete indexes, which helps to write high-performance SQL syntax.

Ii. Notes for SQL syntax writing
The following describes the issues that need to be paid attention to in the WHERE clause of some SQL syntax.
In these where clauses, even if some columns have indexes, the system cannot use these indexes when executing the SQL syntax because poor SQL statements are compiled, using Full table scanning also reduces the response speed.

1. Is null and is not null
Null cannot be used as an index. Any column containing null values will not be included in the index.
Even if there are multiple columns in the index, as long as one of these columns contains null, this column will be excluded from the index.
That is to say, if a column has a null value, even if the column is indexed, the performance will not be improved.

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

2. Join Columns

For joined columns, the optimizer does not use indexes even if the last join value is a static value.
Let's take a look at an example. Assume that there is a employee table (employee), which stores the names and surnames of an employee in two columns (first_name and last_name ),
Now I want to query an employee named Bill Cliton.

The following is an SQL syntax that uses join queries,

Select * From employss
Where
First_name | \ '| last_name = \ 'beill Cliton \';

The above syntax can be used to check whether there is a bill Cliton employee. However, it should be noted that the system optimizer is not used for the index created based on last_name.

When the following SQL syntax is used, the Oracle system can use an index based on last_name.

Select * from employee
Where
First_name = \ 'beill \ 'and last_name = \ 'cliton \';

What should we do in the following situations?
If a variable (name) contains the name of the employee Bill Cliton, how can we avoid full traversal and use indexes in this case?
You can use a function to separate the surname and name in the variable name. However, note that this function cannot be used in the index column. The following is an 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 syntax with wildcard (%)

The above example shows this situation. Currently, You need to query the persons whose names contain Cliton in the employee table. You can use the following SQL query syntax:

Select * from employee where last_name like \ '% Cliton % \';

This is because the wildcard (%) appears at the beginning of the search term, so the Oracle system does not use the last_name index.
This situation may not be avoided in many cases, but it must be well understood. Using wildcard characters will reduce the query speed.
However, when the wildcard character appears at another position of the string, the optimizer can use the index. In the following query, the index is used:

Select * from employee where last_name like \ 'C % \';

4. Order by syntax

The order by syntax determines how Oracle sorts the returned query results. The order by syntax does not have any special restrictions on the columns to be sorted. You can also add functions to the columns (such as joining or appending ).
Any non-index item or computed expression in the order by syntax will reduce the query speed.

Check the order by syntax carefully to find out non-index items or expressions, which will reduce performance.
To solve this problem, rewrite the order by syntax to use the index. You can also create another index for the column you are using, and avoid using the expression in the order by clause.

5. Not

When querying, we often use some logical expressions in the WHERE clause, such as greater than, smaller than, equal to, and not equal to. We can also use and (and), or (OR) and not (not ).
Not can be used to reverse all logical operators. The following is an example of a not clause:

... Where not (status = \ 'valid \')

If you want to use not, brackets should be added before the phrase to be reversed, and the not operator should be added before the phrase. The not operator is included 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. See the following example:

... Where status <> \ 'invalid \';

Let's look at the example below:

Select * from employee where salary <> 3000;

You can rewrite this query to not using not:

Select * from employee where salary <3000 or salary> 3000;

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

6. In and exists

Sometimes a column is compared with a series of values. The simplest way is to use subqueries in the where clause. Subqueries in two formats can be used 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. In fact, the second format is far more efficient than the first one. In Oracle, almost all in operator subqueries can be rewritten to subqueries using exists.

In the second format, the subquery starts with "select \ 'x. Use the exists clause to query the information extracted from the table without a pipe. It only reviews the WHERE clause. In this way, the optimizer does not have to traverse the entire table, but only performs the work based on the index (Here we assume that the column used in the where syntax has an index ). Compared with the in clause, exists uses connected subqueries, which is more difficult to construct than in subqueries.

By using exist, the Oracle system first checks the primary query and then executes the subquery until it finds the first match, which saves time. When executing an in subquery, the Oracle system first executes the subquery and stores the obtained result list in a temporary table with an index. Before executing a subquery, the system suspends the primary query. After the subquery is executed, it is stored in the temporary table and then executes the primary query. This is why exists is faster than in queries.

At the same time, do not exists should be used as much as possible to replace not in, although both use not (the index cannot be used to reduce the speed), not exists is more efficient than not in query.