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

缺乏、安全感 2022-09-02 03:51 126阅读 0赞

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]