31. Forced index
If two or more indexes have the same level, you can force the Oracle optimizer to use one of them (by it, the number of records) is less.
Example:
SELECT ENAME
From EMP
WHERE Empno = 7935
And deptno 0 = 10 / * Index on DePTNO will fail * /
And Emp_Type || '' = 'A' / * EMP_TYPE index will fail * /
This is a considerable way to improve query efficiency. But you have to carefully consider this strategy. In general, you can use it when you want to optimize several SQLs separately.
Here is an example about when this strategy is used.
Assume that there is no index on EMP_CLASS on the EMP_TYPE column of the EMP table.
SELECT ENAME
From EMP
WHERE EMP_TYPE = 'a'
AND EMP_CLASS = 'x';
The optimizer will notice the index on the EMP_TYPE and use it. This is the only choice. If, after a while, another uniqueness is built on EMP_CLASS, the optimizer must choose two indexes, in general The optimizer will use two indexes and perform sorting and mergers on their results collection. However, if one of the indexes (EMP_TYPE) is close to uniqueness and there are thousands of repetitions on the other index (EMP_CLASS). Sort And merge will become an unnecessary burden. In this case you want to make the optimizer shield the EMP_CLASS index.
Use the following solution to solve the problem.
SELECT ENAME
From EMP
WHERE EMP_TYPE = 'a'
AND EMP_CLASS || '' = 'x';
32. Avoid using calculations on the index column.
In the WHERE clause, if the index column is part of the function. The optimizer will not use the index to use a full table scan.
Example:
Inefficient:
SELECT ...
From dept
WHERE SAL * 12> 25000;
Efficient:
SELECT ...
From dept
WHERE SAL> 25000/12;
Translator presses:
This is a very practical rule, please keep in mind
33. Automatically select index
If there are more than two (including two) indexes in the table, there is a unique index, while others are unique.
In this case, Oracle will use a unique index to completely ignore the unique 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 records.
Table Access by RowID on EMP
Index Unique Scan on EMP_NO_IDX
34. Avoid using NOT on the index column
Typically, we must avoid using NOT on the index column, and Not will generate the same function as the function on the index column.
Impact. When Oracle "encounters" NOT, he stops using indexing to perform full table scans.
Example:
Inefficient: (here, not using the index)
SELECT ...
From dept
WHERE DEPT_CODE NOT = 0;
Efficient: (here, using index)
SELECT ...
From dept
WHERE DEPT_CODE> 0;
It should be noted that at some point, the Oracle optimizer automatically converts NOT into correspondence.
Not> to <=
Not> = TO <
NOT
NOT <= to> Translator Press:
In this example, the author has made some mistakes. The low efficiency SQL in the example cannot be performed.
I did 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, maybe this is the view that the author will automatically convert NOT into a corresponding relationship operator.
35. Use> = alternative>
If there is an index on deptno,
Efficient:
SELECT *
From EMP
WHERE Deptno> = 4
Inefficient:
SELECT *
From EMP
WHERE Deptno> 3
The difference between the two is that the former DBMS will jump directly to the record of the first DEPT equal to 4 and the latter will first position the record of DEPTNO = 3 and scanned to the first DEPT greater than 3 records.
