Manipulate NULL column values for fast sql performance


By default, NULL values means “not present”. Therefore, Oracle indexes will not include NULL values.

If you wonder about how slow your sql query, you must check whether your sql include where condition that use IS NULL.

When you use IS NOT NULL, your sql run normally. But if where condition include IS Null, sql performance slow down,espesially when there are join table condition.
So, i try to change it with use of index that change NULL values to ‘NULL’ string values.

for example, usually we use this,
create index ename_idx on emp (ename);

select * from emp where ename is null;

so, we can change it, this way
create index ename_idx on emp (nvl(ename,’NULL’));

select * from emp where nvl(ename,’NULL’) = ‘NULL’;

——————————————————————————————-
then as conclusion, it can said as
1. IS NOT NULL in WHERE clause use an index
2. IS NULL in WHERE clause doesn’t use an index

the solution is create an index with manipulate column with NULL values to another values
(string values or number values or any type of data).

^_^

One Response

  1. Hi
    Nice site!

    Bye

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: