Oracle Database Performance Tuning

One: note the connection order in the WHERE clause:
Oracle uses a bottom-up sequential parsing where clause, according to which the connection between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.
Especially "PRIMARY key id=?" "Such a condition.

Second: Avoid using ' * ' in the SELECT clause:
During the parsing process, Oracle translates ' * ' into all column names, which is done by querying the data dictionary, which means more time is spent.

Simply put, the shorter the statement execution time, the better (especially for the end user of the system). And for query statements, because the full table scan read more data, especially for large tables not only slow query speed, but also a large pressure on the disk IO, usually to avoid, and the way to avoid is usually used indexed index.

Three: The advantages and costs of using indexes.
Advantage:
1) The index is a conceptual part of the table used to improve the efficiency of retrieving data, and Oracle uses a complex self-balancing b-tree structure. In general, querying data through an index is faster than a full table scan. When Oracle finds the best path to execute queries and UPDATE statements, the Oracle Optimizer uses the index. Also, using indexes when joining multiple tables can improve efficiency.
2) Another advantage of using an index is that it provides the uniqueness of the primary key (primary key) Validation: Those long or long raw data types, you can index almost all the columns. In general, using indexes in large tables is particularly effective. Of course, you will also find that using indexes can also improve efficiency when scanning small tables.
Cost: Although the use of indexes can improve query efficiency, we must also pay attention to its cost. Indexes require space to store, and they need to be maintained regularly, and the index itself is modified whenever a record is added to a table or the index column is modified. This means that each record's insert, DELETE, and update will pay more than 4, 5 disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time. and the larger the table, the more serious the impact.

The places to be aware of using indexes:

1. Avoid using not on the index column,
We want to avoid using not on indexed columns, and not to have the same effect as using functions on indexed columns. When Oracle "encounters" not, he stops using the index instead of performing a full-table scan.

2. Avoid using calculations on indexed columns.
Where clause, if the index column is part of a function. The optimizer will use a full table scan without using an index. Example:

The code is as follows:
Inefficient: SELECT ... From DEPT WHERE SAL * > 25000;
Efficient: SELECT ... From DEPT WHERE SAL > 25000/12;

3. Avoid using is null and is not NULL on index columns
To avoid using any nullable columns in the index, Oracle performance will not work with that index. For single-column indexes, this record will not exist in the index if the column contains null values.　For composite indexes, if each column is empty, the same record does not exist in the index. If at least one column is not empty, the record exists in the index. For example, if a uniqueness index is established on column A and column B of a table, and the table has a value of a, a and a record of (123,null), Oracle will not accept the next record (insert) with the same A, B value (123,null). However, if all the index columns are empty, Oracle will assume that the entire key value is empty and null is not equal to NULL. So you can insert 1000 records with the same key value, of course they are empty! Because null values do not exist in the index column, a null comparison of indexed columns in the WHERE clause causes Oracle to deactivate the index.

The code is as follows:
Inefficient: (index invalidation) SELECT ... From DEPARTMENT WHERE dept_code are not NULL;
Efficient: (index valid) SELECT ... From DEPARTMENT WHERE Dept_code >=0;

4. Note the effect of the wildcard%
Oracle may deactivate the index if you use a wildcard character. Such as:

The code is as follows:
SELECT ... From DEPARTMENT WHERE dept_code like '%123456% ' (invalid).
SELECT ... From DEPARTMENT WHERE dept_code = ' 123456 ' (active)

5. Avoid changing the type of indexed columns:
Oracle automatically makes simple type conversions to columns when comparing data of different data types.
Suppose Empno is an indexed column of a numeric type. SELECT ... From EMP WHERE EMPNO = ' 123 ' Actually, after the Oracle type conversion, the statement is converted to: SELECT ... From EMP where EMPNO = To_number (' 123 ') Fortunately, the type conversion did not occur on the index column, and the use of the index was not changed. Now, suppose Emp_type is an indexed column of a character type. SELECT ... From EMP WHERE Emp_type = 123 This statement is converted by Oracle to: SELECT ... From EMP Whereto_number (emp_type) =123 This index will not be used because of the type conversions that occur internally! To avoid the implicit type conversion of your SQL by Oracle, it is best to explicitly express the type conversions. Note When comparing characters to numbers, Oracle takes precedence over numeric types to character types

6, the index of some "temper"
A. If the number of records in a table that has more than 30% data is retrieved. Using indexes will have no significant efficiency gains.
B. In certain situations, using an index may be slower than a full table scan, but this is the same order of magnitude difference. In general, the use of indexes than the full table scan to block several times or even thousands of times!

In addition to using indexes, there are other ways to reduce resource consumption:

1. Replace distinct with exists:
Avoid using DISTINCT in the SELECT clause when submitting a query that contains one-to-many table information, such as a departmental table and an employee table. It is generally possible to consider replacing with exist, EXISTS makes the query faster because the RDBMS core module will return the results immediately after the conditions of the subquery have been 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 (for indexed columns) with (Union) Union All
In general, replacing or in a WHERE clause with Union will have a good effect. Using or on an indexed column causes a full table scan.
Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, the query efficiency may be reduced because you did not select or. In the following example, indexes are built on both loc_id and region.
If you persist in using or, you need to return the least logged index column to the front.

The code is as follows:
Efficient: SELECT loc_id, Loc_desc, region from location WHERE loc_id = Ten 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 = ten OR region = "MELBOURNE"

3. Replace union with Union-all (if possible):
When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner and then sorted before the final result is output. If you use UNION ALL instead of union, this sort is not necessary. Efficiency will therefore be improved. It is important to note that the UNION all will output the same record in the two result set repeatedly. So you still have to analyze the feasibility of using union all from the business requirements. The UNION will sort the result set, which will use the memory of the sort_area_size. The optimization of this memory is also very important.
4. The Order by statement is added to the index column, preferably the primary key PK.

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 using resource-intensive operations:
SQL statements with distinct,union,minus,intersect start the SQL engine to perform the resource-intensive sorting (sort) function. Distinct requires a sort operation, while the others need to perform at least two sorting. Typically, SQL statements with union, minus, and intersect can be overridden in other ways. If your database is well-sort_area_size, using union, minus, intersect can also be considered, after all, they are very readable

6. Use where to replace having (if possible)
Optimize GROUP BY:
Increase the efficiency of the group BY statement by filtering out unwanted records before group by. The following two queries return the same result but the second one is significantly faster.

The code is as follows:
Low efficiency:
SELECT JOB, AVG (SAL)
From EMP GROUP job has 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, usually, if the statement can avoid the use of subqueries, as far as possible without sub-query.

Because the overhead of subqueries is quite expensive. Specific examples are in the following case "an optimization process for SQL".

Oracle Database Performance Tuning