B-tree and nulls

Today while I was scrolling my “WordPress reader” page I saw a post which reminds me to something I had fun before /and find it useful in many situations/:


SQL> create table xnull(a number,b number not null);

Table created.

SQL> create index xnull_idx on xnull(a,b);

Index created.

SQL> insert into xnull values(null,1);

1 row created.

SQL> insert into xnull values(null,1);

1 row created.

SQL> insert into xnull values(null,1);

1 row created.

SQL> insert into xnull values(null,1);

1 row created.

SQL> insert into xnull values(null,1);

1 row created.

SQL> insert into xnull values(1,1);

1 row created.

SQL> insert into xnull values(1,1);

1 row created.

SQL> insert into xnull values(1,1);

1 row created.

SQL> insert into xnull values(1,1);

1 row created.

SQL> insert into xnull values(1,1);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('sys','xnull');

PL/SQL procedure successfully completed.

SQL> select a from xnull where a is null;

         A
----------







Execution Plan
----------------------------------------------------------
Plan hash value: 1345802251

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     5 |    10 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| XNULL_IDX |     5 |    10 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("A" IS NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        584  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> select * from xnull where a is null;

         A          B
---------- ----------
                    1
                    1
                    1
                    1
                    1


Execution Plan
----------------------------------------------------------
Plan hash value: 1345802251

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     5 |    25 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| XNULL_IDX |     5 |    25 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("A" IS NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        653  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL>


So if we have an index based on null column followed by not null column we can use it to filter the rows with null value.

Update:
Instead of not null column we use constant as well: create index nulls on t(a,1);
This can be good for saving storage, but the additional column in the index can be used for filtering as well.

Kovachev

Advertisements

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