Oracle Query Tuning – Example 3

In this query tuning example we will see how to index table columns null value to improve the performance of query. Many developers writes queries which has filter condition as NULL i.e. they are quering the tables with something IS NULL. We will also see how oracle executes with such type of queries. For our demonstration I have created EMPLOYEE table which has structure as given below.

SQL> desc employee
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(40)
 CITY                                               VARCHAR2(40)
 PROJECT                                            VARCHAR2(40)

Related Articles:

This employee table contains the data for all the employees of an organization including any project is allocated to them or not. Let’s check the number of employees allocated to each project.

select count(1),project from employee group by project order by 2;

  COUNT(1) PROJECT
---------- --------------
      3355 1
      3284 2
      3410 3
      3272 4
      3451 5
      3282 6
      3252 7
      3339 8
      3355 9
      1000

10 rows selected.

The above output shows that there are 1000 employees who are not working on any project. I am creating an index on Project column.

create index idx_project on employee(project);

Index created.

Let’s check how oracle executes queries where PROJECT is not allocated to employees.

SQL> select * from employee where project is null;

1000 rows selected.

Elapsed: 00:00:00.10

Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  1000 | 68000 |   102   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE |  1000 | 68000 |   102   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROJECT" IS NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        427  consistent gets
        357  physical reads
          0  redo size
      84274  bytes sent via SQL*Net to client
       1277  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

The above query returned 1000 rows. Execution plan shows that optimizer chooses to use FULL TABLE SCAN for Employee table even there is an index present for PROJECT column. Now it’s time to know why oracle is not used an index here.

Why Oracle has not used an index?

By Default, RDBMS databases ignores the NULL Values. In simple words NULL means nothing is present. Because of this, optimizer has not used an index in above example. Now let’s see how to deal with such null values.

Solution:

We need to recreate the above index as a functional index to which we are going to add some default value. We can add any default value. I prefer 1 default value.

drop index idx_project;

Index dropped.

Elapsed: 00:00:00.01
create index idx_project on employee(project,1);

Index created.

Elapsed: 00:00:00.04

Let’s execute the same query again and check the execution plan for it.

SQL> select * from employee where project is null;

1000 rows selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 3094398651

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |  1000 | 68000 |    93   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEE    |  1000 | 68000 |    93   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_PROJECT |  1000 |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PROJECT" IS NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        147  consistent gets
          6  physical reads
          0  redo size
      84274  bytes sent via SQL*Net to client
       1277  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

This time optimizer uses the INDEX RANGE SCAN instead of FULL TABLE SCAN. Also note the difference between the consistent gets and physical reads in both the cases.

Conclusion:

By default oracle does not index NULL values for table columns. We need to create functional index by adding default value to it as shows in above example.

Query Tuning Related Articles:

Oracle Query Tuning – Example 3

Leave a Reply