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