索引 - null值上的索引测试（2）

刚才测试的是单列的索引，下面继续测试在带有null值时，复合索引的使用情况。这次测试我先比较表与索引的数据，然后根据比较结果再测试索引的使用情况。

 

1. 创建测试数据

 

SQL> truncate table test_x;

 

Table truncated

 

SQL> drop index ind_test_x;

 

Index dropped

 

SQL> create index ind_test_x on test_x(id, name);

 

Index created

 

SQL> insert into test_x values(NULL, NULL);

 

1 row inserted

 

SQL> insert into test_x values(NULL, 'yct2');

 

1 row inserted

 

SQL> insert into test_x values(3, NULL);

 

1 row inserted

 

SQL> insert into test_x values(4, 'yct4');

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

2. 查看数据的存储情况

 

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,

  2    dbms_rowid.rowid_block_number(rowid) bno,

  3    dbms_rowid.rowid_row_number(rowid) rno,

  4    id, name

  5  from test_x;

 

       FNO        BNO        RNO         ID NAME

---------- ---------- ---------- ---------- ----------

         5     246599          0            

         5     246599          1            yct2

         5     246599          2          3 

         5     246599          3          4 yct4

 

SQL> col segment_name format a14

SQL> col segment_type format a14

SQL> col relative_fno format 999999

SQL> col header_file  format 999999

SQL> col header_block format 999999

SQL> col blocks format 999999

SQL> SELECT segment_name, segment_type, relative_fno, header_file, header_block, blocks

  2  FROM dba_segments WHERE segment_name IN('TEST_X', 'IND_TEST_X');

 

SEGMENT_NAME   SEGMENT_TYPE   RELATI HEADER HEADER BLOCKS

-------------- -------------- ------ ------ ------ ------

TEST_X         TABLE               5      5 246595      8

IND_TEST_X     INDEX               5      5 246603      8

 

SQL> alter system dump datafile 5 block 246599;

 

System altered

 

SQL> alter system dump datafile 5 block 246604;

 

System altered

 

SQL> select p.value||'\'||i.instance_name||'_ora_'||p.spid||'.trc' trace_path

  2  from v$process p, v$instance i, v$parameter p

  3  where p.addr = (

  4  select paddr from v$session

  5  where sid = ( select sid from v$mystat where rownum = 1)

  6  )

  7  and p.name='user_dump_dest';

 

TRACE_PATH

--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_7636.trc

 

表中数据存储情况如下（紫色字体为我的注释）：

 

block_row_dump:

tab 0, row 0, @0x1f95 #第1行都为null，都不存储

tl: 3 fb: --H-FL-- lb: 0x1  cc: 0

tab 0, row 1, @0x1f8c #第2行id为null，占用一点空间

tl: 9 fb: --H-FL-- lb: 0x1  cc: 2

col  0: *NULL*

col  1: [ 4]  79 63 74 32

tab 0, row 2, @0x1f86 #第3行name为null，不存储

tl: 6 fb: --H-FL-- lb: 0x1  cc: 1

col  0: [ 2]  c1 04

tab 0, row 3, @0x1f7b #第4行都不为null，都存储

tl: 11 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 05

col  1: [ 4]  79 63 74 34

end_of_block_dump

 

索引中数据存储情况如下：

 

Leaf block dump

===============

header address 153102948=0x9202a64

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 3

kdxcosdc 0

kdxconro 3

kdxcofbo 42=0x2a

kdxcofeo 7991=0x1f37

kdxcoavs 7949

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8036

row#0[8008] flag: ------, lock: 2, len=13 #第3行name为null，建立了索引

col 0; len 2; (2):  c1 04

col 1; NULL

col 2; len 6; (6):  01 43 c3 47 00 02

row#1[7991] flag: ------, lock: 2, len=17 #第4行都不为null，建立了索引

col 0; len 2; (2):  c1 05

col 1; len 4; (4):  79 63 74 34

col 2; len 6; (6):  01 43 c3 47 00 03

row#2[8021] flag: ------, lock: 2, len=15 #第2行id为null，建立了索引

col 0; NULL

col 1; len 4; (4):  79 63 74 32

col 2; len 6; (6):  01 43 c3 47 00 01

----- end of leaf block dump -----

 

第1行因为id和name都为null，所以并未建立对应的索引。为什么第2行的索引会放到最后呢？因为索引数据是排序后存储的，这里是按照order by id, name来存储的。对于null值，Oracle会认为它“无限大”，所以放在最后来存储。比如下面的测试：

 

SQL> select * from test_x order by id, name;

 

        ID NAME

---------- ----------

         3 

         4 yct4

           yct2

 

 

SQL> 

 

在前几天的一个测试中，也测试到了null值的排序问题（见《SQL中对null数据的排序问题》）。

 

3. 根据上面的数据比较，其实可以猜测出这个复合索引的使用情况。

 

我们先把数据填满，同时为了执行计划显示得更准确，在test_x上增加一列：

 

SQL> alter table test_x add sex varchar2(1);

 

Table altered

 

SQL> update test_x set id = rownum , name='yct'||rownum, sex='f';

 

4 rows updated

 

SQL> commit;

 

Commit complete

 

SQL> select * from test_x;

 

        ID NAME       SEX

---------- ---------- ---

         1 yct1       f

         2 yct2       f

         3 yct3       f

         4 yct4       f

 

比如对于如下SQL，可以先猜猜是否能使用到索引，然后再动手测试： 

• select count(*) from test_x;
• select count(*) from test_x where id = 3;
• select count(*) from test_x where name = 'yuechaotian';
• select count(*) from test_x where id = 3 and name = 'yuechaotian';
• select * from test_x where id = 3;
• select * from test_x where name = 'yuechaotian';
• select * from test_x where  id = 3 and name = 'yuechaotian';
• select id from test_x where id = 3;
• select id from test_x where id is null;
• select id from test_x where name = 'yuechaotian';
• select id from test_x where name is null;

4. 当列id和name都设置为not null的情况下，重新猜测一下这些SQL能否使用索引。