IS NULL Performance

[thiago.sousa]

Someone would know how to explain why the use of 'is null' in a SQL query diminishes the Bank's performance, making excessive use of the processor?

[dr_gori]

I may be mistaken, but that I know, the null does not enter the indexes. Type, Oracle only places in the Null values ??index. So when you seek the null, he has to do a full scan ...

Someone knows if my answer confers?
This is a good theme for discussion here! ...

[thiago.sousa]

I really had a lot of interest in knowing this ...
and how can we solve this?

[gilbertoca]

Maybe this link http://www.dba-oracle.com/oracle_tips_null_idx.htm can help. Another thing, I usually create my columns with a default value.

Gilberto

[thiago.sousa]

Yes ... I also create my columns always with default.
What I want to say is the following

Ex:

Select all

  select * from teste  
  where 
    (campo1 = :parametro1 or :parametro1 is null)

understood?

How do I optimize this?

[gilbertoca]

Select all

 
 select * from teste 
where 
(campo1 = :parametro1 or :parametro1 is null) 

This command is not wrong? It should not be:

Select all

 
nomedacouluna = :parametro 

I think no JDBC driver will recognize only this command: parameter. Do you want to set up a dynamic SQL passing the column that you need to use?

understood?

As you saw, no!

[dr_gori]

This command is not wrong?

This command is very common, when it has optional parameters.
Example:
All records of the EMP:

Select all

SQL> select * from emp; 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO 
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20 
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 
 
14 rows selected. 

Now, let's create a variable BIND.

Select all

SQL> var vjob varchar2(20) 
SQL> select * from emp where job = :vjob; 
 
no rows selected

Now, let's apply the method:
As VJOB is null, you have to bring everything from the table.

Select all

SQL> select * from emp where (job = :vjob or :vjob is null); 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO 
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20 
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 
 
14 rows selected.

Now, let's set a value for VJOB:

Select all

SQL> begin 
  2    :vjob :='SALESMAN'; 
  3  end; 
  4  / 
 
PL/SQL procedure successfully completed. 
 
SQL> select * from emp where (job = :vjob or :vjob is null); 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO 
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 
 
4 rows selected. 
 
SQL>  

Said?

I think to solve our friend's problem, we have to find out if his column has index if the bank statistics were recently collected, the other table relationships, etc.

I think a trace would help you know what Join is slow.

[thiago.sousa]

In fact, the problem is always with the is nulll .... if you do a test will check the difference that happens in the bank.
I'd like to know why? And how I would solve

[gilbertoca]

This command is very common, yes, when you have optional parameters.

You can use enough resource from the bank and cause a problem to make tunning. Well, it's the opinion of a Newbie !
For me in this command,

Select all

 
SQL> select * from emp where (job = :vjob or :vjob is null); 

You are not fully using the IS NULL option! Because? In my point of view this query will be translated to

Select all

 
SQL> select * from emp where (job = null or null is null); 
                                                  falso        verdadeiro 

that is, the same as

Select all

 
select * from emp where (job = null or 1=1); 
select * from emp where (1=1); 

resulting in a full table scan. And you will!

While

Select all

 
SQL> select * from emp where (job = :vjob or :vjob is null); 

shall be

Select all

 
SQL> select * from emp where (job = 'SALESMAN' or SALESMAN is null); 
                                                 verdadeiro               falso 

when Variabel is:

Select all

vjob := 'SALESMAN'.

Boy This business is really complicated! It's making me confused! I'll read and practice a little more.