不懂利用选择性索引提高效率

数据库的设计是很灵活多变的,要有跳出常规思维的想法。结算组有个兄弟问我:“能不能只对某个字段的某些特定的值建索引?”问这话值得表扬,因为他会思考,认为这样的索引要比普通索引“个头”来的小一点,所以会更块!
确实有只对列的某些值建索引的方法,测试如下:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as ljb
SQL> drop table ljb_test;
Table dropped
SQL> create table ljb_test (id int ,status varchar2(2));
Table created

这里表示只对status列的值为N的情况建索引

SQL> create index id_status on  ljb_test (Case when status= 'N' then 'N' end);
Index created
插入很多为Y的值
SQL> insert into ljb_test select rownum ,'Y' from dual connect by rownum<=100000;
100000 rows inserted
插入单条值为N的记录
SQL> insert into ljb_test select 1 ,'N' from dual;
1 row inserted
SQL> commit;
Commit complete

分析的目的是为了让查询在基于COST模式下,因为只为字段的部分值建索引本质是函数索引,要在COST模式下才生效。

SQL> analyze table ljb_test compute statistics for table for all indexes for all indexed columns;
Table analyzed
以下这个select * from ljb_test where (case when status='N' then 'N' end)='N’写法不能变,如果是select * from ljb_test where status='N'将无效!我见过有些人设置了选择性索引,却这样调用的,结果根本起不到任何效果!
SQL> explain plan for  select * from ljb_test where (case when status='N' then 'N' end)='N';
Explained

可以看出来代价还是比较低的,效果不错!

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3317130416
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     2   (0)| 0
|   1 |  TABLE ACCESS BY INDEX ROWID| ljb_test   |     1 |    10 |     2   (0)| 0
|*  2 |   INDEX RANGE SCAN          | ID_STATUS |     1 |       |     1   (0)| 0
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N')
14 rows selected

接着删除掉选择性索引,建普通索引比较一下效果

SQL> drop index id_status;
Index dropped
SQL> create index id_normal on ljb_test(status);
Index created
SQL> explain plan for  select * from ljb_test where status='N';
Explained

发现同样是走了索引,但是代价却高出许多!

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2648582318
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  1000 | 10000 |    40   (0)| 0
|   1 |  TABLE ACCESS BY INDEX ROWID| ljb_test   |  1000 | 10000 |    40   (0)| 0
|*  2 |   INDEX RANGE SCAN          | ID_NORMAL |   400 |       |    91   (0)| 0
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------------------------
   2 - access("STATUS"='N')
14 rows selected

不难看两者效率差别巨大,所以在你知道某些字段值的分布,并确定某些字段的特定值比较少,而你的应用却经常查询这些特定的少量值的时候,可以用这个方法, 于是选择性索引就闪亮登场,有了大展身手的机会了!
介绍完了选择性索引,大家能回答为什么更快吗?很多人会说:“这简单,选择性索引只选择少的特定值做索引,那索引块就很小,搜索起来更快!”回答正确!不过你能回答索引块变小是多小,和普通索引差距有多少?能量化为具体值吗?下面我来回答这个问题。

以下是选择性索引id_status建好后,索引的情况

SQL> analyze index id_status validate structure;
Index analyzed
SQL> select name,btree_space,lf_rows,height from index_stats;
NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ------------------------------------------------------
ID_STATUS                             8000           1            1 
接着是看id_normal,普通索引的情况
SQL> select name,btree_space,lf_rows,height from index_stats;
NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- -------------------------------------------------------
ID_NORMAL                          1464032        100001         2

总结:很明显,选择索引的大小为8K,而普通索引的大小为1464K;选择索引的高度为1,而普通索引的的高度为2。差异相当显著!通过本小节应该学会巧妙的将选择性索引应用到的工作中去,会带来意想不到效果!

Trackback

no comment untill now

Add your comment now

切换到手机版