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