为什么单列索引不能存储null值

gdpr-dba

看了这个帖子http://t.askmaclean.com/thread-936-1-1.html，突然有了这样的疑问，为什么单列索引不能存储null值，oracle在设计index的时候完全可以将null值当做无穷大，然后放在合适的叶块，这样查询有null值的情况也可以用到索引，但是oracle却没有这样做，想不明白为什么？

不了峰

见图

个人理解，如果是无穷大，根据建索引是按升序规则 ，那么这个null，一定是放在最右侧。

好吧，如果是放在最右侧，那么这时如果新增值 比原有的数据大时，也是会放在最右侧，那么这时候 算法就要考虑，如果存放这新的索引数据。也许就会产生大量的索引块的分裂，从而造成 level级别的上升……从而影响 增删改查的性能。

当然，不把null值 存在索引，一定是别的原因。

gdpr-dba

可能有这个原因，谢谢峰同学，刘老师也来发表下意见吧

Maclean Liu(刘相兵

single column b*tree index 不会存放NULL
composite  b*tree index  会存放NULL
bitmap index 会存放NULL


我的理解：
single column b*tree index不存放NULL， 不是出于技术上无法实现 ， 而是出于 设计理念和性能考量的目的。

实际上我们可以通过 加入常数字段的方法(create index ind_t1 on test(col,1)) 折中地使用 单字段的索引 存放 NULL 值。

下面的演示 希望说明的是：

1. 在index中存放NULL值 在技术上是可行的
2. 在普通的 b*tree index中NULL值 确实是被当做最大值处理 总是放在索引的右手边的





SQL> create table test (t1 int);

Table created.

SQL> begin
  2  for i in 1..99999 loop
  3  insert into test values(i);
  4  end loop;
  5  commit;
  6  end;
  7  /
  

SQL> create index ind_t1 on test(t1,1);

Index created.

  1  begin
  2  for i in 1..99 loop
  3  insert into test values(NULL);
  4  end loop;
  5  commit;
  6* end;

PL/SQL procedure successfully completed.



SQL> alter system checkpoint;

System altered.



SQL> exec dbms_stats.gather_table_stats('','TEST',cascade=>TRUE);

PL/SQL procedure successfully completed.



SQL> explain plan for select /*+index_ffs(test ind_t1)*/ t1 from test;

Explained.

SQL>  @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3622308455

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |   100K|   488K|    74   (2)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IND_T1 |   100K|   488K|    74   (2)| 00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.




SQL> SELECT object_id FROM dba_objects WHERE object_name ='IND_T1';

OBJECT_ID
----------
     81251

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 81251';

Session altered.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name
/s01/orabase/diag/rdbms/g11r23/G11R23/trace/G11R23_ora_13176.trc


----- begin tree dump
branch: 0x41e131 4317489 (0: nrow: 264, level: 1)
   leaf: 0x41e132 4317490 (-1: nrow: 404 rrow: 404)
   leaf: 0x41e133 4317491 (0: nrow: 399 rrow: 399)
   leaf: 0x41e134 4317492 (1: nrow: 399 rrow: 399)
   leaf: 0x41e135 4317493 (2: nrow: 399 rrow: 399)
   leaf: 0x41e136 4317494 (3: nrow: 399 rrow: 399)
   leaf: 0x41e137 4317495 (4: nrow: 399 rrow: 399)
   leaf: 0x41e138 4317496 (5: nrow: 399 rrow: 399)
   leaf: 0x41e139 4317497 (6: nrow: 399 rrow: 399)
   leaf: 0x41e13a 4317498 (7: nrow: 399 rrow: 399)
   leaf: 0x41e13b 4317499 (8: nrow: 399 rrow: 399)
   leaf: 0x41e13c 4317500 (9: nrow: 399 rrow: 399)
   。。。。。。。。。。。。。。。。。。。。。。。。。
   leaf: 0x41e309 4317961 (262: nrow: 153 rrow: 153)
   
   
   0x41e131 0000000001   0000011110000100110001=> 1 123185  这个是root block
   0x41e309 0000000001   0000011110001100001001   1 123657  这个是目前最右手边的leaf block存放了NULL值

SQL> select dump(99999,16) from dual;

DUMP(99999,16)
-----------------------
Typ=2 Len=4: c3,a,64,64  

SQL> alter system dump datafile 1 block 123657;

System altered.

row#52[7131] flag: ------, lock: 0, len=17
col 0; len 4; (4):  c3 0a 64 63
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 51   
row#53[7114] flag: ------, lock: 0, len=17
col 0; len 4; (4):  c3 0a 64 64
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 52  
row#54[7101] flag: ------, lock: 2, len=13
col 0; NULL
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 53
row#55[7088] flag: ------, lock: 2, len=13
col 0; NULL
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 54
row#152[5827] flag: ------, lock: 2, len=13
col 0; NULL
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 b5




SQL> insert into test values(99999);

1 row created.

SQL> insert into test values(1000000);

1 row created.

SQL> insert into test values(100000);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.


SQL> select dump(1000000,16) from dual;

DUMP(1000000,16)
-----------------
Typ=2 Len=2: c4,2

SQL> select dump(100000,16) from dual;

DUMP(100000,16)
-----------------
Typ=2 Len=2: c3,b

  SQL> alter system dump datafile 1 block 123657;

System altered.
   
row#52[7131] flag: ------, lock: 0, len=17
col 0; len 4; (4):  c3 0a 64 63
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 51
row#53[7114] flag: ------, lock: 0, len=17
col 0; len 4; (4):  c3 0a 64 64
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 52
row#54[5810] flag: ------, lock: 2, len=17
col 0; len 4; (4):  c3 0a 64 64
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 b6
row#55[5780] flag: ------, lock: 2, len=15
col 0; len 2; (2):  c3 0b
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 b8
row#56[5795] flag: ------, lock: 2, len=15
col 0; len 2; (2):  c4 02
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 b7
row#57[7101] flag: ------, lock: 0, len=13
col 0; NULL
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 53
row#58[7088] flag: ------, lock: 0, len=13
col 0; NULL
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 54
row#59[7075] flag: ------, lock: 0, len=13
col 0; NULL
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 55
row#60[7062] flag: ------, lock: 0, len=13
col 0; NULL
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 41 e1 98 01 56

flyfish576

主要是考虑到了B树的 高度和存储空间

yanshoupeng

0x41e131 0000000001   0000011110000100110001=> 1 123185  这个是root block
   0x41e309 0000000001   0000011110001100001001   1 123657  这个是目前最右手边的leaf block存放了NULL值

老大 这个BLOCKID 123657 是怎么计算出来的

Maclean Liu(刘相兵

你可以直接用以下函数 来转换：

1. CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
   
2.    RETURN VARCHAR2
   
3. IS
   
4.    l_str   VARCHAR2 (255) DEFAULT NULL;
   
5.    l_fno   VARCHAR2 (15);
   
6.    l_bno   VARCHAR2 (15);
   
7. BEGIN
   
8.    l_fno :=
   
9.       DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),
   
10.                                                        'xxxxxxxx'
    
11.                                                       )
    
12.                                            );
    
13.    l_bno :=
    
14.       DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),
    
15.                                                         'xxxxxxxx'
    
16.                                                        )
    
17.                                             );
    
18.    l_str :=
    
19.          'datafile# is:'
    
20.       || l_fno
    
21.       || CHR (10)
    
22.       || 'datablock is:'
    
23.       || l_bno
    
24.       || CHR (10)
    
25.       || 'dump command:alter system dump datafile '
    
26.       || l_fno
    
27.       || ' block '
    
28.       || l_bno
    
29.       || ';';
    
30.    RETURN l_str;
    
31. END;
    
32. /

复制代码

使用的方法：

SQL> select getbfno('0x00400009') from dual;

GETBFNO('0X00400009')
--------------------------------------------------------------------------------
datafile# is:1
datablock is:9
dump command:alter system dump datafile 1 block 9;

yanshoupeng

谢谢刘老大，我知道了。

leaf: 0x41e13c 4317500 (9: nrow: 399 rrow: 399)


这个4317500 其实就是DBA（DATA BLOCK ADDRESS）。