1861人阅读 B树索引我们可以把它看成是书的目录，在这个目录中主要记录的是索引所对应的表列的值和这个值所对应的ROWID。在通常情况下，我们在表中增加索引的目的是增加表的查询性能，但是有几种情况，即使你在表中加入了索引，Oracle也不会执行索引。下面来说明一下其中一种不走索引的情况--null值不入索引。 以下的说明索引只针对B树索引，对于位图索引，是可以记录NULL值的。 首先需要说明的是，有的人会认为Oracle的表中只要有一列（表中的某个属性或字段）没有非空约束，并且该列中存在NULL值，那么对该列（表中的某个属性或字段）做任何查询都不走索引，这事不对的，请看下面的例子。 下面我们来创建一张text_tab表，并为该表的comm字段增加普通B树索引。 SQL> create table text_tab as select * from emp; Table created SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 14 rows selected SQL> create index idx_comm on text_tab(comm); Index created 再来看一下对text_tab的comm字段的查询，之后查看其执行计划。 SQL> alter system flush shared_pool; --此操作在生产环境中慎用 System altered SQL> select count(1) from text_tab where comm = '300'; COUNT(1) ---------- 1 SQL> select sql_text, sql_id, hash_value, child_number from v$sql where sql_text like '%select count(1) from text_tab%'; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER -------------------------------------------------------------------------------- ------------- ---------- ------------ select sql_text, sql_id, hash_value, child_number from v$sql where sql_text lik 1nm4rhvwjndut 4179244889 0 select count(1) from text_tab where comm = '300' 8ykxwd1c1v6zj 1478335473 0 SQL> select * from table(dbms_xplan.display_cursor('8ykxwd1c1v6zj', 0, 'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 8ykxwd1c1v6zj, child number 0 ------------------------------------- select count(1) from text_tab where comm = '300' Plan hash value: 4008428181 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX RANGE SCAN| IDX_COMM | 1 | 13 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / TEXT_TAB@SEL$1 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Outline Data ------------- ... 48 rows selected 从上面测试中可以看出，Oracle执行了索引，执行计划为索引范围扫描。这说明，在表中的某一列中如果存在NULL值，并不是对此列的任何操作Oracle都不走索引。 下面举一个Oracle因为NULL值不走索引的例子。 SQL> alter system flush shared_pool; --此操作在生产环境中慎用 System altered SQL> select * from text_tab where comm is null; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 rows selected SQL> select sql_text, sql_id, hash_value, child_number from v$sql where sql_text like '%select * from text_tab where comm is%'; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER -------------------------------------------------------------------------------- ------------- ---------- ------------ select sql_text, sql_id, hash_value, child_number from v$sql where sql_text lik a4dd5ksa9uf77 345848039 0 select * from text_tab where comm is null 01g489ph8yjcq 1620002198 0 SQL> select * from table(dbms_xplan.display_cursor('01g489ph8yjcq', 0, 'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 01g489ph8yjcq, child number 0 ------------------------------------- select * from text_tab where comm is null Plan hash value: 2822424504 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| TEXT_TAB | 10 | 870 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / TEXT_TAB@SEL$1 Outline Data ------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ... 49 rows selected 通过上面的执行计划可以看出，CBO对该条查询选择的执行计划为全表扫描。这种情况就是我们所说的，NULL值不入索引的情况。 首先来看为什么Oracle的常规B树索引不记录NULL值。 1、索引是有序的。当一个空值进入索引时，无法确定其在索引中的位置。 2、空值与空值不相等。当检索一个空值时，由于空值与空值并不相等，所以，无法在索引中找到期望的空值索引。 但是在某些情况下，由于业务规定或者为了开发便利，我们无法避免这种NULL值的情况，那么如何在无法避免NULL值的情况下，去避免不必要的全表扫描，让其走索引呢？ PS：这里插一句题外话，某些情况下，全表扫描的效率不一定会低于走索引。甚至有些情况下会高于执行索引。因为在Oracle执行索引的时候，会先去索引中查找是否有对应的记录，如果没有找到可以返回的记录，它会找到需要返回记录的rowid,去表块中读取需要返回的值。但是如果一张表有1万条记录，而我们的查询结果大约9000条，这种情况下，先查索引再回表的效率要远远低于全表扫描这样直接去扫描数据块的效率。全表扫描的最大弊病在于全表扫描在同一查询中，执行效率是不可控的，它的性能会随着表中数据的增加而下降。 下面来说明一下再NULL值无法改变的情况下，如何让CBO选择执行索引的执行计划。大概方法有如下几种： 1、通过NVL这类的函数来解决。 2、通过复合索引和非空约束来解决。 3、通过复合索引增加伪列来解决。这种方式是非常推荐的。 下面来分别说明以下这三种解决方案。 1、使用NVL类似函数解决 SQL> drop index idx_comm; --首先删除之前建立的索引 Index dropped SQL> create index idx_comm on text_tab(nvl(comm, -1)); --重新建立索引，指定如果comm为NULL的情况下，值为-1 Index created SQL> select * from text_tab where nvl(comm, -1) = -1; --将is null改为这种写法 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 rows selected SQL> select sql_text, sql_id, hash_value, child_number from v$sql where sql_text like '%select * from text_tab where nvl%'; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER -------------------------------------------------------------------------------- ------------- ---------- ------------ select sql_text, sql_id, hash_value, child_number from v$sql where sql_text lik gg3m6han59y4d 2824140941 0 select * from text_tab where nvl(comm, -1) = -1 7yywfqzkk2rqh 3844169424 0 SQL> select * from table(dbms_xplan.display_cursor('7yywfqzkk2rqh', 0, 'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 7yywfqzkk2rqh, child number 0 ------------------------------------- select * from text_tab where nvl(comm, -1) = -1 Plan hash value: 386593135 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | TABLE ACCESS BY INDEX ROWID| TEXT_TAB | 1 | 100 | 2 (0)| 00 |* 2 | INDEX RANGE SCAN | IDX_COMM | 1 | | 1 (0)| 00 -------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- .... 52 rows selected 2、通过建立复合索引和非空约束来解决 SQL> drop index idx_comm; Index dropped SQL> create index idx_comm on text_tab(comm, empno); Index created SQL> alter table text_tab modify empno not null; Table altered SQL> alter system flush shared_pool; System altered SQL> select * from text_tab where comm is null; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 rows selected SQL> select sql_text, sql_id, hash_value, child_number from v$sql where sql_text like '%select * from text_tab where comm%'; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER -------------------------------------------------------------------------------- ------------- ---------- ------------ select sql_text, sql_id, hash_value, child_number from v$sql where sql_text lik dtny2zs959kwm 307547027 0 select * from text_tab where comm is null 01g489ph8yjcq 1620002198 0 SQL> select * from table(dbms_xplan.display_cursor('01g489ph8yjcq', 0, 'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 01g489ph8yjcq, child number 0 ------------------------------------- select * from text_tab where comm is null Plan hash value: 386593135 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | TABLE ACCESS BY INDEX ROWID| TEXT_TAB | 10 | 870 | 2 (0)| 00 |* 2 | INDEX RANGE SCAN | IDX_COMM | 1 | | 1 (0)| 00 -------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- .... 53 rows selected 3、通过复合索引增加伪列的方式解决。 这种方式的大体思路与上面第二个的思路相似。但是这种方式是我比较推荐的解决方式。因为如果在复合索引中增加一个其他列，而不是一个常量，会降低DML的操作性能。原因是在Oracle数据库中建立索引是有代价的。增加索引带来的负面影响就是会影响DML的性能，insert的时候，Oracle会先到索引中创建KV键值，在插入数据。delete也一样，会先删除索引键值，再删除数据。update的时候，如果update的是索引键值列，性能也会先下降，如果不是，性能不收影响。 至于create index idx_comm on text_tab(comm, 0);中，创建常量0也是有原因的，因为0在Oracle数据库中只占用1个字节。如果设置其他常量，比方说1，他在Oracle中占用2个字节。 SQL> drop index idx_comm; Index dropped SQL> create index idx_comm on text_tab(comm, 0); Index created SQL> alter system flush shared_pool; System altered SQL> select * from text_tab where comm is null; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 rows selected SQL> select sql_text, sql_id, hash_value, child_number from v$sql where sql_text like '%select * from text_tab where comm%'; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER -------------------------------------------------------------------------------- ------------- ---------- ------------ select sql_text, sql_id, hash_value, child_number from v$sql where sql_text lik dtny2zs959kwm 307547027 0 select * from text_tab where comm is null 01g489ph8yjcq 1620002198 0 SQL> select * from table(dbms_xplan.display_cursor('01g489ph8yjcq', 0, 'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 01g489ph8yjcq, child number 0 ------------------------------------- select * from text_tab where comm is null Plan hash value: 386593135 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | TABLE ACCESS BY INDEX ROWID| TEXT_TAB | 10 | 870 | 2 (0)| 00 |* 2 | INDEX RANGE SCAN | IDX_COMM | 1 | | 1 (0)| 00 -------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- ... 52 rows selected   发布于2019-05-13著作权归作者所有 相关推荐更多 Oracle复合索引与空值的索引使用问题小结 weixin_38734037 0 下载 oracle 空值 走索引吗,Oracle中NULL值与索引 twxy 534 阅读  0 评论 oracle null 不走索引,搜索条件设置为Is Null一定不走索引吗？ weixin_39797324 230 阅读  0 评论 最新发布 oracle is null走索引,oracle sql中涉及is null时如何优化（索引创建和直方图） | 学步园... 长亭科技 59 阅读  0 评论 热门推荐 ORACLE 不走索引（失效）的原因以及解决办法 H7_N18 2万+ 阅读  1 评论 oracle nvl不走索引,极度困惑：where条件中，nvl放到=号右边也会阻止优化程序使用索引？... 安尼迪 799 阅读  0 评论 让Oracle NULL和NOT NULL使用B数索引_cuiwangxie1183的... 在OLTP系统中,通过合理的创建和使用索引,可以大大提高sql语句的执行效率。但是B树索引有一个缺点就是,索引的叶子节点中不会存放null字段的值,也就表明如果sql语句中使用了诸如is null或是is not null,那么oracle通常是不使用B树索引的。 ORACLE 查询不走索引的原因分析,解决办法通过强制索引... 索引失效。 基于cost成本分析(Oracle因为走全表成本会更小):查询小表,或者返回值大概在10%以上; 解决办法: 在这种条件下 oracle会认为索引更占资源,就默认不走索引了。这种情况如果觉得索引快的,通过强制索引提高查询速度 ... SQL优化:NULL值与索引的使用_LSSSSSS的专栏 NULL是数据库中特有的数据类型,当一条记录的某个列为NULL,则表示这个列的值是未知的、是不确定的。简单的说,由于NULL存在着无数的可能,因此两个NULL不是相等的关系,同样也不能说两个NULL就不相等,或者比较两个NULL的大小,这些操作... oracle 不指定类型_b树索引,Oracle最常用的B树索引的5... 今天我们讨论下Oracle数据库中最常用的B树索引,首先我们先来看一下Oracle数据库里B树索引的结构。 从图中我们可以看出,Oracle数据库里的B树索引就好像一颗倒长的树,它包含两种类型的数据块。 一种是索引分支块(L1-1,L1-2),另一种是... 讨论B树索引中的 is null/is not null_人生没有后悔的博客 讨论B树索引中的 is null/is not null 为什么在查询中使用IS NULL 或IS NOT NULL同样会限制索引的使用 简答的说因为索引键值不会存储空值 Oracle的CBO并不会因为SQL语句中指定了IS NOT NULL或IS NULL操作就不再使用索引。CBO选择... Oracle技术之索引与Null值对于Hints及执行计划的影响_w... 在此情况下即使使用Hints,Oracle也不会使用索引,其根本原因就是因为Null值的存在. 我们看以下测试. 在username字段为Not Null时,Index Hints可以生效. 当索引字段允许为Null时,Oracle放弃此索引: ... NULL 值与索引(一) anjichan4261 132 阅读  0 评论 MySQL is null真的不走索引吗？ 码农的进阶之路 1691 阅读  0 评论 让IS NULL走起索引 呆瓜呆呆 1万+ 阅读  0 评论 MySQL索引 犀牛_2046 67 阅读  0 评论