Improve SQL performance measures!
Last Source: http: //www.ourasp.net/ Author: Shi Xiao Fei included in: a raise, the issue of December 19, 2001 early in application development, due to the relatively small development of the database data for the query SQL statements, complex view The writing, etc., the performance of the SQL statement is the performance of the various ways of writing, but if the application system is submitted to the actual application, with the increase in data in the database, the system's response speed becomes the most important issue that needs to solve the current system. one. 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 Note Problem The following is a detailed description of the issues required to pay attention to the WHERE clause 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. 1. Is NULL and IS NOT NULL cannot use NULL as an 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. The join column For columns with join, even if the final coupling value is a static value, the optimizer does not use indexes. 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 using a join query, select * from employsswherefirst_name || '' || Last_Name = 'Beill Cliton' The statement is fully available to whether there is a Bill Cliton, but you need to pay attention to the system optimizer Indexes created based on Last_name are not used. When using the following SQL statement, the Oracle system can use the index created based on_name. Select * from Employewherefirst_name = 'beill' and last_name = 'cliton' How to 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.
The following is a SQL query script: select * from employeewherefirst_name = SUBSTR ( '&& name', 1, INSTR ( '&& name', '') -1) andlast_name = SUBSTR ( '&& name', INSTR ( '&& name', '') 1 3. The LIKE statement of the belt card (%) 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 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 Last_name Like 'C%' 4. The Order By Statement ORDER BY statement determines how Oracle will sort 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 when query, such as greater than, less than, equal to, or equal, 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 a 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 the example: ... WHERE status <> INVALID 'and then look at the following example: Select * from Employee Where Salary <> 3000; For this query, you can rewrite not to use NOT: Select * from Employee WHERE SALY <3000 or Salary> 3000; although the results of these two queries are the same, the second query scheme is queried than the first query The program is faster. 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 compare a column and a range of values. The easiest way is to use subqueries in the WHERE clause. Two formats can be used in the WHERE clause.
