`
burninglouis
  • 浏览: 36378 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
社区版块
存档分类
最新评论

oracle索引

阅读更多

唯一索引:unique
对于唯一索引来说,由于null不等于null,所以就算一列中有多个null ,还是可以建立唯一所有的。但是这样不能用到索引,因为有null值。

但是对添加唯一复合索引时,是不能插入null的。

 

  1. -->从上面的情形可知,  
  2. -->基于单列的唯一索引,可以多次插入null值,但其索引上并不存储null值。  
  3. -->基于多列的复合索引,尽管全为null值的行可以多次插入,但不全为null的重复行则不能被插入(注,非唯一复合索引不存在此限制,此处不演示)。  
  4. -->基于多列的复合索引,对于全为null值的索引值也不会被存储。如上面的情形,尽管插入了5条记录,复合索引中只存储了3条。  
  5. -->注:对于唯一性约束,null值不等于null值,同样(null,null)也不等同于(null,null),所以上面的两次null能够被插入。

二、null值与执行计划

  1. scott@ORCL> set autot trace exp;  
  2. scott@ORCL> select * from t1 where id is null;  
  3.   
  4. Execution Plan  
  5. ----------------------------------------------------------  
  6. Plan hash value: 3617692013  
  7.   
  8. --------------------------------------------------------------------------  
  9. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  10. --------------------------------------------------------------------------  
  11. |   0 | SELECT STATEMENT  |      |     5 |     5 |     3   (0)| 00:00:01 |  
  12. |*  1 |  TABLE ACCESS FULL| T1   |     5 |     5 |     3   (0)| 00:00:01 |  
  13. --------------------------------------------------------------------------  
  14.   
  15. Predicate Information (identified by operation id):  
  16. ---------------------------------------------------  
  17.   
  18.    1 - filter("ID" IS NULL)  
  19.   
  20. -->从上面的测试可知,由于null值是不被存储的,因此当使用id is null作为谓词时,走了全表扫描  
  21.      
  22. scott@ORCL> select * from t1 where id is not null;  
  23.   
  24. Execution Plan  
  25. ----------------------------------------------------------  
  26. Plan hash value: 796913935  
  27.   
  28. ---------------------------------------------------------------------------------------  
  29. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  30. ---------------------------------------------------------------------------------------  
  31. |   0 | SELECT STATEMENT            |         |     1 |     1 |     0   (0)| 00:00:01 |  
  32. |   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |     1 |     0   (0)| 00:00:01 |  
  33. |*  2 |   INDEX FULL SCAN           | I_T1_ID |     1 |       |     0   (0)| 00:00:01 |  
  34. ---------------------------------------------------------------------------------------  
  35.   
  36. Predicate Information (identified by operation id):  
  37. ---------------------------------------------------  
  38.   
  39.    2 - filter("ID" IS NOT NULL)  
  40.   
  41. -->从上面的测试可知,尽管当前表上id列上的所有值都为null,但不排除后续记录插入的id不为null的列。  
  42. -->故当使用id is not null作为谓词时,此时执行计划中走了索引全扫描。     
  43.   
  44. -->下面来看看复合索引的情形     
  45. scott@ORCL> select * from t1 where val is null;  
  46.   
  47. Execution Plan  
  48. ----------------------------------------------------------  
  49. Plan hash value: 3617692013  
  50.   
  51. --------------------------------------------------------------------------  
  52. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  53. --------------------------------------------------------------------------  
  54. |   0 | SELECT STATEMENT  |      |     2 |     2 |     3   (0)| 00:00:01 |  
  55. |*  1 |  TABLE ACCESS FULL| T1   |     2 |     2 |     3   (0)| 00:00:01 |  
  56. --------------------------------------------------------------------------  
  57.   
  58. Predicate Information (identified by operation id):  
  59. ---------------------------------------------------  
  60.   
  61.    1 - filter("VAL" IS NULL)  
  62.   
  63. scott@ORCL> select * from t1 where val is not null;  
  64.   
  65. Execution Plan  
  66. ----------------------------------------------------------  
  67. Plan hash value: 1931510411  
  68.   
  69. --------------------------------------------------------------------------------  
  70. | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  71. --------------------------------------------------------------------------------  
  72. |   0 | SELECT STATEMENT |             |     3 |     3 |     1   (0)| 00:00:01 |  
  73. |*  1 |  INDEX FULL SCAN | I_T1_ID_VAL |     3 |     3 |     1   (0)| 00:00:01 |  
  74. --------------------------------------------------------------------------------  
  75.   
  76. Predicate Information (identified by operation id):  
  77. ---------------------------------------------------  
  78.   
  79.    1 - filter("VAL" IS NOT NULL)  
  80.   
  81. -->对于复合唯一索引的情形,当使用单列且非前导列谓词时,使用is null与 is not null等同于单列唯一索引的情形。  
  82. -->即原理也是一样的,val is null走全表扫描而val is not null走索引。因为null值不会被存储。  
  83.   
  84. -->下面看看两个列都作为谓词的情形     
  85. scott@ORCL> select * from t1 where id is null and val is not null;  
  86.   
  87. Execution Plan  
  88. ----------------------------------------------------------  
  89. Plan hash value: 1040510552  
  90.   
  91. --------------------------------------------------------------------------------  
  92. | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  93. --------------------------------------------------------------------------------  
  94. |   0 | SELECT STATEMENT |             |     3 |     3 |     1   (0)| 00:00:01 |  
  95. |*  1 |  INDEX RANGE SCAN| I_T1_ID_VAL |     3 |     3 |     1   (0)| 00:00:01 |  
  96. --------------------------------------------------------------------------------  
  97.   
  98. Predicate Information (identified by operation id):  
  99. ---------------------------------------------------  
  100.   
  101.    1 - access("ID" IS NULL)  
  102.        filter("VAL" IS NOT NULL)  
  103.   
  104. -->从上面的测试可知,尽管两个谓词列上都存在索引,一个为单列唯一索引,一个为复合唯一索引。Oracle 选择了复合索引I_T1_ID_VAL。      
  105.   
  106. scott@ORCL> select * from t1 where id is not null and val is null;  
  107.   
  108. Execution Plan  
  109. ----------------------------------------------------------  
  110. Plan hash value: 796913935  
  111.   
  112. ---------------------------------------------------------------------------------------  
  113. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  114. ---------------------------------------------------------------------------------------  
  115. |   0 | SELECT STATEMENT            |         |     1 |     1 |     0   (0)| 00:00:01 |  
  116. |*  1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |     1 |     0   (0)| 00:00:01 |  
  117. |*  2 |   INDEX FULL SCAN           | I_T1_ID |     1 |       |     0   (0)| 00:00:01 |  
  118. ---------------------------------------------------------------------------------------  
  119.   
  120. Predicate Information (identified by operation id):  
  121. ---------------------------------------------------  
  122.   
  123.    1 - filter("VAL" IS NULL)  
  124.    2 - filter("ID" IS NOT NULL)      
  125.   
  126. -->同样的情形,谓词的顺序与复合索引定义的顺序一样,只不过第一个谓词为id is not null,而第二个谓词为val is null。  
  127. -->此时Oracle 选择了单列唯一索引I_T1_ID  
  128. -->看到此,不知道大家是否已明白,即哪个列为is not null,则会使用该列上的索引,原因还是那句话,索引不存储null值。  
  129. -->对于颠倒id列与val列以及id,val列为null或not null的其他不同组合情形不再演示,其执行计划类似。  

三、使用is null走索引的情形

  1. scott@ORCL> set autot off;  
  2. --删除原有表上的null值记录  
  3. scott@ORCL> delete from t1 where val not in('Y','N'or val is null;  
  4.   
  5. rows deleted.  
  6.   
  7. scott@ORCL> update t1 set id=1 where val='Y';  
  8.   
  9. 1 row updated.  
  10.   
  11. scott@ORCL> update t1 set id=2 where val='N';  
  12.   
  13. 1 row updated.  
  14.   
  15. scott@ORCL> commit;  
  16.   
  17. Commit complete.  
  18.   
  19. -->对原有记录更新后的情形  
  20. scott@ORCL> select * from t1;  
  21.   
  22.         ID VAL  
  23. ---------- ------------------------------  
  24.          1 Y  
  25.          2 N  
  26.   
  27. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);  
  28.   
  29. PL/SQL procedure successfully completed.  
  30.   
  31. -->修改表列id使之具有not null约束的特性  
  32. scott@ORCL> alter table t1 modify(id not null);  
  33.   
  34. Table altered.  
  35.   
  36. scott@ORCL> set autot trace exp;  
  37. scott@ORCL> select * from t1 where id is null;  
  38.   
  39. Execution Plan  
  40. ----------------------------------------------------------  
  41. Plan hash value: 3160894736  
  42.   
  43. --------------------------------------------------------------------------------  
  44. | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  45. --------------------------------------------------------------------------------  
  46. |   0 | SELECT STATEMENT |             |     1 |     5 |     0   (0)|          |  
  47. |*  1 |  FILTER          |             |       |       |            |          |  
  48. |   2 |   INDEX FULL SCAN| I_T1_ID_VAL |     2 |    10 |     1   (0)| 00:00:01 |  
  49. --------------------------------------------------------------------------------  
  50.   
  51. Predicate Information (identified by operation id):  
  52. ---------------------------------------------------  
  53.   
  54.    1 - filter(NULL IS NOT NULL)  
  55.   
  56. -->从上面的执行计划中可知,当表t1列id上具有not null 约束时,此时使用id is null选择了索引范围扫描  
  57.   
  58. -->下面来看看列val is null 的情形     
  59. scott@ORCL> select * from t1 where val is null;  
  60.   
  61. Execution Plan  
  62. ----------------------------------------------------------  
  63. Plan hash value: 48744011  
  64.   
  65. ------------------------------------------------------------------------------------  
  66. | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  67. ------------------------------------------------------------------------------------  
  68. |   0 | SELECT STATEMENT     |             |     1 |     5 |     2   (0)| 00:00:01 |  
  69. |*  1 |  INDEX FAST FULL SCAN| I_T1_ID_VAL |     1 |     5 |     2   (0)| 00:00:01 |  
  70. ------------------------------------------------------------------------------------  
  71.   
  72. Predicate Information (identified by operation id):  
  73. ---------------------------------------------------  
  74.   
  75.    1 - filter("VAL" IS NULL)  
  76.   
  77. -->尽管val列上允许null值存在,但由于列id上具有not null 约束,且id列与val列存在复合唯一索引,因此此时选择了索引快速全扫描  
  78. -->其余不同组合情形大致相同,不再演示  
  79.   
  80. -->为表t1新增一条val为null的记录  
  81. scott@ORCL> insert into t1 select 3,null from dual;  
  82.   
  83. 1 row created.  
  84.   
  85. scott@ORCL> commit;  
  86.   
  87. Commit complete.  
  88.   
  89. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);  
  90.   
  91. PL/SQL procedure successfully completed.  
  92.   
  93. -->下面的查询中可以看出尽管只有列id有not null约束,当所有的索引值都被存储  
  94. scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys  
  95.   2  from user_indexes  where table_name='T1';  
  96.   
  97. INDEX_NAME      INDEX_TYPE     BLEVEL LEAF_BLOCKS   NUM_ROWS STATUS   DISTINCT_KEYS  
  98. --------------- ---------- ---------- ----------- ---------- -------- -------------  
  99. I_T1_ID         NORMAL              0           1          3 VALID                3  
  100. I_T1_ID_VAL     NORMAL              0           1          3 VALID                3  
  101.   
  102. -->Author : Robinson Cheng  
  103. -->Blog :   http://blog.csdn.net/robinson_0612  
四、总结
    无论是单列唯一索引或复合唯一索引,对于可以为null的列或复合null值,Oracle不会为其存储索引值
    故在基于单列创建B树唯一索引或多列创建B树复合唯一索引的情形下,
    当列上允许为null值时
        where子句使用了基于is null的情形,其执行计划走全表扫描。
        where子句使用了基于is not null的情形,其执行计划走索引扫描(索引范围扫描或索引全扫描)。
    当列上不允许为null值时,存在非null约束
        where子句使用了基于is null的情行,其执行计划走索引扫描。
        where子句使用了基于is not null的情形,其执行计划也是走索引扫描
    注:此在Oracle 10g R2(linux)下的情形,不同的优化器版本可能会有偏差。

 

分享到:
评论

相关推荐

    ORACLE索引详解及SQL优化

    本文将深入探讨Oracle索引的原理、创建方法,并结合SQL优化策略,帮助你提升数据库查询速度,降低资源消耗。 首先,我们要理解索引的基本概念。在数据库中,索引类似于书籍的目录,它为数据提供快速访问的途径。...

    Oracle索引分析与比较

    Oracle 索引是数据库管理系统中提升数据查询速度的关键组件,尤其在大型企业级应用中,选择合适的索引类型对于数据库性能至关重要。本篇文章将深入探讨Oracle中的B*Tree索引、反向索引、降序索引、位图索引和函数...

    oracle索引类型及扫描方式大整理new

    ### Oracle索引类型详解 #### 一、B\*Tree索引:数据检索的基石 在Oracle数据库中,B\*Tree索引是最常见的索引结构,也是默认创建的索引类型。它基于二叉树原理,由分支块(branch block)和叶块(leaf block)构成,...

    oracle索引被限制的一些情况

    Oracle索引被限制的一些情况 Oracle索引是关系数据库管理系统中用于提高查询效率的一种机制,但是索引的使用也存在一些限制。本文将详细介绍一些常见的限制情况,帮助开发人员更好地使用索引,提高查询效率。 限制...

    Oracle索引优化相关

    ### Oracle索引优化相关知识点详解 #### 一、基本索引概念 在Oracle数据库中,索引是提高数据检索速度的重要工具。通过查询`DBA_INDEXES`视图,可以获取到当前数据库中所有表的所有索引信息。需要注意的是,如果...

    oracle索引类型及扫描方式大整理

    Oracle 索引是数据库管理系统中提升数据查询速度的关键技术。本文主要介绍Oracle数据库中不同类型的索引及其内部结构,以及各种索引的工作原理和适用场景。 首先,Oracle使用平衡树(B-Tree)作为其索引的基础结构...

    Oracle 索引

    Oracle索引是数据库管理系统中用于加速数据检索的关键结构。它们的工作原理类似于书籍的目录,允许数据库系统快速定位和访问特定的数据行,而无需扫描整个表。Oracle提供了多种类型的索引,包括B树索引、位图索引、...

    Oracle 索引 使用方法

    Oracle 索引 使用方法,索引 使用原理, 索引 使用顺序过程

    Oracle优化全攻略一【Oracle 索引概念】.docx

    总的来说,理解Oracle索引的工作原理并适当使用,是实现数据库高效运行的关键。正确选择和设计索引,避免使用限制索引的因素,能显著提高查询性能,减少数据库响应时间,从而提升整个系统的性能。在实践中,需要结合...

    oracle 索引的原理

    oracle 索引的原理原理深入理解!

    Oracle索引优化

    Oracle索引优化是数据库性能调优的关键环节,它关乎到数据检索速度和整体系统的效率。在Oracle数据库中,索引的种类繁多,包括B*Tree索引、反向索引、降序索引、位图索引和函数索引等。理解并正确使用这些索引类型...

    oracle索引与分区索引介绍

    Oracle 索引与分区索引介绍 Oracle 索引与分区索引是 Oracle 数据库中两个重要的概念,它们都是为了提高查询性能和数据存储效率而设计的。在本文中,我们将详细介绍 Oracle 索引与分区索引的概念、特点、分类、创建...

    oracle索引,常见索引问题

    Oracle数据库中的索引是优化查询性能的关键工具,它允许快速定位和访问数据。常见的索引类型包括B*树索引和位图索引,每种都有其特定的应用场景和优势。 1. **B*树索引**:这是最常见的索引类型,类似于二叉树结构...

    Oracle 索引练习语句程序

    ### Oracle索引的基本概念 #### 1. 创建索引 索引是数据库中一种用于提高数据检索速度的数据结构。通过创建索引,可以显著提升查询性能。例如,在`student`表上创建一个基于`sno`列的索引: ```sql CREATE INDEX ...

Global site tag (gtag.js) - Google Analytics