[Author: Shi Xiao Fei]
First, the question is proposed
In the initial stage of application system development, due to the relatively small development database data, for querying SQL statements, the complicated views of the complex view may not have the performance of the SQL statement, but if the application is submitted, after the application is submitted, along with the database Increase in data, the response speed of the system is one of the most important issues that need to be solved by the system. A very important aspect of system optimization is the optimization of SQL statements. For massive data, inferior SQL statements and high quality SQL statements can reach hundreds of times, visible to a system is not simply implementing its function, but to write high quality SQL statements, improve system availability .
In most cases, Oracle uses the index to traverse the table, the optimizer mainly improves performance based on the defined index. However, if the SQL code written in the WHERE clause written in the SQL statement is unreasonable, it will cause an optimizer to delete an index and use a full mete scan, usually this SQL statement is the so-called inferior SQL statement. When writing SQL statements, we should clearly remove the index according to what principles, which helps to write high-performance SQL statements.
Second, SQL statement writing attention problem
The following is a detailed description of the issues that need to pay attention to the WHERE clause writing of some SQL statements. In these WHERE clauses, even if some columns have an index, since the inferior SQL is written, the system cannot use the index when running the SQL statement, and the full surface scan is used, which causes the pole of the response speed. Large decrease.
IS NULL & IS NOT NULL
You cannot use NULL to index, and any columns containing NULL values will not be included in the index. Even if the index has multiple columns, as long as there is a null in these columns, the column is excluded from the index. That is to say, if there is a null value, even if the index of the index is not improved.
Any statement optimizer using is NULL or IS NOT NULL in the WHERE clause is not allowed to use indexes.
2. Joint columns
For a column, the optimizer does not use the index even if the final connection value is a static value. Let's take a look at an example. It is assumed to have an employee table, divided into two columns in two columns (first_name and last_name), now query a staff called Bill Clinton.
Below is a SQL statement that uses a join query.
Select * from Employss
WHERE
First_name || '' || Last_name = 'Beill Cliton';
Above this statement can be queried in the employee of Bill Cliton, but it is important to note that the system optimizer is not used by the index created based on Last_Name.
When using the following SQL statement, the Oracle system can use the index created based on_name.
SELECT * from Employee
WHERE
FigSt_name = 'beill' and last_name = 'cliton';
How do I deal with this situation? If a variable (Name) is stored in the name of the staff of the Bill Cliton, how do we avoid traversing throughout, use the index? You can use a function, you can separate the last names and names in the Variable Name, but you need to pay attention, this function does not act on the index column. Here is the SQL query script:
SELECT * from Employee
WHERE
First_name = Substr ('&& name', 1, INSTR ('&& name', '') -1)
Last_name = Substr ('&& name', INSTR ('&& name', '') 1)
3. Talking> 1 LIKE statement
This is the same as the example above. The current demand is like this, requiring people who contain cliton in the query name in the employee table. You can use the following query SQL statement:
Select * from Employee WHERE LAST_NAME LIKE '% CLITON%';
Here, due to wildcard (%), the Oracle system does not use the index of Last_name. In many cases, this may not be avoided, but must have the bottom, and wildcards will reduce the query speed so use. However, when the wildcard appears in other locations of the string, the optimizer can utilize the index. The index in the query below is used:
Select * from Employee WHERE LAST_NAME LIKE 'C%';
4. ORDER BY statement
The ORDER BY statement determines how Oracle will be sorted by the returned query results. The ORDER BY statement does not have a special restriction on the columns to be sorted, or the function can be added to the column (like join or attachment, etc.). Any non-index item or computational expression in the ORDER B statement will reduce the query speed.
Carefully check the ORDER BY statement to find non-indexes or expressions, they reduce performance. The solution to this problem is to override the Order By statement to use an index, or establish another index for the columns used, and should absolutely avoid using expressions in the ORDER BY clause.
5. NOT
We often use some logical expressions in the WHERE clause, such as greater than, less than, equal to, and eliminating, and not equals, and (and), OR (or), and NOT (non). NOT can be used to reverse any logical operation symbol. Below is an example of a Not clause:
... WHERE NOT (status = 'valid')
If you want to use NOT, you should add parentheses in front of the reflective phrase and add the NOT operator in front of the phrase. The NOT operator is included in another logical operator, which is not equal to (<>) operator. In other words, even if you don't explicitly join the NOT words in the WHERE clause, NOT is still in the operator, see example:
... WHERE STATUS <> INVALID ';
Look at the following example:
Select * from Employee WHERE SALY <> 3000;
For this query, it can be rewritten as not using NOT:
Select * from Employee WHERE SALY <3000 or 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 indexes to the SALARY column, and the first query cannot use an index.
6. In and EXISTS
Sometimes a column and a range of values are compared. The easiest way is to use subqueries in the WHERE clause. 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 relatively easy to write, and actually the second format is much higher than the first format. In Oracle, you can rewrite almost all IN operator sub-queries to use the subquery using Exists.
In the second format, the subquery starts with 'SELECT' X '. Use the EXISTS clause No, no matter what data extracted from the table, it only views the WHERE clause. Such an optimizer does not have to traverse the entire table, and the work can be done based on the index (here it assumes an index in the column used in the WHERE statement). Relative to the in clause, exists uses a linked sub-query to construct some difficulties than in sub child.
By using the exist, the Oracle system will first check the main query, then run the sub-query until it finds the first match, which saves time. When the IN subquery is executed, the Oracle System first performs sub-queries and stores the result list in a temporary table added in an index. Before executing subqueries, the system will hang the primary query first. When the sub-query is executed, the primary query is stored in the temporary table. This is why using Exists is much faster than using IN usually queries.
At the same time, NOT EXISTS should be used as much as possible to replace Not in, although both NOTs (can not use indexes), NOT EXISTS is higher than NOT inquiry efficiency.
