Tuesday, 15 September 2015

SQL and null conditions

Today I found in my SQL package code one bug. I created function/procedure with a few input parameters and used them in simple sql query as condition. In some cases that input parameters are null. I used that parameters with = sign. However oracle interpret construction like:
some_column = input_val
even column and input_value are null as not fulfill condition.

In older oracle sql it is possible to use function decode(some_column, input_val, -1) = -1 but it is not performance
In newer oracle db you can create index like:

create index some_index_name for table_name (some_column, -1)

You have to look out for possible values in some_column.

No comments:

Post a Comment