oracle和达梦数据库在索引上的一个区别

 Oracle索引是不存储null值的，达梦是存储了Null值，下面来做一个实验。

drop table test;
create table test(id number primary key,name varchar2(100), age number);
insert into test select rownum, 'aaaaaaaaaaa'||rownum, 
  CASE WHEN mod(rownum,10)  = 0 THEN null
  ELSE rownum END    from dual connect by level <1000000;
commit;
create index ind_t_age on test(age);
call dbms_stats.gather_table_stats(user,'TEST',cascade => true);

在Oracle里面，查询null值是用不到索引的。
SQL> select count(*) from test where age is null;
已用时间:  00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 1950795681
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |  1236   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST | 99999 |   488K|  1236   (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("AGE" IS NULL)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4493  consistent gets
          0  physical reads
          0  redo size
        361  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from test where age is not null;
已用时间:  00: 00: 00.05
执行计划
----------------------------------------------------------
Plan hash value: 1853573966
-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     5 |   550   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IND_T_AGE |   900K|  4394K|   550   (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("AGE" IS NOT NULL)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2023  consistent gets
          2  physical reads
          0  redo size
        359  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
达梦数据库索引是存储null值的，查询null值会用到索引。          
explain
select count(*) from test where age is null
1   #NSET2: [6, 1, 30] 
2     #PRJT2: [6, 1, 30]; exp_num(1), is_atom(FALSE) 
3       #AAGR2: [6, 1, 30]; grp_num(0), sfun_num(1) slave_empty(0)
4         #SSEK2: [6, 49529, 30]; scan_type(ASC), IND_T_AGE(TEST), scan_range[NULL,NULL]