Principle One: Note the order of joins in the WHERE clause:
Oracle parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions, and the conditions 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.
Principle two: Avoid the use of ' * ' in the SELECT clause:
Oracle converts ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time will be spent.
Simply put, the less time the statement executes, the better (especially for the system's end users). For query statements, because the full table scan read more data, especially for large tables not only slow query speed, but also on disk IO caused great pressure, usually to avoid, and the way to avoid is usually the use of indexed index.
Advantages and costs of using indexes.
Advantage:
1 The index is a conceptual part of the table, which is used to improve the efficiency of retrieving data, and Oracle uses a complex b-tree structure of self balance. In general, querying data through an index is faster than full table scans. The Oracle optimizer uses indexes when Oracle finds the best path to execute queries and UPDATE statements. It also increases efficiency when you use indexes to join multiple tables.
2 Another advantage of using the index is that it provides uniqueness validation for the primary key (primary key). Those long or long raw data types, you can index almost all columns. In general, using indexes in large tables is particularly effective. Of course, you'll also find that using indexes can also improve efficiency when scanning small tables.
Cost: Although using indexes can improve query efficiency, we must also pay attention to its costs. Indexes require space for storage and regular maintenance, and the index itself is modified whenever a record is added or subtracted from the table or the index column is modified. This means that each record's insert, DELETE, and update will pay 4, 5 more disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow down query response times. and the larger the table, the more serious the impact.
Where to use the index:
1, to avoid using not on the index column,
We want to avoid using not on indexed columns, not 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 the use of calculations on indexed columns.
In the WHERE clause, if the indexed column is part of the function. The optimizer uses a full table scan without indexing. Example:
Copy Code code as follows:
Low efficiency: SELECT ... From DEPT WHERE SAL * > 25000;
Efficient: SELECT ... From DEPT WHERE SAL > 25000/12;
3. Avoid using is null and is not NULL on indexed columns
Avoid using any nullable columns in the index, which will not be available on Oracle performance. For Single-column indexes, this record will not exist in the index if the column contains a null value. For composite indexes, if each column is empty, the record also does not exist in the index. If at least one column is not empty, the record exists in the index. For example: If the uniqueness index is based on the columns A and B of the table, and the a,b value of a record exists in the table (123,null), Oracle will not accept the next record (insert) with the same a,b value (123,null). However, if all the indexed columns are empty, Oracle will assume that the entire key value is empty and empty is not equal to NULL. So you can insert 1000 records with the same key value, of course they're all empty! Because a null value does not exist in an indexed column, a null comparison of an indexed column in a WHERE clause causes Oracle to deactivate the index.
Copy Code code as follows:
Inefficient: (index invalidated) SELECT ... From DEPARTMENT WHERE dept_code are not NULL;
Efficient: (index valid) SELECT ... From DEPARTMENT WHERE Dept_code >=0;
4, pay attention to the effect of the wildcard character%
Oracle may deactivate the index if you use a wildcard character. Such as:
Copy Code code as follows:
SELECT ... From DEPARTMENT WHERE dept_code like '%123456% ' (invalid).
SELECT ... From DEPARTMENT WHERE dept_code = ' 123456 ' (valid)
5. Avoid changing the type of the indexed column:
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 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 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 an internally occurring type conversion! To avoid an implicit type conversion of Oracle to your SQL, it is a good idea to explicitly display the type conversion. Note that when characters and numeric comparisons are compared, Oracle converts numeric types to character types preferentially
6, index of some "temper"
A. The number of records in a table that retrieves more than 30% of the data. Using indexes will not improve significantly.
B. In certain situations, using an index may be slower than full table scans, but this is the same order of magnitude difference. In general, using an index is a few times or even thousands of times times more than a full table scan!
In addition to using indexes, we have other ways to reduce resource consumption:
1. Replace distinct with exists:
Avoid using DISTINCT in the SELECT clause when submitting a query that contains a one-to-many table of information, such as a department table and an employee table. It is generally possible to consider replacing with exist, EXISTS make the query faster, because the RDBMS core module returns the result immediately after the condition of the subquery is satisfied.
Example:
Copy Code code 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 indexed columns)
In general, replacing or in a WHERE clause with union would have a better effect. Using or for 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, query efficiency may be reduced because you have not selected an OR. In the following example, indexes are built on both loc_id and region.
If you insist on using or, you need to return the least recorded index column to the front.
Copy Code code as follows:
High efficiency: SELECT loc_id, Loc_desc, REGION from LOCATION WHERE loc_id = UNION All
SELECT loc_id, Loc_desc, REGION from LOCATION WHERE REGION = "MELBOURNE"
Inefficiencies: 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 outputting the final result. If you use UNION ALL instead of union, this sort is not necessary. Efficiency will be improved accordingly. It should be noted that UNION all outputs the same record in the two result sets repeatedly. So you still need to analyze the feasibility of using union all from the business requirements analysis. The UNION will sort the result set, which will use the memory of the Sort_area_size block. The optimization of this block of memory is also very important.
4, the order BY statement added in the index column, preferably the primary key PK.
Copy Code code as follows:
SELECT Dept_code from DEPT order by Dept_type (inefficient)
SELECT Dept_code from DEPT Order by Dept_code (efficient)
5, to avoid the use of resource-consuming operations:
SQL statements with distinct,union,minus,intersect start the SQL engine to perform a resource-intensive sort (sort) function. Distinct requires a sort operation, while the other requires at least two times to perform the sort. Typically, SQL statements with union, minus, and intersect can be overridden in other ways. If your database sort_area_size well, use union, minus, intersect can also be considered, after all, their readability is very strong
6. Use where to replace having (if applicable)
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 obviously much faster.
Copy Code code as follows:
Low efficiency:
SELECT JOB, AVG (SAL)
From EMP GROUP job have 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 speaking, if the statement can avoid the use of subqueries, as far as possible without subqueries. Because the cost of a subquery is quite expensive. The concrete example is in the following case "an optimization process for SQL".
If you have any suggestions for Oracle performance improvements, you can talk to the forums on the Web site.
