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

以前没有接触到，的确是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>分析表 t 计算表中所有索引列的统计信息；

--查看函数索引的性能
SQL>设置自动跟踪仅跟踪 SQL> select * from t where (status='N' then 'N' end)='N'; 执行计划 -------------------------------------------------- -------- 计划哈希值：1835552001 -------------------------------------------------- ------------------------------ --------- |身份证 |运营|名称 |行|字节 |成本（%CPU）|时间 | -------------------------------------------------- ------------------------------ --------- | 0 |选择语句 | | 1 | 10 | 10 2 (0)| 0 0:06:48 | 0 0:06:48 | 1 |按索引 ROWID 访问表| T | 1 | 10 | 10 2 (0)| 0 0:06:48 | 0 0:06:48 |* 2 |指数范围扫描 | ID_状态| 1 | | 1 (0)| 0 0:03:24 | 0 0:03:24 -------------------------------------------------- ------------------------------ --------- 谓词信息（通过操作id标识）： -------------------------------------------------- - 2 - 访问（案例“状态”当'N'然后'N'结束='N'） 统计信息 -------------------------------------------------- -------- 15 次递归调用 0 db 块获取 2 一致获取 0 物理读取 0 重做大小 通过 SQL*Net 发送到客户端的 591 字节 通过 SQL*Net 从客户端接收到 519 字节 往返客户端的 2 次 SQL*Net 往返 0 种（内存） 0 种（磁盘） 已处理 1 行
--连接观察函数索引的情况 SQL>设置自动跟踪关闭 SQL>分析索引id_status验证结构； 索引已分析 SQL>从index_stats中选择名称、btree_space、lf_rows、高度； 名称 BTREE_SPACE LF_ROWS 高度 ------------------------------ ----------- --------- - ---------- ID_STATUS 8000 1 1 --函数索引的要少很多
使用函数索引减少了逻辑读取，一定程度提高了sql的性能。