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

今天一个朋友问起在可为空的列id上建立了索引，当使用select count(*)时能否使用到该索引。下面是我做的一些测试，看起来有些乱，对照最后dump出的表与索引的数据，就比较清楚了。

 

1. 创建环境

 

C:\Documents and Settings\yuechao.tianyc>sqlplus test/test

 

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 24 14:36:40 2009

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

SQL> create table test_x(id number, name varchar(10));

 

表已创建。

 

SQL> insert into test_x select rownum, 'yct'||rownum from dual

  2  connect by rownum < 11;

 

已创建10行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from test_x;

        ID NAME

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

         1 yct1

         2 yct2

         3 yct3

         4 yct4

         5 yct5

         6 yct6

         7 yct7

         8 yct8

         9 yct9

        10 yct10

 

10 rows selected

 

SQL> create index ind_test_x on test_x(id);

 

索引已创建。

 

2. 测试索引的使用情况

 

-- 1. count(*)不使用索引

SQL> explain plan

  2  for

  3  select count(*) from test_x;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2620736238

 

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

| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |        |     1 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |        |     1 |            |          |

|   2 |   TABLE ACCESS FULL| TEST_X |    10 |     3   (0)| 00:00:01 |

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

 

Note

-----

   - dynamic sampling used for this statement

 

已选择13行。

 

-- 2. 只查询列id，仍不会使用索引

SQL> explain plan for

  2  select id from test_x;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1044436394

 

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

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |        |    10 |   130 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| TEST_X |    10 |   130 |     3   (0)| 00:00:01 |

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

Note

-----

   - dynamic sampling used for this statement

 

已选择12行。

 

-- 3. 通过谓词id=10，可以使用索引

SQL> explain plan for

  2  select id from test_x where id = 10;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3259450756

 

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

| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |            |     1 |    13 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IND_TEST_X |     1 |    13 |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

   1 - access("ID"=10)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择17行。

 

-- 4. 使用谓词 id is null，不能使用索引，即使我通过hint，仍不能使用索引

SQL> explain plan for

  2  select id from test_x where id is null;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1044436394

 

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

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |        |     1 |    13 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_X |     1 |    13 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("ID" IS NULL)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择17行。

 

SQL> explain plan for

  2  select/*+index (test_x ind_test_x)*/ id from test_x where id is null;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1044436394

 

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

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |        |     1 |    13 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_X |     1 |    13 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

   1 - filter("ID" IS NULL)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择17行。

 

3. 如果对列id限制为not null，就可以使用索引了。

 

SQL> alter table test_x modify id not null;

 

表已更改。

 

-- 1. 对于count(*)，直接从索引中统计数据

SQL> explain plan for

  2  select count(*) from test_x;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1581047046

 

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

| Id  | Operation        | Name       | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |            |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |            |     1 |            |          |

|   2 |   INDEX FULL SCAN| IND_TEST_X |    10 |     1   (0)| 00:00:01 |

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

 

Note

-----

   - dynamic sampling used for this statement

 

已选择13行。

 

-- 2. 直接查询列id，使用索引

SQL> explain plan for

  2  select id from test_x;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2342453941

 

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

| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |            |    10 |   130 |     1   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | IND_TEST_X |    10 |   130 |     1   (0)| 00:00:01 |

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

 

Note

-----

   - dynamic sampling used for this statement

 

已选择12行。

 

-- 3. 使用谓词 id is null，可以使用索引（虽然此时id为not null，这个谓词没有意义）

SQL> explain plan for

  2  select id from test_x where id is null;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 208822267

 

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

| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |            |     1 |    13 |     0   (0)|          |

|*  1 |  FILTER          |            |       |       |            |          |

|   2 |   INDEX FULL SCAN| IND_TEST_X |    10 |   130 |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

   1 - filter(NULL IS NOT NULL)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择18行。

 

4. 下面我看一下null值在索引中是如何存储的。

 

-- 1. 为避免update操作对dump文件中标志位的设置，重新创建测试数据

SQL> truncate table test_x;

 

Table truncated

 

SQL> insert into test_x values(null, 'yct1');

 

1 row inserted

 

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

 

1 row inserted

 

SQL> insert into test_x values(3, 'yct3');

 

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            yct1

         5     246599          1            yct2

         5     246599          2          3 yct3

         5     246599          3          4 yct4

 

已选择10行。

 

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

 

-- 3. dump表中数据和索引数据

SQL> alter system dump datafile 5 block 246599;

 

系统已更改。

 

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_4692.trc

 

dump文件中，表的数据如下：

 

block_row_dump:

tab 0, row 0, @0x1f8f

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

col  0: *NULL*

col  1: [ 4]  79 63 74 31

tab 0, row 1, @0x1f86

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

col  0: *NULL*

col  1: [ 4]  79 63 74 32

tab 0, row 2, @0x1f7b

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

col  0: [ 2]  c1 04

col  1: [ 4]  79 63 74 33

tab 0, row 3, @0x1f70

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 2

kdxcosdc 0

kdxconro 2

kdxcofbo 40=0x28

kdxcofeo 8012=0x1f4c

kdxcoavs 7972

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8036

row#0[8024] flag: ------, lock: 2, len=12

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

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

row#1[8012] flag: ------, lock: 2, len=12

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

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

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

 

比较二者可以看到，null值并没有出现在索引数据中。由此即可明白上面的测试结果的原因。

 

5. 想起以前的一些小测试 

• null值占用空间的一点测试
• 关于NULL（1）
• 关于NULL（2）
• 关于NULL（3）
• 关于NULL（4）