使用索引的误区之五：空值的妙用

使用索引的误区之五：空值的妙用

并不是因为完全为空的条目不被记录到索引中，就坚决不能使用空值，相反，有时候合理使用oracle的空值会为我们的查询带来几倍甚至几十倍的效率提高。

举个例子，加入有一个表，里面有个字段是“处理时间”，如果没有处理的事务，该列就为空，并且在大部分情况下，处理的事务总是在总记录数的10％或者更少，而等待处理的记录（“处理时间”这列为空）总是绝大多数的记录，那么在“等待时间”这列上建立索引，索引中就总是会保存很少的记录，我们希望的访问方式是，当访问表中所有代处理的记录（即10％或者更多的记录数目）时，我们希望通过全表扫描的方式来检索；然而，当我们希望访问已经处理的事务（即5％或者更少的记录数目）时，我们希望通过索引来访问，因为索引中的记录数目很少，请看下面的例子：

SQL> create table tt as select * from sys.dba_objects;

 

Table created

 

Executed in 0.601 seconds

 

SQL> alter table tt add (t int);

 

Table altered

 

Executed in 0.061 seconds

 

SQL> select count(*) from tt;

 

  COUNT(*)

----------

      6131c

 

Executed in 0.01 seconds

 

SQL> UPDATE tt set t=1 where owner='DEMO';

 

10 rows updated

 

Executed in 0.03 seconds

 

SQL> COMMIT;

 

Commit complete

 

Executed in 0 seconds

 

SQL> select count(*) from tt where OWNER='DEMO';

 

  COUNT(*)

----------

        10  ――――――――――――――已经处理的数目

 

Executed in 0.08 seconds

s

SQL> select count(*) from tt;

 

  COUNT(*)

----------

      6131      ――――――――――――――总记录数目

 

 

Executed in 0.01 seconds

 

下面的查询因为访问表中的大多数记录（代处理的记录，即10％以上的记录数目），可以看见，它如我们所希望的那样使用了全表扫描：

 

select object_name from tt where t is null;

 

PLAN_TABLE_OUTPUT

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

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

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT     |             |       |       |       |

|*  1 |  TABLE ACCESS FULL   | TT          |       |       |       |

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

Predicate Information (identified by operation id):

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

   1 - filter("TT"."T" IS NULL)

Note: rule based optimization

 

14 rows selected

 

Executed in 0.05 seconds

 

下面的查询因为要访问表中的少数记录，我们希望通过索引来访问：

select object_name from tt where t=1;

 

PLAN_TABLE_OUTPUT

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

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

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT     |             |       |       |       |

|*  1 |  TABLE ACCESS FULL   | TT          |       |       |       |

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

Predicate Information (identified by operation id):

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

   1 - filter("TT"."T"=1)

Note: rule based optimization

 

14 rows selected

 

Executed in 0.06 seconds

请注意，这里并没有如我们所希望的那样使用索引，而是使用了全表扫描，这里有一个结论：

建立了索引后，要想在CBO下合理的使用索引，一定要定期的更新统计信息

 

下面我们分析一下索引，看看有什么效果：

SQL> analyze index tt_idx validate structure;

 

Index analyzed

 

Executed in 0 seconds

 

SQL> select lf_rows from index_stats;

 

   LF_ROWS

----------

        10  ――――――――――索引中总共有10行

 

Executed in 0.05 seconds

 

SQL> exec dbms_stats.gather_index_stats('DEMO','TT_IDX');

 

PL/SQL procedure successfully completed

 

Executed in 0.03 seconds

 

SQL> SELECT DISTINCT_KEYS FROM USER_INDEXES;

 

DISTINCT_KEYS

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

            1   ――――――――――只有一个键值

 

Executed in 0.05 seconds

 

SQL> select * from tt where t is null;

 

已选择6121行。

 

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'TT'

 

 

 

 

Statistics

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

          0  recursive calls

          0  db block gets

        485  consistent gets

          0  physical reads

          0  redo size

     355012  bytes sent via SQL*Net to client

       4991  bytes received via SQL*Net from client

        410  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       6121  rows processed

 

SQL> select * from tt where t=5;

 

未选定行

 

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TT'

   2    1     INDEX (RANGE SCAN) OF 'TT_IDX' (NON-UNIQUE)

 

 

 

 

Statistics

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

          0  recursive calls

          0  db block gets

          1  consistent gets

          0  physical reads

          0  redo size

        964  bytes sent via SQL*Net to client

        372  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

SQL> select * from tt where t=1;

 

已选择10行。

 

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TT'

   2    1     INDEX (RANGE SCAN) OF 'TT_IDX' (NON-UNIQUE)

 

 

 

 

Statistics

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

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1639  bytes sent via SQL*Net to client

        503  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         10  rows processed

 

SQL> update tt set t=2 where t=1;

 

已更新10行。

 

 

Execution Plan

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

   0      UPDATE STATEMENT Optimizer=CHOOSE

   1    0   UPDATE OF 'TT'

   2    1     INDEX (RANGE SCAN) OF 'TT_IDX' (NON-UNIQUE)

 

 

 

 

Statistics

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

          0  recursive calls

         14  db block gets

          1  consistent gets

          0  physical reads

       3216  redo size

        616  bytes sent via SQL*Net to client

        527  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

         10  rows processed

 

SQL> set autotrace traceonly

SQL> update tt set t=3 where t is null;

 

6121 rows updated.

 

 

Execution Plan

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

   0      UPDATE STATEMENT Optimizer=CHOOSE

   1    0   UPDATE OF 'TT'

   2    1     TABLE ACCESS (FULL) OF 'TT'

 

 

 

 

Statistics

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

          0  recursive calls

      18683  db block gets

         80  consistent gets

          0  physical reads

    2583556  redo size

        618  bytes sent via SQL*Net to client

        533  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

       6121  rows processed

 

SQL>