ORACLE-017:SQL Optimization-is not null and nvl
Today, I am optimizing a section of sql. The original script is roughly as follows:
select a. field n from tab_a therea. field 2 is not null;
a. Field 2 adds index, but query speed is very slow,
The following changes were made:
select a. field n from tab_a awarenvl (a. field 2,'0')!= '0';
The speed increase was obvious.
What is the reason? It's simple, because is null and is not null invalidate the index of the field.
Although we all know which cases will make the index invalid, but sometimes inevitably affected by business requirements and consider not comprehensive enough, so sql optimization should be carried out at any time, at any time. Efforts to improve sql execution efficiency.
