函数索引使用之部分记录建索引

以前没有接触到，的确是sql优化很经典的方法

假设有这样一个情况，在一个表中的某一个字段的某一个值相对于其他值经常使用，但是表的记录比较大，我们就可以使用这种方法

具体的实例如下：

SQL> drop table t purge;

表已删除。

SQL> set autotrace off

SQL> create table t (id int ,status varchar2(2));

表已创建。

--建立普通索引

SQL> create index id_normal on t(status);

索引已创建。

SQL> insert into t select rownum ,'Y' from dual connect by rownum<=1000000;

已创建1000000行。

SQL> insert into t select 1 ,'N' from dual;

已创建 1 行。

SQL> commit;

--进行表分析

SQL> analyze table t compute statistics for table for all indexes for all indexe d columns;

--当使用普通索引性能如下

SQL> set linesize 1000

SQL> set autotrace traceonly

SQL> select * from t where status='N';

SQL> select * from t where status='N';

执行计划

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

Plan hash value: 2252729315

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T ime | 

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

| 0  | SELECT STATEMENT |      | 1     | 10 | 4 (0)| 0 0:13:35 | 

| 1  | TABLE ACCESS BY INDEX ROWID| T  | 1 | 10 | 4 (0)| 0 0:13:35 | 

|* 2 | INDEX RANGE SCAN | ID_NORMAL | 1 | | 3 (0)| 0 0:10:11 | 

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

Predicate Information (identified by operation id):

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

   2 - access("STATUS"='N')

统计信息

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

          1 recursive calls

          0 db block gets

          5 consistent gets --产生5个逻辑读

          0 physical reads

          0 redo size

        595 bytes sent via SQL*Net to client

        519 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

--查看索引的详细信息

SQL> set autotrace off

SQL> analyze index id_normal validate structure;

索引已分析

SQL> select name,btree_space,lf_rows,height from index_stats;

NAME BTREE_SPACE LF_ROWS HEIGHT

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

ID_NORMAL 22600352 1000001 3

SQL> set autotrace off

SQL> analyze index id_normal validate structure;

索引已分析

SQL> select name,btree_space,lf_rows,height from index_stats;

NAME                           BTREE_SPACE  LF_ROWS    HEIGHT

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

ID_NORMAL                        22600352   1000001      3   --产生的索引的详细信息

--建函数索引

SQL> drop index id_normal; 

索引已删除。

SQL> create index id_status on t (Case when status= 'N' then 'N' end);

/*

   select * from t where (case when status='N' then 'N' end)='N' 

   可以使用这种写法代替上面的写法

*/

索引已创建。

SQL> analyze table t compute statistics for table for all indexes for all indexe d columns;

--查看函数索引的性能

SQL> set autotrace traceonly

SQL> select * from t where (case when status='N' then 'N' end)='N';

执行计划

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

Plan hash value: 1835552001

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T ime | 

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

| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 0 0:06:48 | 

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)| 0 0:06:48 | 

|* 2 | INDEX RANGE SCAN | ID_STATUS | 1 | | 1 (0)| 0 0:03:24 | 

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

Predicate Information (identified by operation id):

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

   2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N')

统计信息

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

         15 recursive calls

          0 db block gets

          2 consistent gets

          0 physical reads

          0 redo size

        591 bytes sent via SQL*Net to client

        519 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

--接着观察函数索引的情况

SQL> set autotrace off

SQL> analyze index id_status validate structure;

索引已分析

SQL> select name,btree_space,lf_rows,height from index_stats;

NAME                           BTREE_SPACE   LF_ROWS  HEIGHT

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

ID_STATUS                       8000         1             1  --函数索引的要少很多

使用函数索引减少了逻辑读，一定程度提高了sql的性能。