Oracle-017:sql optimization-is NOT null and NVL

Today optimizing a piece of SQL, the original script is roughly as follows:

Select a. field n from Tab_a awherea. Field 2 is not null;

A. Field 2 is indexed, but the query is very slow.

So the following changes were made:

Select a. field N from Tab_a awherenvl (A. Field 2, ' 0 ')! = ' 0 ';

The speed increase is obvious.

What is the reason? In fact, it is very simple, because is null and is not NULL invalidates the index of the field.

Although it is known which situations will invalidate the index, it is sometimes difficult to consider the impact of the business requirements, so the SQL optimization should be done at all times. Efforts to improve the efficiency of SQL execution.

Oracle-017:sql optimization-is NOT null and NVL