Several suggestions on Oracle Optimization

Tips on Oracle optimization: Methods for Improving ORACLE performance (indexing SQL statements as much as possible) SQLOracle script I. question raised at the early stage of application system development, due to the small amount of data in the development database, you cannot understand the performance of SQL statement writing for querying SQL statements and writing complex views. However, after submitting the application system to the actual application, with the increase of data in the database, the response speed of the system has become one of the most important problems to be solved by the system. An 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 a system can not simply implement its functions, instead, we need to write high-quality SQL statements 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 the SQL statement is unreasonable, the optimizer will delete the index and use full table scan, in general, such SQL statements are so-called inferior SQL statements. When writing SQL statements, we should be clear about the optimizer's principles for deleting indexes, which helps to write high-performance SQL statements. Ii. Notes for writing SQL statements The following describes the issues that need attention in the where clause of some SQL statements. In these where clauses, even if some columns have indexes, the system cannot use these indexes when running the SQL statement because of poor SQL writing. The full table scan is also used, this greatly reduces the response speed. 1. is null and is not null cannot be indexed using null. 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 statement optimizer that uses is null or is not null in the where clause cannot use indexes. 2. For joined columns, the optimizer does not use indexes even if the last joined value is a static value. Let's take a look at an example. Suppose there is a employee table (employee). For a employee's surname and name are divided into two columns for storage (FIRST_NAME and LAST_NAME), we want to query a table named Bill. bill Cliton employees. The following is an SQL statement that uses join queries, select * from employss where first_name | '| last_name = 'beill cliton' the preceding statement can be used to check whether the employee Bill Cliton exists. However, pay attention to the following, the system optimizer does not use indexes created based on last_name. When the following SQL statement is used, the Oracle system can use an index created based on last_name. Select * from employee where first_name = 'beill' and last_name = 'cliton' what should I do in the following situations? If a variable (name) stores 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. Under www.2cto.com 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. the like statement with a wildcard (%) is also described in the preceding example. Currently, You need to query the persons whose names contain cliton in the employee table. You can use the following query SQL statement: select * from employee where last_name like '% cliton %' because the wildcard (%) appears at the beginning of the search term, therefore, 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 a wildcard appears at another position of a string, the optimizer can use the index. In the following query, the query result is sorted by the select * from employee where last_name like 'C % '4. 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. You can also add functions to the columns (such as joining or appending ). Any non-index item or computed expression in the Order by statement will reduce the query speed. Check the order by statement carefully to find out non-index items or expressions, which will reduce performance. To solve this problem, rewrite the order by statement to use the index. You can also create another index for the column you are using. Avoid using an expression in the order by clause. 5. NOT we often use some logical expressions in the where clause during queries, such as greater than, less 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, you should add brackets before the reverse phrase and add the NOT operator before the phrase. NOT operator is included in another logical operator, which is NOT equal to (<>;) 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' then let's look at the following example: select * from employee where salary <>; 3000; for this query, you can rewrite it to NOT: select * from employee where salary <3000 or salary>; 3000; 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 for salary columns, while the first query does not. 6. IN and EXISTS sometimes compare a column with a series of values. The simplest way is to use subqueries in the where clause. Www.2cto.com can use two subqueries 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. Use the EXISTS clause to query the data extracted from the table without a pipe. It only displays 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 statement 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 runs 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. Author: zhangxijian1988