Oracle SQL Optimization

SQL optimization should be adjusted from five aspects:
1. Remove unnecessary full table scans for large tables
2. cache full table scan for small tables
3. Verify the use of optimized Indexes
4. Verify the optimized Connection Technology
5. minimize the Cost of the Execution Plan

SQL statement:
Is the only way to operate databases (data;
70% ~ 90% of database resources; independent of the program design logic, compared to the optimization of the program source code, the optimization of SQL statements at a low cost of time and risk;
It can be written in different ways. It is easy to learn and difficult to master.

SQL optimization:
Fixed SQL writing habits. The same queries should be kept as much as possible, and the storage process is more efficient.
Statements in the same format should be written, including uppercase and lowercase letters, punctuation marks, and line breaks.

ORACLE optimizer:
The expression is evaluated whenever possible and the specific syntax structure is converted to an equivalent structure.
Either the result expression can be faster than the source expression
Either the source expression is only an equivalent Semantic Structure of the Result Expression
Different SQL structures sometimes have the same operation (for example, = ANY (subquery) and IN (subquery). ORACLE maps them to a single semantic structure.
1 constant optimization:
Constant calculation is completed at one time when the statement is optimized, rather than at each execution. The following is an expression for retrieving a monthly salary greater than 2000:
Sal> 24000/12
Sal> 2000
Sal * 12 & gt; 24000
If the SQL statement includes the first case, the optimizer simply converts it to the second case.
The optimizer does not simplify the expressions that span the comparison operator. For example, in the third statement, you should try to write the expression that compares the constant with the field, rather than placing the field in the expression. Otherwise, there is no way to optimize it. For example, if there is an index on sal, the first and second can be used, and the third is difficult to use.

2 operator optimization:
The optimizer converts a search expression consisting of the LIKE operator and an expression without wildcards into a "=" operator expression.
For example, the optimizer converts the expression ename LIKE 'Smith 'to ename = 'Smith'
The optimizer can only convert expressions that involve variable-length data types. In the previous example, if the type of the ENAME field is CHAR (10), the optimizer will not perform any conversion.

 

Generally, LIKE is difficult to optimize.
Where:
~~ IN operator optimization:
The optimizer replaces the search expression using the IN comparison operator with the equivalent search expression using the "=" and "OR" operators.
For example, the optimizer replaces the expression ename IN ('Smith ', 'King', 'Jones')
Ename = 'Smith 'OR ename = 'King' OR ename = 'Jones'
Oracle will generate a temporary table in memory for the items following in. Then perform the query.

 

How to compile efficient SQL statements:
Of course, you must consider the optimization of SQL constants and operators. In addition, you also need:
1. Reasonable index design:
For example, a table with 620000 rows of record and rows with different indexes can run the following SQL statements:
Statement
SELECT count (*) FROM record
WHERE date> '123' and date <'123' and amount> 19991201
Statement B
SELECT count (*) FROM record
WHERE date> '2013' and place IN ('bj ', 'sh ')
Statement C
SELECT date, sum (amount) FROM record
Group by date
1. Create a non-clustered index on date
A: (25 seconds)
B: (27 seconds)
C: (55 seconds)
Analysis:
There are a large number of duplicate values on date. In a non-clustered index, data is physically stored on the data page at random. During range search, you must perform a table scan to find all rows in this range.
2. A clustered index on date
A: (14 seconds)
B: (14 seconds)
C: (28 seconds)
Analysis:
Under the clustered index, data is physically stored on the data page in order, and duplicate values are arranged together. Therefore, you can first find the starting and ending points of this range during range search, in addition, only data pages are scanned within this range, which avoids large-scale scanning and improves the query speed.
3 composite indexes on place, date, and amount
A: (26 seconds)
C: (27 seconds)
B :( <1 second)
Analysis:
This is an unreasonable composite index, because its leading column is place, the first and second SQL statements do not reference place, so the upper index is not used; the third SQL uses place, all referenced columns are included in the composite index, which forms an index overwrite, so it is very fast.
4 composite indexes on date, place, and amount
A: (<1 second)
B :( <1 second)
C: (11 seconds)
Analysis:
This is a reasonable composite index. It uses date as the leading column, so that each SQL can use the index, and the index coverage is formed in the first and third SQL statements, so the performance is optimal.

Conclusion 1
By default, an index is a non-clustered index, but sometimes it is not the best. A reasonable index design should be based on the analysis and prediction of various queries. Generally speaking:
Columns with a large number of duplicate values and frequent range queries (between, >,<,>=, <=), order by, and group by are considered to create a clustered index;
Multiple columns are frequently accessed at the same time, and each column contains duplicate values. You can consider creating a composite index. You can create a search for columns with many different values that are frequently used in conditional expressions, do not create indexes on columns with less values. For example, in the "gender" column of the employee table, there are only two different values: "male" and "female", so there is no need to create an index. If an index is created, the query efficiency is not improved, but the update speed is greatly reduced.
Composite indexes should try to overwrite key queries. The leading column must be the most frequently used column.
2. Avoid using incompatible data types:
For example, float and INt, char and varchar, bINary, and varbINary are incompatible. Data Type incompatibility may make the optimizer unable to perform some optimization operations that can be performed originally. For example:
SELECT name FROM employee WHERE salary> 60000
In this statement, if the salary field is of the money type, it is difficult for the optimizer to optimize it because 60000 is an integer. We should convert the integer type into a coin type during programming, instead of waiting for the conversion at runtime.
3 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, the 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 does not allow the use of indexes.
5 IN and OR clauses usually use worksheets to invalidate indexes:
If a large number of duplicate values are not generated, consider splitting the clause. The split clause should contain the index.
6. Avoid or simplify sorting:
Duplicate sorting of large tables should be simplified or avoided. When indexes can be used to automatically generate output in the appropriate order, the optimizer avoids the sorting step. The following are some influencing factors:
The index does not contain one or more columns to be sorted;
The order of columns in the group by or order by clause is different from that of the index;
Sort columns from different tables.
In order to avoid unnecessary sorting, We need to correctly add indexes and reasonably merge database tables (although it may affect table standardization sometimes, it is worthwhile to Improve the efficiency ). If sorting is unavoidable, you should try to simplify it, such as narrowing the column range of sorting.

7. Eliminate sequential access to the data of large table rows:
In nested queries, sequential access to a table may have a fatal impact on query efficiency. For example, the sequential access policy is used to create a nested layer-3 query. IF 1000 rows are queried at each layer, 1 billion rows of data are queried. The primary way to avoid this is to index the connected columns. For example, two tables: Student table (student ID, name, age ??) And Course Selection form (student ID, course number, score ). If you want to connect two tables, you need to create an index on the join field "student ID.
Union can also be used to avoid sequential access. Although all check columns are indexed, some forms of WHERE clauses force the optimizer to use sequential access. The following query forces sequential operations on the orders table:
SELECT * FROM orders WHERE (customer_num = 104 AND order_num> 1001) OR order_num = 1008
Although indexes are created on customer_num and order_num, the optimizer still uses sequential access paths to scan the entire table in the preceding statement. Because this statement is used to retrieve the set of separated rows, it should be changed to the following statement:
SELECT * FROM orders WHERE customer_num = 104 AND order_num> 1001
UNION
SELECT * FROM orders WHERE order_num = 1008
In this way, you can use the index path to process queries.

8. Avoid subqueries:
The label of a column appears in both the primary query and the WHERE clause query. It is very likely that after the column value in the primary query changes, the subquery must perform a new query. The more nested query layers, the lower the efficiency. Therefore, avoid subqueries as much as possible. If the subquery is unavoidable, filter as many rows as possible in the subquery.
9 avoid difficult Regular Expressions:
MATCHES and LIKE keywords support wildcard matching, technically called regular expressions. However, this matching is especially time-consuming.

Example: SELECT * FROM customer WHERE zipcode LIKE "98 ___"
Even if an index is created on the zipcode field, sequential scanning is used in this case. If you change the statement to SELECT * FROM customer WHERE zipcode> "98000", the query will be executed using the index, which will obviously increase the speed.
In addition, avoid non-starting substrings. For example, if SELECT * FROM customer WHERE zipcode [2, 3]> "80" is used in the WHERE clause, non-starting substrings are used. Therefore, this statement does not use indexes.

 

10 unfilled connection conditions:
For example, the table card has 7896 rows, there is a non-clustered index on card_no, the table account has 191122 rows, and there is a non-clustered index on account_no, explain execution of two SQL statements under different table connection conditions:
SELECT sum (a. amount) FROM account a, card B WHERE a. card_no = B. card_no
(20 seconds)
Change SQL:
SELECT sum (a. amount) FROM account a, card B WHERE a. card_no = B. card_no and a. account_no = B. account_no
(<1 second)
Analysis:
Under the first join condition, the optimal query scheme is to use the account as the outer table, and the card as the inner table. The I/O times of the card can be estimated by the following formula:
Outer table account page 22541 + (the first row of the outer table account * the third page corresponding to the first row of the outer table on the card of the inner table) = 191122 times I/O
Under the second join condition, the best query scheme is to use card as the outer table and account as the inner table. The number of I/O times of the account can be estimated by the following formula:
1944 page + on the outer table card (the fourth row of the outer table card * The fourth page corresponding to each row of the outer table on the inner table account) = 7896 times I/O
It can be seen that only a full set of connection conditions can be executed for the best solution.
Before a multi-table operation is executed, the query optimizer lists several possible connection schemes based on the connection conditions and finds the optimal scheme with the minimum system overhead. The join conditions must fully consider the tables with indexes and tables with multiple rows. The selection of the internal and external tables can be determined by the formula: Number of matched rows in the outer table * Number of times each query is performed in the inner table, the minimum product is the best solution.
Unoptimized WHERE clause
Example 1
The columns in the following SQL condition statements have an appropriate index, but the execution speed is very slow:
SELECT * FROM record WHERE substrINg (card_no, 5378) = '20140901'
(13 seconds)
SELECT * FROM record WHERE amount/30 <1000
(11 seconds)
SELECT * FROM record WHERE convert (char (10), date, 112) = '20140901'
(10 seconds)
Analysis:
Any operation results on the column in The WHERE clause are calculated by column one by one during SQL Execution. Therefore, it has to perform table search without using the index on the column; if these results are obtained during query compilation, they can be optimized by the SQL optimizer and indexed to avoid table search. Therefore, rewrite the SQL statement as follows:
SELECT * FROM record WHERE card_no like '201312'
(<1 second)
SELECT * FROM record WHERE amount <1000*30
(<1 second)
SELECT * FROM record WHERE date = '2014/1/01'
(<1 second)
11 optimize queries using temporary tables during storage:
Example
1. read data from the parven table in the order of vendor_num:
SELECT part_num, vendor_num, price FROM parven order by vendor_num
INTO temp pv_by_vn
This statement reads parven (50 pages) sequentially, writes a temporary table (50 pages), and sorts it. Assume that the sorting overhead is 200 pages, which is 300 pages in total.
2. Connect the temporary table to the vendor table, output the result to a temporary table, and sort by part_num:
SELECT pv_by_vn, * vendor. vendor_num FROM pv_by_vn, vendor
WHERE pv_by_vn.vendor_num = vendor. vendor_num
Order by pv_by_vn.part_num
Into tmp pvvn_by_pn
Drop table pv_by_vn
This query reads pv_by_vn (50 pages). It accesses the vendor Table 15 thousand times through the index. However, due to the vendor_num order, in fact, the vendor table is read in an indexed ORDER (40 + 2 = 42 pages). The output table contains about 95 rows on each page, with a total of 160 pages. Writing and accessing these pages triggers 5*160 = 800 reads and writes, and the index reads and writes 892 pages.
3. Connect the output and the part to get the final result:
SELECT pvvn_by_pn. *, part. part_desc FROM pvvn_by_pn, part
WHERE pvvn_by_pn.part_num = part. part_num
Drop table pvvn_by_pn
In this way, the query reads pvvn_by_pn sequentially (160 pages) and reads the part table 15 thousand times through the index. As the index is built, 1772 disk reads and writes are actually performed. The optimized ratio is. ~~ ANY and SOME operators are optimized:
The optimizer replaces ANY and SOME search conditions that follow the Value List with expressions consisting of equivalent operators and "OR.
For example, the optimizer replaces the first statement with the second statement as follows:
Sal> ANY (: first_sal,: second_sal)
Sal>: first_sal OR sal>: second_sal
The optimizer converts ANY and SOME search conditions that follow subqueries into a search expression consisting of "EXISTS" and a corresponding subquery.
For example, the optimizer replaces the first statement with the second statement as follows:
X> ANY (SELECT sal FROM emp WHERE job = 'analyst ')
EXISTS (SELECT sal FROM emp WHERE job = 'analyst' AND x> sal)
~~ ALL operator optimization:
The optimizer replaces the ALL operator following the Value List with an equivalent expression consisting of "=" AND. For example:
Sal> ALL (: first_sal,: second_sal) is replaced:
Sal>: first_sal AND sal>: second_sal
For the ALL expression that follows the subquery, the optimizer replaces it with an expression consisting of ANY and another suitable comparison operator. For example
X> ALL (SELECT sal FROM emp WHERE deptno = 10):
NOT (x = ANY (SELECT sal FROM emp WHERE deptno = 10 ))
Next, the optimizer converts the conversion rule of the second expression that applies to the ANY expression to the following expression:
Not exists (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal)
~~ BETWEEN operator optimization:
The optimizer always uses the comparison operators "> =" and "<=" to replace the BETWEEN operator.
For example, the optimizer replaces the expression sal BETWEEN 2000 AND 3000 with sal> = 2000 AND sal <= 3000.
~~ NOT operator optimization:
The optimizer always tries to simplify the search conditions to eliminate the impact of the "NOT" logical operator, which involves the elimination of the "NOT" operator and the generation of the corresponding comparison operator.
For example, the optimizer replaces the following first statement with the second statement:
NOT deptno = (SELECT deptno FROM emp WHERE ename = 'taylor ')
Deptno <> (SELECT deptno FROM emp WHERE ename = 'taylor ')
Generally, there are many different statements containing the NOT operator. The optimizer's conversion principle is to make the clauses behind the "NOT" operator as simple as possible, even if the result expression may contain more "NOT" operators.
For example, the optimizer replaces the first statement with the second statement as follows:
NOT (sal <1000 OR comm is null)
NOT sal <1000 AND comm is not null sal> = 1000 AND comm IS NOT NULL
 

34 SQL optimization suggestions

(1) select the most efficient table name sequence (only valid in the rule-based Optimizer ):
The ORACLE parser processes the table names in the FROM clause in the order FROM right to left. The table written in the FROM clause (basic table driving table) will be processed first, when the FROM clause contains multiple tables, You must select the table with the least number of records as the base table. If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.
(2) join order in the WHERE clause:
ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join between tables must be written before other WHERE conditions. The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
(3) Avoid using '*' in the SELECT clause '*':
During the parsing process, ORACLE converts '*' into all column names in sequence. This is done by querying the data dictionary, which means it takes more time.
(4) Reduce the number of visits to the database:
ORACLE has performed a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks;
(5) re-set the ARRAYSIZE parameter in SQL * Plus, SQL * Forms, and Pro * C to increase the retrieval data volume for each database access. The recommended value is 200.
(6) use the DECODE function to reduce processing time:
You can use the DECODE function to avoid repeated scan of the same record or join the same table.
(7) simple integration with no associated database access:
If you have several simple database query statements, you can integrate them into a single query (even if there is no relationship between them)
(8) delete duplicate records:
The most efficient way to delete duplicate records (because ROWID is used) is as follows:
Delete from emp e where e. ROWID> (select min (X. ROWID)
From emp x where x. EMP_NO = E. EMP_NO );
(9) replace DELETE with TRUNCATE:
When deleting records in a table, a rollback segment is usually used to store information that can be recovered. if you do not have a COMMIT transaction, ORACLE will recover the data to the State before the deletion (which is precisely the State before the deletion command is executed). When TRUNCATE is used, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short. (The translator Press: TRUNCATE applies only to deleting the entire table, and TRUNCATE is DDL rather than DML)
(10) Try to use COMMIT as much as possible:
As long as possible, use COMMIT as much as possible in the program, so that the program's performance is improved, and the demand will also be reduced by the resources released by COMMIT:
Resources released by COMMIT:
A. Information used to restore data on the rollback segment.
B. Locks obtained by Program Statements
C. Space in redo log buffer
D. ORACLE manages the internal costs of the above three types of resources
(11) replace HAVING clause with the Where clause:
Avoid using the HAVING clause. HAVING filters the result set only after all records are retrieved. this process requires sorting, total, and other operations. if the WHERE clause can be used to limit the number of records, this overhead can be reduced. (in non-oracle) where on, where, and having can be added, on is the first statement to execute, where is the second clause, and having is the last clause, because on filters out records that do not meet the conditions before making statistics, it can reduce the data to be processed by intermediate operations. It is reasonable to say that the speed is the fastest, where should also be faster than having, because it performs sum only after filtering data, and on is used only when two tables are joined, so in a table, then we can compare where with having. In the case of single-Table query statistics, if the filter condition does not involve fields to be calculated, the results will be the same, but the where technology can be used, having cannot be used. In terms of speed, the latter must be slow. If a calculated field is involved, the value of this field is uncertain before calculation, according to the workflow written in the previous article, the where function time is completed before computing, and having works only after computing. In this case, the results are different. In multi-table join queries, on takes effect earlier than where. The system first combines multiple tables into a temporary table based on the join conditions between tables, then filters them by where, then computes them, and then filters them by having after calculation. It can be seen that to filter a condition to play a correct role, you must first understand when the condition should take effect, and then decide to put it there.
(12) Reduce table queries:
In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table. Example:
SELECT TAB_NAME from tables where (TAB_NAME, DB_VER) = (SELECT
TAB_NAME, DB_VER FROM TAB_COLUMNS where version = 604)
(13) Improve SQL efficiency through internal functions:
Complex SQL statements tend to sacrifice execution efficiency. It is very meaningful to grasp the above methods to solve problems by using functions.
(14) use the table Alias (Alias ):
When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each Column. in this way, the parsing time can be reduced and the syntax errors caused by Column ambiguity can be reduced.
(15) Replace IN with EXISTS and not exists instead of not in:
In many basic table-based queries, to meet one condition, you often need to join another table. in this case, using EXISTS (or not exists) usually improves the query efficiency. IN a subquery, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). to avoid the use of not in, we can rewrite it into an Outer join (Outer Joins) or not exists.
Example:
(Efficient) SELECT * from emp (basic table) where empno> 0 and exists (SELECT 'x' from dept where dept. DEPTNO = EMP. deptno and loc = 'melb ')
(Inefficient) SELECT * from emp (basic table) where empno> 0 and deptno in (select deptno from dept where loc = 'melb ')
(16) Identifying 'inefficient execution' SQL statements:
Although a variety of graphical tools for SQL optimization are emerging, writing your own SQL tools is always the best way to solve the problem:
Select executions, DISK_READS, BUFFER_GETS,
ROUND (BUFFER_GETS-DISK_READS)/BUFFER_GETS, 2) Hit_radio,
ROUND (DISK_READS/EXECUTIONS, 2) Reads_per_run,
SQL _TEXT
From v $ SQLAREA
Where executions> 0
AND BUFFER_GETS> 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS <0.8
Order by 4 DESC;
(17) using indexes to improve efficiency:
An index is a conceptual part of a table to improve data retrieval efficiency. ORACLE uses a complex self-balancing B-tree structure. data Query by index is usually faster than full table scan. when ORACLE finds the optimal path for executing the query and Update statements, the ORACLE optimizer uses the index. using indexes when joining multiple tables can also improve efficiency. another advantage of using an index is that it provides uniqueness verification for the primary key .. For those LONG or long raw data types, You Can index almost all columns. generally, using indexes in large tables is particularly effective. of course, you will also find that using indexes to scan small tables can also improve efficiency. although the index can improve the query efficiency, we must pay attention to its cost. the index requires space for storage and regular maintenance. The index itself is also modified whenever a record is increased or decreased in the table or the index column is modified. this means that the INSERT, DELETE, and UPDATE operations for each record will pay four or five more disk I/O. because indexes require additional storage space and processing, unnecessary indexes will slow the query response time .. Regular index reconstruction is necessary .:
Alter index <INDEXNAME> REBUILD <TABLESPACENAME>
18) replace DISTINCT with EXISTS:
When you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using DISTINCT in the SELECT clause. in general, you can consider replacing it with EXIST, and EXISTS makes the query more rapid, because the RDBMS core module will return the result immediately after the subquery conditions are met. example:
(Inefficient ):
Select distinct DEPT_NO, DEPT_NAME from dept d, EMP E
Where d. DEPT_NO = E. DEPT_NO
(Efficient ):
SELECT DEPT_NO, DEPT_NAME from dept d where exists (SELECT 'x'
From emp e where e. DEPT_NO = D. DEPT_NO );
(19) SQL statements in uppercase:
Because oracle always parses SQL statements first, converts lowercase letters into uppercase letters, and then executes
(20) try to use the connector "+" to connect strings in java code!
(21) Avoid using NOT in the index column:
We should avoid using NOT in the index column, NOT will have the same impact as using the function in the index column. when ORACLE Encounters "NOT", it stops using indexes and performs full table scanning.
(22) Avoid using computation on index columns.
In the WHERE clause, if the index column is part of the function, the optimizer will use full table scan without using the index.
Example:
Inefficiency:
SELECT... From dept where sal * 12> 25000;
Efficient:
SELECT... From dept where sal> 25000/12;
(23) Replace with> =>
Efficient:
SELECT * from emp where deptno> = 4
Inefficiency:
SELECT * from emp where deptno> 3
The difference between the two lies in that the former DBMS will jump directly to the first record whose DEPT is equal to 4, while the latter will first locate the record whose DEPTNO is = 3 and scan forward to the record whose first DEPT is greater than 3.
(24) replace OR with UNION (applicable to index columns)
In general, replacing OR in the WHERE clause with UNION will produce better results. using OR for index columns will scan the entire table. note that the preceding rules are only valid for multiple index columns. if a column is not indexed, the query efficiency may be reduced because you did not select OR. in the following example, both LOC_ID and REGION have indexes.
Efficient:
SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
Where region = "MELBOURNE"
Inefficiency:
SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE LOC_ID = 10 or region = "MELBOURNE"
If you insist on using OR, you need to write the index columns with the least records at the beginning.
(25) use IN to replace OR
This is a simple and easy-to-remember rule, but the actual execution results must be tested. in ORACLE8i, the execution paths of the two seem to be the same.
Inefficiency:
SELECT .... From location where LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
Efficient
SELECT... From location where LOC_IN IN (10, 20, 30 );
(26) Avoid using is null and is not null in the index column.
To avoid using any columns that can be empty in the index, ORACLE will not be able to use this index. this record does not exist in the index if the column contains a null value. for a composite index, if each column is empty, this record does not exist in the index. if at least one column is not empty, the record is stored in the index. for example, if the unique index is created in column A and column B of the table, and the and B values of A record exist in the table are (123, null ), ORACLE will not accept the next record with the same A, B value (123, null) (insert ). however, if all index columns are empty, ORACLE considers the entire key value to be null, but null is not equal to null. therefore, you can insert 1000 records with the same key value. Of course, they are empty! Because the null value does not exist in the index column, the Null Value Comparison of the index column in The WHERE clause will disable ORACLE.
Inefficiency: (index failure)
SELECT... From department where DEPT_CODE is not null;
Efficient: (index valid)
SELECT... From department where DEPT_CODE> = 0;
(27) always use the first column of the index:
If an index is created on multiple columns, the optimizer selects this index only when its first column (leading column) is referenced by the where clause. this is also a simple and important rule. When only the second column of the index is referenced, the optimizer uses the full table scan and ignores the index.
28) replace UNION with UNION-ALL (if possible ):
When an SQL statement needs to UNION two query result sets, these two result sets are merged in the form of UNION-ALL and sorted before the final result is output. if union all is used to replace UNION, sorting is unnecessary. the efficiency will be improved accordingly. note that union all will repeatedly output the same records in the two result sets. therefore, you still need to analyze the feasibility of using union all from the business needs. UNION sorts the result set. This operation uses SORT_AREA_SIZE memory. this memory optimization is also very important. the following SQL can be used to query the consumption of sorting
Inefficiency:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
Efficient:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
(29) replace order by with WHERE:
The order by clause only uses indexes under two strict conditions.
All columns in order by must be included in the same index and maintained in the ORDER of the index.
All columns in order by must be defined as non-empty.
The index used BY the WHERE clause and the index used in the order by clause cannot be tied together.
For example:
The DEPT table contains the following columns:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
Inefficiency: (indexes are not used)
SELECT DEPT_CODE from dept order by DEPT_TYPE
Efficiency: (using indexes)
SELECT DEPT_CODE from dept where DEPT_TYPE> 0
(30) Avoid changing the index column type:
ORACLE automatically converts columns to different types of data.
Assume that EMPNO is a numeric index column.
SELECT... From emp where empno = '20140901'
In fact, after ORACLE type conversion, the statement is converted:
SELECT... From emp where empno = TO_NUMBER ('123 ')
Fortunately, the type conversion does not occur on the index column, and the purpose of the index is not changed.
Assume that EMP_TYPE is a character-type index column.
SELECT... From emp where EMP_TYPE = 123
This statement is converted:
SELECT... From emp WHERETO_NUMBER (EMP_TYPE) = 123
This index will not be used because of internal type conversion! To avoid implicit type conversion for your SQL statements, it is best to explicitly convert the type conversion. Note that when comparing the character and value, ORACLE will first convert the value type to the character type.
(31) WHERE clause to be careful:
The WHERE clause in some SELECT statements does not use indexes. Here are some examples.
In the following example,
(1 )'! = 'No index will be used. Remember, indexes only tell you what exists in the table, but not what does not exist in the table.
(2) '|' is a character concatenation function. indexes are disabled just like other functions.
(3) '+' is a mathematical function. Like other mathematical functions, indexes are disabled.
(4) The same index Columns cannot be compared with each other, which enables full table scan.

(32)
A. If the retrieved data volume exceeds 30% of the records in the table, using indexes will not significantly improve the efficiency.
B. in certain cases, using indexes may be slower than full table scanning, but this is an order of magnitude difference. in general, using an index is several times or even several thousand times more than a full table scan!
(33) Avoid resource-consuming operations:
SQL statements with DISTINCT, UNION, MINUS, INTERSECT, and order by will start the SQL engine.
Execute the resource-consuming sorting (SORT) function. DISTINCT requires a sorting operation, while other operations require at least two sorting operations. generally, SQL statements with UNION, MINUS, and INTERSECT can be rewritten in other ways. if your database's SORT_AREA_SIZE is well configured, you can also consider using UNION, MINUS, and INTERSECT. After all, they are highly readable.
(34) Optimize group:
To improve the efficiency of the group by statement, you can filter out unnecessary records before group by. The following two queries return the same results, but the second query is much faster.
Inefficiency:
Select job, AVG (SAL)
FROM EMP
GROUP by JOB
Having job = 'President'
Or job = 'manager'
Efficient:
Select job, AVG (SAL)
FROM EMP
Where job = 'President'
Or job = 'manager'
GROUP by JOB

 

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4672023.aspx