Oracle SQL Performance Optimization Series (10)

zhaozj2021-02-16  54

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.

转载请注明原文地址:https://www.9cbs.com/read-25329.html

New Post(0)