Oracle query efficiency (III)

 Avoid using not in index ColumnsTo 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 example below, (1 )'! = 'No index is 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. as with other functions, indexes are disabled. (3) '+' is a mathematical function. as with 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 number of 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 job
Having job = 'President'
Or job = 'manager'
Efficient:
Select job, AVG (SAL)
From EMP
Where job = 'President'
Or job = 'manager'