ORACLE SQL Performance Optimization Series (10)

oracle| Performance | optimization
31. Force Index Invalidation





If two or more indexes have the same level, you can force the Oracle optimizer to use one of them (through which the number of records retrieved is low).



Example:



SELECT ename

From EMP

WHERE EMPNO = 7935

The index on DEPTNO + 0 =/*deptno will fail.

and Emp_type | | The index on the ' = ' a '/*emp_type will fail.



This is a fairly straightforward way to improve query efficiency. But you have to be careful about this strategy, and in general you can only use it if you want to optimize a few SQL alone.



Here's an example of when to adopt this strategy,



Suppose there is a Emp_type index on the EMP table and there is no index on the emp_class.



SELECT ename

From EMP

WHERE emp_type = ' A '

and Emp_class = ' X ';



The optimizer notices the index on the Emp_type and uses it. This is the only option at the moment. If, after a period of time, another non uniqueness is established on the Emp_class, the optimizer must select two indexes, and in general, the optimizer will use two indexes and perform sorting and merging on their result sets. However, if one of the indexes (Emp_type) is close to uniqueness and the other index (EMP_CLASS) has thousands of duplicate values. Sorting and merging can be an unnecessary burden. In this case, you want the optimizer to block out the Emp_class index.

Use the following scenario to solve the problem.

SELECT ename

From EMP

WHERE emp_type = ' A '

and emp_class| | ' = ' X ';



32. Avoid using 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:



Low efficiency:

SELECT ...

From DEPT

WHERE SAL * > 25000;



Efficient:

SELECT ...

From DEPT

WHERE SAL > 25000/12;



Translator by:

This is a very practical rule, please be sure to keep in mind



33. Automatically Select Index

If there are more than two (including two) indexes in the table, there is one uniqueness index and the other is not unique.

In this case, Oracle uses a uniqueness index to completely ignore the non-uniqueness index.



Example:

SELECT ename

From EMP

WHERE EMPNO = 2326

and DEPTNO = 20;



Here, only the index on the empno is unique, so the empno index will be used to retrieve the records.

TABLE ACCESS by ROWID on EMP

INDEX UNIQUE SCAN on Emp_no_idx



34. Avoid using not on indexed columns

In general, we want to avoid using not on indexed columns, and not on the same as using functions on indexed columns

Effect. When Oracle "encounters" not, he stops using the index instead of performing a full table scan.

Example:



Inefficient: (here, do not use the index)



SELECT ...

From DEPT

WHERE Dept_code not = 0;



Efficient: (here, using the index)



SELECT ...

From DEPT

WHERE dept_code > 0;



It should be noted that at some point the Oracle optimizer automatically converts not to the corresponding relational operator.

Not > to <=

Not >= to <

Not < to >=

Not <= to >





Translator by:

In this example, the author made some mistakes. Inefficient SQL in an example cannot be executed.

I have done some tests:



Sql> SELECT * from emp where not empno > 1;

No rows selected

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (by INDEX ROWID) of ' EMP '

2 1 INDEX (RANGE SCAN) of ' EMPNO ' (UNIQUE)



Sql> SELECT * from emp where empno <= 1;

No rows selected

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (by INDEX ROWID) of ' EMP '

2 1 INDEX (RANGE SCAN) of ' EMPNO ' (UNIQUE)



The efficiency of the two is exactly the same, perhaps in line with the author's view that "at some point, the Oracle optimizer will automatically convert not to a corresponding relational operator."



35. Replace > with >=



If there is an index on the Deptno,



Efficient:



SELECT *

From EMP

WHERE DEPTNO >=4



Low efficiency:



SELECT *

From EMP

WHERE DEPTNO >3



The difference is that the former DBMS will jump directly to the first dept equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first dept greater than 3 records.