oraclesql性能优化OracleSQL性能优化.doc

oracle sql性能优化（Oracle SQL性能优化）
Oracle SQL性能优化
用索引提高效率：
索引是表的一个概念部分，用来提高检索数据的效率，Oracle使用 了一个复杂的自平衡树结构。通常，通过索引查询数据比全表扫描e index. For example: if the only index based on the A and B columns of the table, and there is a record in the A table, B value (123, null), ORACLE will not be accepted with the same A under a B value (123, null) records (insert). However, if the indexed column all is empty, ORACLE will think the key is empty and empty is not empty. So you can insert 1000 records with the same key value, of course they are empty!
Because the null value does not exist in the columns in the index, so the WHERE clause of the index column Performing a null comparison will cause ORACLE to deactivate the index
Inefficient: (index invalid)
SELECT. . . FROM, DEPARTMENT, WHERE, DEPT_CODE, IS, NOT, NULL;
Efficient: (index valid)
SELECT. . . FROM, DEPARTMENT, WHERE, DEPT_CODE, >=0;
always use the first column of the index:
If the index is based on multiple columns, only in the first column of it (leading column) by the where clause references, the optimizer will choose to use the index. This is a simple but very important rule, when only the reference index of second columns, the optimizer uses a full table scan and ignore the index
replace UNION with UNION-ALL (if possible):
When the SQL statement requires UNION two query result set, the two set of results will be merged with