oracle仅部分记录建立索引的方法-阿里云开发者社区

开发者社区> 科技小能手> 正文

oracle仅部分记录建立索引的方法

简介:
+关注继续查看
今天研究了一下oracle对部分记录建立索引的方法。其实对部分记录建立的索引就是基于
函数的索引。由于部分记录函数化以后,返回了NULL值,而ORACLE索引不索引NULL值,
导致这些记录没有索引条目。
  
  这样我们就可以对自已关注的少数记录建立很小索引,提高查询速度。
一个例子,学校有10000学生,其中女同学仅有100人。我们经常关注女生,几乎不关注男生。
这样我们就可以只在女生记录上添加索引。这个很小的索引就能帮我们很快把女生找到。
做个测试:
SQL> create table students(id int ,name varchar2(200),sex varchar(16)) 
    2    / 
Table created 

SQL> declare 
    2     style varchar2(16); 
    3    begin 
    4    for i in 1..10000 loop 
    5     if(i mod 100 = 0) then 
    6         style:='F'
    7     else style := 'M'
    8     end if
    9     insert into students values(i,rpad('student'||i,150,'F'),style); 
10     end loop; 
11     commit
12    end
13    / 
PL/SQL procedure successfully completed.
这里为了让女生分布均匀,加了i mod 100;
SQL> select count(1) from students where sex='F'
  2  /
 
  COUNT(1)
----------
       100
刚好100位女生.
SQL> select * from students where (case when sex='F' then sex end)='F' 
    2    / 
已选择100行。 
执行计划 
---------------------------------------------------------- 
Plan hash value: 4078133427 
------------------------------------------------------------------------------ 
| Id    | Operation                 | Name         | Rows    | Bytes | Cost (%CPU)| Time         | 
------------------------------------------------------------------------------ 
|     0 | SELECT STATEMENT    |                    |        93 | 11625 |        56     (2)| 00:00:01 | 
|*    1 |    TABLE ACCESS FULL| STUDENTS |        93 | 11625 |        56     (2)| 00:00:01 | 
------------------------------------------------------------------------------ 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
     1 - filter(CASE "SEX" WHEN 'F' THEN "SEX" END ='F'
Note 
----- 
     - dynamic sampling used for this statement 

统计信息 
---------------------------------------------------------- 
                169    recursive calls 
                    0    db block gets 
                337    consistent gets 
                    0    physical reads 
                    0    redo size 
            17196    bytes sent via SQL*Net to client 
                451    bytes received via SQL*Net from client 
                    8    SQL*Net roundtrips to/from client 
                    4    sorts (memory) 
                    0    sorts (disk
                100    rows processed 
执行计划采用了全表扫描。
建立部分元组索引:
SQL> create index femaleIndex on students(case when sex='F' then sex end); 
Index created 
SQL> exec dbms_stats.gather_index_stats('study','femaleIndex'
    
PL/SQL procedure successfully completed 
    
SQL> select num_rows,blevel from user_indexes where index_name=upper('femaleIndex'
    2    / 
    
    NUM_ROWS         BLEVEL 
---------- ---------- 
             100                    0 
索引条目刚好100个,与女生数相等。
SQL> select * from students where (case when sex='F' then sex end)='F' 
    2    / 
已选择100行。 

执行计划 
---------------------------------------------------------- 
Plan hash value: 2152294204 
| Id    | Operation    | Name                | Rows    | Bytes | Cost (%CPU)| Time | 
------------------------------------------------------------------------------- 
|    0 | SELECT STATEMENT                        |                    | 93 | 11625 |    2     (0)|00:00:01 | 
|    1 |    TABLE ACCESS BY INDEX ROWID| STUDENTS | 93 | 11625 |    2     (0)| 00:00:01 | 
|* 2 |     INDEX RANGE SCAN                    | FEMALEINDEX| 38 |         |    1     (0)| 00:00:01 | 
-------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
     2 - access(CASE "SEX" WHEN 'F' THEN "SEX" END ='F'
Note 
------ dynamic sampling used for this statement 

统计信息 
---------------------------------------------------------- 
                 11    recursive calls 
                    0    db block gets 
                174    consistent gets 
                    0    physical reads 
                    0    redo size 
            17196    bytes sent via SQL*Net to client 
                451    bytes received via SQL*Net from client 
                    8    SQL*Net roundtrips to/from client 
                    0    sorts (memory) 
                    0    sorts (disk
                100    rows processed 
可以看到速度提高了很多。但是如果我们查询男生:
SQL> select * from students where (case when sex='F' then sex end)='M' 
    2    / 
未选定行 

执行计划 
---------------------------------------------------------- 
Plan hash value: 2152294204 
------------------------------------------------------------------------------- 
| Id    | Operation | Name                | Rows    | Bytes | Cost (%CPU)|Time         | 
-------------------------------------------------------------------------------- 
|     0 | SELECT STATEMENT                |                    |         3 | 375 | 2     (0)|00:00:01 | 
|     1 |    TABLE ACCESS BY INDEX ROWID| STUDENTS|    3 | 375 | 2     (0)|00:00:01 | 
|*    2 |     INDEX RANGE SCAN            | FEMALEINDEX |    38 |        |    1     (0)|00:00:01 | 
-------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
     2 - access(CASE "SEX" WHEN 'F' THEN "SEX" END ='M'
Note 
----- 
     - dynamic sampling used for this statement 

统计信息 
---------------------------------------------------------- 
                 11    recursive calls 
                    0    db block gets 
                 67    consistent gets 
                    0    physical reads 
                    0    redo size 
                375    bytes sent via SQL*Net to client 
                374    bytes received via SQL*Net from client 
                    1    SQL*Net roundtrips to/from client 
                    0    sorts (memory) 
                    0    sorts (disk
                    0    rows processed
居然没有记录返回!! 当然了因为(case when sex='F' then sex end)这个函数,我们姑且把它看成一个函数,是不会返回'M'这个值的。这也说明了如果建立了函数索引,查询条件上使用了该函数,则查询必走此索引。
以上是个简单的例子,假如我们一个项目管理系统,项目只有三种状态:投标,开发,验收。历经很多年,验收的验目很多,但正在投标,开发的项目肯定不多(IBM可能有很多),这样我们就可以只在需要关注的投标与开发的记录上添加索引:
create index test on projects(case when status='投标' then  status when status='开发' then  status end).
部分记录建立的索引还有一个经典用法就是建立唯一索引,完成对记录的约束,比如上面的例子,如果建成唯一索引,那么全校只能有一个女生了,而男生无限制。我不喜欢这样,所以就不演示了,记得加个unique就成。


本文转自 anranran 51CTO博客,原文链接:http://blog.51cto.com/guojuanjun/325580

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
ECS的使用感受
在学校结合所学的知识,运用在阿里云上,学思悟践,使我进步了很多!
9 0
作为程序员的他,大学四年一直自学,全靠这些实用工具和学习网站!
作为程序员的他,大学四年一直自学,全靠这些实用工具和学习网站!
4 0
mysql操作过程中的一些注意点
mysql操作过程中的一些注意点
5 0
面试官:equals和hashCode到底有什么关系?
面试官:equals和hashCode到底有什么关系?
4 0
【大学四年自学Java的学习路线】写了一个月,这是一份最适合普通大众、非科班的路线,祝你零基础快速找到一份满意的工作(3)
【大学四年自学Java的学习路线】写了一个月,这是一份最适合普通大众、非科班的路线,祝你零基础快速找到一份满意的工作
5 0
细说一下RedisTemplate的使用方法(二)
执行自定义SQL方法时,调用的就是execute方法。execute方法是一个很宽泛的方法,其他专注特定的Redis数据类型封装的方法都是基于此方法。这就说明了其他方法能实现的,execute方法也能实现。
5 0
当推荐遇到社交:美图的推荐算法设计优化实践
本文是美图高级算法专家汤斌的一篇文章,重点介绍了社交网络背景下推荐算法面临的挑战,以及应对的方法。
5 0
详细分析MyBatis框架中exists的基本使用
本篇文章中主要介绍了MyBatis框架中exists的基本用法。在介绍exists的用法的同时,也介绍了not exists的基本使用,说明了exists和in在使用过程中的区别。使用一个具体示例对MyBatis框架中使用exists进行具体详细的说明。最后对SQL中的in,not in,exists,not exists之间的区别进行总结说明。
4 0
关于 Kubernetes中Volume的一些笔记
男女情事,谁先动心谁吃亏,越吃亏越难忘,到最后,到底是喜欢对方呢,还是喜欢自己,都搞不清楚了,答案偏偏在对方身上,所以才说,由爱故生忧。--------《剑来》
4 0
历经 7 年双 11 实战,阿里巴巴是如何定义云原生混部调度优先级及服务质量的?
本文将聚焦在 K8s 层的容器优先级和服务质量模型上,希望给业界提供一些可借鉴的思路。
6 0
14426
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载