在Oracle世界里，索引主要有B树索引，位图索引和全文索引等，其中最常见于OLTP系统的是B树索引。下图所示就是一个典型的B树索引结构，Root为根节点，branch 为分支节点，
leaf 到最下面一层称为叶子节点。每个节点表示一层，当查找某一数据时先读根节点，再读支节点，最后找到叶子节点。叶子节点会存放索引的键值，和指向表中实际数据位置的rowid。
在OLTP系统中，通过合理的创建和使用索引，可以大大提高sql语句的执行效率。但是B树索引有一个缺点就是，索引的叶子节点中不会存放null字段的值，也就表明如果sql语句中使用了诸如is null或是is not null，那么oracle通常是不使用B树索引的。
    那么有没有办法让oracle引擎，对于涉及null查询的sql语句也可以使用B树索引呢？方法是肯定的，那就是我们可以创建一个包含伪列的复合索引，且该复合索引的前导列就是sql中使用的查询列。通过这这种方法创建的B树索引，将使得涉及nulL查询的sql一样可以使用B数索引。


    下面我们将通过实际测试加以验证，测试环境使用的数据库版本为：


SQL> select * from v$version;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


一）创建测试表，并将表中object_id列的部分行置为NULL
SQL> update tab119 set object_id = null where rownum < 9;


8 rows updated.


SQL> commit;


Commit complete.


SQL> insert into tab119 select * from tab119 order by trunc(dbms_random.value(1,16));


14039 rows created.


SQL> /


28078 rows created.


SQL> /


56156 rows created.


SQL> /


112312 rows created.


SQL> /


224624 rows created.


SQL> commit;
二）查看表中object_id列NULL和NOT NULL的行数分布，可以看出object_id为NULL的行数约为250列左右，整个表占用了5888个数据块。
SQL> select count(*),count(object_id) from tab119;


  COUNT(*) COUNT(OBJECT_ID)
---------- ----------------
    449248           448992


SQL> select blocks from dba_segments where segment_name='TAB119';


    BLOCKS
----------
      5888


三）针对object_id列创建B树索引， 并收集表和索引的统计信息
SQL> create index ind_tab119_obj_id on tab119(object_id);
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'TAB119', estimate_percent=>100, cascade=>true);


PL/SQL procedure successfully completed.
四）执行NULL判断的查询，通过autotrace可以看出该查询没有使用索引，走的是全表扫描，逻辑读大约是5889块
SQL> select object_name from tab119 where object_id is null;
256 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 1553573051


----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   256 |  6144 |  1593   (1)| 00:00:20 |
|*  1 |  TABLE ACCESS FULL| TAB119 |   256 |  6144 |  1593   (1)| 00:00:20 |
----------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("OBJECT_ID" IS NULL)




Statistics
----------------------------------------------------------
         23  recursive calls
          0  db block gets
       5889  consistent gets
          0  physical reads
          0  redo size
       6040  bytes sent via SQL*Net to client
        710  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
        256  rows processed


五）创建一个包含伪列的索引，并再次收集表和索引的统计信息
SQL> create index ind_tab119_obj2_id on tab119(object_id, '0');


Index created.


SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'TAB119', estimate_percent=>100, cascade=>true);


PL/SQL procedure successfully completed.


六）再次执行上述查询，通过autotrace的结果可以看出此次查询使用了IND_TAB119_OBJ2_ID索引，其逻辑读也锐减为251个。
SQL> select object_name from tab119 where  object_id is null;


256 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 3084549537


--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |   256 |  6144 |   259   (0)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB119             |   256 |  6144 |   259   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | IND_TAB119_OBJ2_ID |   256 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("OBJECT_ID" IS NULL)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        251  consistent gets
          0  physical reads
          0  redo size
       6040  bytes sent via SQL*Net to client
        710  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        256  rows processed


