Summary of oracle performance optimization suggestions

Users who usually pay attention to Oracle databases know that Oracle performance optimization ensures the robustness of Oracle databases. The following two principles need attention.

Users who usually pay attention to Oracle databases know that Oracle performance optimization ensures the robustness of Oracle databases. The following two principles need attention.

Principle 1: Pay attention to the connection sequence 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.
In particular, "primary key ID = ?" Such a condition.

Principle 2: Avoid '*' 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.

Simply put, the shorter the statement execution time, the better (especially for end users of the system ). For query statements, because the full table scan reads a large amount of data, especially for large tables, the query speed is not only slow, but also causes heavy pressure on disk IO, which should be avoided generally, instead, Index is usually used.

Advantages and costs of using indexes.
Advantages:
1) indexing 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.
2) Another advantage of using indexes 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.
Cost: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 .. The larger the table, the more serious the impact.

Notes for using indexes:

1. 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.

2. 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. For example:
The Code is as follows:
Inefficient: SELECT... From dept where sal * 12> 25000;
Efficient: SELECT... From dept where sal> 25000/12;

3. Avoid using is null and is not null in the index column.
Avoid using any columns that can be empty in the index. This index cannot be used in ORACLE performance. 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.

The Code is as follows:
Inefficient: (index failure) SELECT... From department where DEPT_CODE is not null;
Efficient: (index valid) SELECT... From department where DEPT_CODE> = 0;


4. Note the influence of wildcard %.
If a wildcard is used, Oracle may disable the index. For example:
The Code is as follows:
SELECT... From department where DEPT_CODE like '% 100' (invalid ).
SELECT... From department where DEPT_CODE = '20140901' (valid)

5. 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 = '000000' in fact, after ORACLE type conversion, the statement is converted to: 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 to: SELECT... From emp WHERETO_NUMBER (EMP_TYPE) = 123 because of internal type conversion, this index will not be used! 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.

6. Some "temper" of Indexes"
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!

In addition to using indexes, we also have other methods to reduce resource consumption:

1. 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:
The Code is as follows:
(Inefficient): select distinct DEPT_NO, DEPT_NAME from dept d, EMP E
Where d. DEPT_NO = E. DEPT_NO
And E. sex = man
(Efficient): SELECT DEPT_NO, DEPT_NAME FROM DEPT D
WHERE EXISTS
(SELECT 'x' from emp e where e. DEPT_NO = D. DEPT_NO
And E. sex = man
);

2. replace OR with (UNION) union all (applicable to index columns)
In general, replacing OR in the WHERE clause with UNION will produce better results. Using OR for the index column will cause a full table scan.
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.
If you insist on using OR, you need to write the index columns with the least records at the beginning.
The Code is as follows:
Efficient: SELECT LOC_ID, LOC_DESC, region from location where LOC_ID = 10 UNION ALL
SELECT LOC_ID, LOC_DESC, region from location where region = "MELBOURNE"
Inefficient: SELECT LOC_ID, LOC_DESC, region from location where LOC_ID = 10 or region = "MELBOURNE"

3. 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.
4. Add the Order By statement to the index column, preferably the primary key.
The Code is as follows:
SELECT DEPT_CODE from dept order by DEPT_TYPE (inefficient)
SELECT DEPT_CODE from dept order by DEPT_CODE (efficient)

5. Avoid resource-consuming operations:
SQL statements with DISTINCT, UNION, MINUS, and INTERSECT start the SQL engine to 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.

6. Use Where to replace Having (if possible)
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.
The Code is as follows:
Inefficiency:
Select job, AVG (SAL)
From emp group job having job = 'President 'and avg (SAL)> XXX
Efficient:
Select job, AVG (SAL)
FROM EMP
Where job = 'President'
Or job = 'manager' group job Having and avg (SAL)> XXX

7. Generally, if the statement can avoid the use of subqueries, try not to use subqueries.Because the sub-query overhead is quite expensive. The specific example is in the subsequent case "an SQL optimization process.
If you have any suggestions on Oracle performance improvement, visit the forum on the website.