- 浏览: 36590 次
- 性别:
- 来自: 武汉
最新评论
唯一索引:unique
对于唯一索引来说,由于null不等于null,所以就算一列中有多个null ,还是可以建立唯一所有的。但是这样不能用到索引,因为有null值。
但是对添加唯一复合索引时,是不能插入null的。
- -->从上面的情形可知,
- -->基于单列的唯一索引,可以多次插入null值,但其索引上并不存储null值。
- -->基于多列的复合索引,尽管全为null值的行可以多次插入,但不全为null的重复行则不能被插入(注,非唯一复合索引不存在此限制,此处不演示)。
- -->基于多列的复合索引,对于全为null值的索引值也不会被存储。如上面的情形,尽管插入了5条记录,复合索引中只存储了3条。
- -->注:对于唯一性约束,null值不等于null值,同样(null,null)也不等同于(null,null),所以上面的两次null能够被插入。
二、null值与执行计划
- scott@ORCL> set autot trace exp;
- scott@ORCL> select * from t1 where id is null;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3617692013
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 5 | 5 | 3 (0)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| T1 | 5 | 5 | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("ID" IS NULL)
- -->从上面的测试可知,由于null值是不被存储的,因此当使用id is null作为谓词时,走了全表扫描
- scott@ORCL> select * from t1 where id is not null;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 796913935
- ---------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 1 | 0 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 0 (0)| 00:00:01 |
- |* 2 | INDEX FULL SCAN | I_T1_ID | 1 | | 0 (0)| 00:00:01 |
- ---------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("ID" IS NOT NULL)
- -->从上面的测试可知,尽管当前表上id列上的所有值都为null,但不排除后续记录插入的id不为null的列。
- -->故当使用id is not null作为谓词时,此时执行计划中走了索引全扫描。
- -->下面来看看复合索引的情形
- scott@ORCL> select * from t1 where val is null;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3617692013
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 2 | 2 | 3 (0)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| T1 | 2 | 2 | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("VAL" IS NULL)
- scott@ORCL> select * from t1 where val is not null;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1931510411
- --------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 3 | 3 | 1 (0)| 00:00:01 |
- |* 1 | INDEX FULL SCAN | I_T1_ID_VAL | 3 | 3 | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("VAL" IS NOT NULL)
- -->对于复合唯一索引的情形,当使用单列且非前导列谓词时,使用is null与 is not null等同于单列唯一索引的情形。
- -->即原理也是一样的,val is null走全表扫描而val is not null走索引。因为null值不会被存储。
- -->下面看看两个列都作为谓词的情形
- scott@ORCL> select * from t1 where id is null and val is not null;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1040510552
- --------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 3 | 3 | 1 (0)| 00:00:01 |
- |* 1 | INDEX RANGE SCAN| I_T1_ID_VAL | 3 | 3 | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("ID" IS NULL)
- filter("VAL" IS NOT NULL)
- -->从上面的测试可知,尽管两个谓词列上都存在索引,一个为单列唯一索引,一个为复合唯一索引。Oracle 选择了复合索引I_T1_ID_VAL。
- scott@ORCL> select * from t1 where id is not null and val is null;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 796913935
- ---------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 1 | 0 (0)| 00:00:01 |
- |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 0 (0)| 00:00:01 |
- |* 2 | INDEX FULL SCAN | I_T1_ID | 1 | | 0 (0)| 00:00:01 |
- ---------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("VAL" IS NULL)
- 2 - filter("ID" IS NOT NULL)
- -->同样的情形,谓词的顺序与复合索引定义的顺序一样,只不过第一个谓词为id is not null,而第二个谓词为val is null。
- -->此时Oracle 选择了单列唯一索引I_T1_ID
- -->看到此,不知道大家是否已明白,即哪个列为is not null,则会使用该列上的索引,原因还是那句话,索引不存储null值。
- -->对于颠倒id列与val列以及id,val列为null或not null的其他不同组合情形不再演示,其执行计划类似。
三、使用is null走索引的情形
- scott@ORCL> set autot off;
- --删除原有表上的null值记录
- scott@ORCL> delete from t1 where val not in('Y','N') or val is null;
- 3 rows deleted.
- scott@ORCL> update t1 set id=1 where val='Y';
- 1 row updated.
- scott@ORCL> update t1 set id=2 where val='N';
- 1 row updated.
- scott@ORCL> commit;
- Commit complete.
- -->对原有记录更新后的情形
- scott@ORCL> select * from t1;
- ID VAL
- ---------- ------------------------------
- 1 Y
- 2 N
- scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);
- PL/SQL procedure successfully completed.
- -->修改表列id使之具有not null约束的特性
- scott@ORCL> alter table t1 modify(id not null);
- Table altered.
- scott@ORCL> set autot trace exp;
- scott@ORCL> select * from t1 where id is null;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3160894736
- --------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 5 | 0 (0)| |
- |* 1 | FILTER | | | | | |
- | 2 | INDEX FULL SCAN| I_T1_ID_VAL | 2 | 10 | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter(NULL IS NOT NULL)
- -->从上面的执行计划中可知,当表t1列id上具有not null 约束时,此时使用id is null选择了索引范围扫描
- -->下面来看看列val is null 的情形
- scott@ORCL> select * from t1 where val is null;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 48744011
- ------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
- |* 1 | INDEX FAST FULL SCAN| I_T1_ID_VAL | 1 | 5 | 2 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("VAL" IS NULL)
- -->尽管val列上允许null值存在,但由于列id上具有not null 约束,且id列与val列存在复合唯一索引,因此此时选择了索引快速全扫描
- -->其余不同组合情形大致相同,不再演示
- -->为表t1新增一条val为null的记录
- scott@ORCL> insert into t1 select 3,null from dual;
- 1 row created.
- scott@ORCL> commit;
- Commit complete.
- scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);
- PL/SQL procedure successfully completed.
- -->下面的查询中可以看出尽管只有列id有not null约束,当所有的索引值都被存储
- scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys
- 2 from user_indexes where table_name='T1';
- INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS NUM_ROWS STATUS DISTINCT_KEYS
- --------------- ---------- ---------- ----------- ---------- -------- -------------
- I_T1_ID NORMAL 0 1 3 VALID 3
- I_T1_ID_VAL NORMAL 0 1 3 VALID 3
- -->Author : Robinson Cheng -->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)下的情形,不同的优化器版本可能会有偏差。
无论是单列唯一索引或复合唯一索引,对于可以为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)下的情形,不同的优化器版本可能会有偏差。
发表评论
-
ORA-27452: %s is an invalid name for a database object
2013-01-18 14:02 2939Trying to create this job: ... -
oracle日期时间型timestamp
2013-01-06 15:01 18141、获取系统时间的语句(ssxff6获取小数点后面六位) se ... -
索引表
2012-10-08 16:11 10181、索引表 索引表的 ... -
to_char()
2012-09-17 10:12 902SELECT to_char(0.00,'FM99999999 ... -
“SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色”解决方案
2012-08-29 18:45 1156以下操作请用sql*plus工具或sqlplus dos工具做 ... -
oracle中不用密码以dba身份登录
2012-08-28 18:18 2123在oracle中,sqlplus下,我们可以不用密码就以dba ... -
oracle中date数据类型插入格式
2012-08-28 18:03 1306以插入今天的日期为例,TAB_NAME为表名,DATE_COL ... -
Oracle流水号如何生成
2012-08-20 14:53 979有多种方法,比较常见的是: 1,建立序列(sequence) ...
相关推荐
本文将深入探讨Oracle索引的原理、创建方法,并结合SQL优化策略,帮助你提升数据库查询速度,降低资源消耗。 首先,我们要理解索引的基本概念。在数据库中,索引类似于书籍的目录,它为数据提供快速访问的途径。...
Oracle 索引是数据库管理系统中提升数据查询速度的关键组件,尤其在大型企业级应用中,选择合适的索引类型对于数据库性能至关重要。本篇文章将深入探讨Oracle中的B*Tree索引、反向索引、降序索引、位图索引和函数...
总的来说,Oracle索引机制的深入理解对于数据库性能调优、查询优化和系统设计都具有重要意义。正确使用索引可以显著提高查询速度,降低响应时间,提升用户体验,但同时也需要平衡索引对写操作的影响,以确保系统的...
### Oracle索引类型详解 #### 一、B\*Tree索引:数据检索的基石 在Oracle数据库中,B\*Tree索引是最常见的索引结构,也是默认创建的索引类型。它基于二叉树原理,由分支块(branch block)和叶块(leaf block)构成,...
Oracle索引被限制的一些情况 Oracle索引是关系数据库管理系统中用于提高查询效率的一种机制,但是索引的使用也存在一些限制。本文将详细介绍一些常见的限制情况,帮助开发人员更好地使用索引,提高查询效率。 限制...
### Oracle索引优化相关知识点详解 #### 一、基本索引概念 在Oracle数据库中,索引是提高数据检索速度的重要工具。通过查询`DBA_INDEXES`视图,可以获取到当前数据库中所有表的所有索引信息。需要注意的是,如果...
Oracle 索引是数据库管理系统中提升数据查询速度的关键技术。本文主要介绍Oracle数据库中不同类型的索引及其内部结构,以及各种索引的工作原理和适用场景。 首先,Oracle使用平衡树(B-Tree)作为其索引的基础结构...
Oracle索引是数据库管理系统中用于加速数据检索的关键结构。它们的工作原理类似于书籍的目录,允许数据库系统快速定位和访问特定的数据行,而无需扫描整个表。Oracle提供了多种类型的索引,包括B树索引、位图索引、...
Oracle 索引 使用方法,索引 使用原理, 索引 使用顺序过程
总的来说,理解Oracle索引的工作原理并适当使用,是实现数据库高效运行的关键。正确选择和设计索引,避免使用限制索引的因素,能显著提高查询性能,减少数据库响应时间,从而提升整个系统的性能。在实践中,需要结合...
oracle 索引的原理原理深入理解!
Oracle索引优化是数据库性能调优的关键环节,它关乎到数据检索速度和整体系统的效率。在Oracle数据库中,索引的种类繁多,包括B*Tree索引、反向索引、降序索引、位图索引和函数索引等。理解并正确使用这些索引类型...
Oracle 索引与分区索引介绍 Oracle 索引与分区索引是 Oracle 数据库中两个重要的概念,它们都是为了提高查询性能和数据存储效率而设计的。在本文中,我们将详细介绍 Oracle 索引与分区索引的概念、特点、分类、创建...
Oracle数据库中的索引是优化查询性能的关键工具,它允许快速定位和访问数据。常见的索引类型包括B*树索引和位图索引,每种都有其特定的应用场景和优势。 1. **B*树索引**:这是最常见的索引类型,类似于二叉树结构...
### Oracle索引的基本概念 #### 1. 创建索引 索引是数据库中一种用于提高数据检索速度的数据结构。通过创建索引,可以显著提升查询性能。例如,在`student`表上创建一个基于`sno`列的索引: ```sql CREATE INDEX ...