Oracle performance optimization operation 5: Remove & quot; & lt; & gt; & quot; whenever possible, oracle Performance Optimization

Oracle performance optimization operation 5: Remove "<>", oracle performance optimization whenever possible

Oracle performance optimization operation 5: Remove "<>" whenever possible"

Remove "<>" whenever possible to avoid full table scan. If the data is an enumeration value and the value range is fixed, change it to "OR.

Example:

<Span style = "font-size: 18px;"> update serviceinfo set state = 0 where state <> 0; </span>

Because the preceding statement contains "<>", full table access full is used in the execution plan, and the index on the state field is not used.

In practice, due to the limitation of business logic, the Field state is an enumerated value, which can only be 0, 1 or 2, and the value is equal to or less than 1 or 2.

Therefore, you can remove "<>" and use indexes to improve efficiency.

To:

<Span style = "font-size: 18px;"> update serviceinfo set state = 0 where state = 1 or state = 2; </span>

For further modifications, see oracle performance optimization operation 4 ".