SQL Tuning – Mix NULL / NOT NULL Values

One of the difficulty when writing a SQL query (static SQL) is to have in the same Where Clause different conditions handling Null Values and Not Null Values for a predica.

Let’s me explain you by an example :

Users can entered different values for a user field from an OBI report:
– If no value entered then all rows must be returned.
– If 1 value entered then only row(s) related to the filter must be returned.
– If List Of Values entered then only row(s) related to the filter must be returned.

The SQL we want to write must take into account all the conditions possible (the 3 listed above).

Here is the first version of the SQL query written by the customer :

select * 
from my_table a
WHERE a.pt_name LIKE decode(:PT_PARAM, NULL, '%', '')
OR a.pt_name IN (:PT_PARAM);

:PT_PARAM is the user variable.

The problem with this query is that the both conditions :
– a.pt_name LIKE decode(:PT_PARAM, NULL, ‘%’, ”)
– a.pt_name IN (:PT_PARAM)
are always TRUE, so unnecessary work will be done by oracle optimizer.

We can prove that by checking the execution plan :

If :PT_PARAM is equal to ‘Value1’ :

EXPLAIN PLAN FOR
select * 
from my_table a  
WHERE a.pt_name LIKE decode('Value1', NULL, '%', '')
OR a.pt_name IN ('Value1');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 1606647163
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     5 |  1140 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE         |     5 |  1140 |     3   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |                  |       |       |            |          |
|   3 |    BITMAP OR                        |                  |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE       | BIX_DMED_TERM_01 |       |       |            |          |
|   5 |     BITMAP MERGE                    |                  |       |       |            |          |
|*  6 |      BITMAP INDEX RANGE SCAN        | BIX_DMED_TERM_01 |       |       |            |          |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A"."PT_NAME"='Value1')
   6 - access("A"."PT_NAME" LIKE NULL)
       filter("A"."PT_NAME" LIKE NULL AND "A"."PT_NAME" LIKE NULL)

Oracle Optimizer does 2 access :
– 1 access for NULL value
– 1 access for ‘Value1’ value

The first access is not necessary since the user has selected a Not Null Value (‘Value1’). Indeed if the user select one Not Null value (‘Value1’), we don’t want oracle execute condition for NULL value.

To avoid this couple of access, it’s necessary to re-write the SQL statement like that :

select * 
from my_table a
where (:PT_PARAM is null AND a.pt_name like '%')
OR (:PT_PARAM IS NOT NULL AND a.pt_name in (:PT_PARAM));

We just add a SQL clause indicating that if the first condition is TRUE, the second condition is FALSE and vice versa:
if (:PT_PARAM is null AND a.pt_name like ‘%’) is TRUE then (:PT_PARAM IS NOT NULL AND a.pt_name in (:PT_PARAM)) is FALSE
if (:PT_PARAM IS NOT NULL AND a.pt_name in (:PT_PARAM)) is TRUE then (:PT_PARAM is null AND a.pt_name like ‘%’) is FALSE

Checking the execution plan related to the new SQL statement :

EXPLAIN PLAN FOR
select * 
from my_table a
where ('Value1' is null AND a.pt_name like '%')
OR ( 'Value1' IS NOT NULL AND a.pt_name in ('Value1'));

Plan hash value: 2444798625
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     5 |  1140 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE         |     5 |  1140 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |                  |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE        | BIX_DMED_TERM_01 |       |       |            |          |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."PT_NAME"='Value1')

Now only one access is done, the one related to the value ‘Value1’ selected by the user.

Conclusion:

Tuning a SQL query can be made within different way : modify the physical design for a table (indexes, partitioning), influence the optimizer (Hints) to force an execution plan, modify oracle optimizer database parameters.

But very often, SQL tuning can be made “simply” by re-written the SQL query. Most of the time, performance problem is due to bad written SQL statement.

The first advice before to write a SQL query is:
– always understand the business needs in order to avoid bad interpretations.
– avoid unnecessary step for oracle optimizer by checking oracle execution plan in details to control the path oracle choose to access the data.
– avoid writing complex SQL – SQL is a very simple language, don’t forget it.