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

以前没有接触到，的确是sql优化很经典的方法 假设有这样一个情况，在一个表中的某一个字段的某一个相对于其他经常使用，但是表的记录比较大，我们就可以使用这种方法具体的实例如下： SQL drop table t purge;表已删除。SQL set autotrace offSQL create tabl

以前没有接触到，的确是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的性能。