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

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

以前没有接触到，的确是sql优化很经典的方法
假设有这样一个情况，在一个表中的某一个字段的某一个值相对于其他值经常使用，但是表的记录比较大，我们就可以使用这种方法具体的实例如下：
SQL> drop table t purge;表已删除。SQL> set autotrace offSQL> 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 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 1000SQL> set autotrace traceonlySQL> 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 来@源gao*daima.com搞@代#码网| 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 offSQL> 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 3SQL> set autotrace offSQL> 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 traceonlySQL> 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 offSQL> 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的性能。

喜欢 (0)赏

[搞代码]

分享 (0)