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

类别:数据库 点击:0 评论:0 推荐:
使用索引的误区之五:空值的妙用

并不是因为完全为空的条目不被记录到索引中,就坚决不能使用空值,相反,有时候合理使用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>

 

 

本文地址:http://com.8s8s.com/it/it19843.htm