七）实际生产上的应用
上面都是测试的例子，下面我们看一个实际的生产系统的例子。生产系统上的一个表，其XXXXX_GROUP_ID列的值分布如下。可以看出该列的大部分数据都是null，大约占了全部数据的99.5%。


1）改列数据的NULL分布情况
select market_group_id, count(*) from t_xxxxx_yyyyy_data group by market_group_id order by 1
BTU@ oracle>/
G011803680      77350
G032702580      59256
G033002680        252
G080502780          6
G080602880          4
G081302980        321
G083103080       4213
G083103082          8
G110203280        307
G110903380      19143
G120203480      30261
G123003580      64219
             59123243
2）性能较低的查询sql
该系统经常运行下面的查询语句，正常情况下，由于oracle的B树索引对null查询不起作用，因此该查询只能使用全表扫描,见执行计划。T_XXXXX_YYYYY_DATA表较大，每次全表扫描需要耗费
大约280万个物理读。


SELECT A.XXXXX_GROUP_ID, TO_CHAR(SUM(A.YYYYY_AMT)) YYYYY_AMT
  FROM T_XXXXX_YYYYY_DATA A
 WHERE EXISTS (SELECT 1
          FROM T_XXXXX_CFG B
         WHERE A.XXXXX_CFG_ID = B.XXXXX_CFG_ID
           AND B.CHECK_FLAG = '2'
           AND B.VALID_FLAG = '1' --and b.end_date >= to_char(sysdate, 'yyyymmdd'))
   AND A.TRADE_TYPE = '01'
   AND A.YYYYY_FUND_TYPE = '1'
   and a.XXXXX_group_id is not null
--AND A.Batch_Date= to_char(sysdate,'yyyymmdd')                 
GROUP BY
 A.XXXXX_GROUP_ID




---------------------------------------------------------------------------------------------
| Id  | Operation            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                      |       |       |   553K(100)|          |
|   1 |  HASH GROUP BY       |                      |    10 |   380 |   553K  (1)| 01:50:43 |
|   2 |   HASH JOIN          |                      | 44022 |  1633K|   553K  (1)| 01:50:42 |
|   3 |    SORT UNIQUE       |                      |  3500 | 56000 |   137   (1)| 00:00:02 |
|   4 |     TABLE ACCESS FULL| T_XXXXX_CFG         |  3500 | 56000 |   137   (1)| 00:00:02 |
|   5 |    TABLE ACCESS FULL | T_XXXXX_YYYYY_DATA | 63975 |  1374K|   553K  (1)| 01:50:41 |
---------------------------------------------------------------------------------------------
3）考虑到XXXXX_group_id列的大部分数据都是NULL，而查询的条件是NOT NULL，实际查询仅需要访问表中少量的数据，因此在XXXXX_group_id列上创建复合伪索引
create index I_XXXXX_GROUP_ID on T_XXXXX_YYYYY_DATA(XXXXX_GROUP_ID ,'1');
创建索引后，按照期望，该语句使用了索引，执行计划如下，物理读也降低到30万左右，效率有所提高。
Plan hash value: 1291646807


------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |       |       |   616K(100)|          |
|   1 |  SORT GROUP BY NOSORT         |                      |    10 |   380 |   616K  (1)| 02:03:20 |
|   2 |   NESTED LOOPS SEMI           |                      |   148K|  5522K|   616K  (1)| 02:03:20 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_MARKET_REBATE_DATA |   161K|  3473K|   131K  (2)| 00:26:14 |
|   4 |     INDEX FULL SCAN           | I_MARKET_GROUP_ID    |   256K|       |   117K  (2)| 00:23:28 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T_MARKET_CFG         |  3755 | 60080 |     3   (0)| 00:00:01 |
|   6 |     INDEX RANGE SCAN          | I_MC_MARKET_CFG_ID   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------




结论：在oracle中，对于涉及null或not null的sql查询，通过合理的构造索引的结构，并结合实际的sql需要及数据分布，是可以让sql引擎使用B数索引的，并且可以大大提高效率。