通过案例学调优之--Oracle中null使用索引
发表于：2025-01-19 作者：千家信息网编辑
千家信息网最后更新 2025年01月19日，通过案例学调优之--Oracle中null使用索引默认情况下，Oracle数据库，null在Index上是不被存储的，当在索引列以"is null"的方式访问时，无法使用索引；本案例，主要向大家演示如
千家信息网最后更新 2025年01月19日通过案例学调优之--Oracle中null使用索引
通过案例学调优之--Oracle中null使用索引
默认情况下，Oracle数据库，null在Index上是不被存储的，当在索引列以"is null"的方式访问时，无法使用索引；本案例，主要向大家演示如何在存在null的索引列上，使用"is null"访问索引。
案例分析：
1、建立表和普通索引
13:52:23 SCOTT@ prod >create table t2 (x int,y int);Table created.14:00:11 SCOTT@ prod >insert into t2 values (1,1);1 row created.Elapsed: 00:00:00.0414:00:21 SCOTT@ prod >insert into t2 values (1,null);1 row created.Elapsed: 00:00:00.0014:00:31 SCOTT@ prod >insert into t2 values (null,1);1 row created.Elapsed: 00:00:00.0014:00:37 SCOTT@ prod >insert into t2 values (null,null);1 row created.Elapsed: 00:00:00.0014:00:44 SCOTT@ prod >commit;Commit complete.Elapsed: 00:00:00.0414:06:41 SCOTT@ prod >select * from t2; X Y---------- ---------- 1 1 1 1 14:36:12 SCOTT@ prod >create index t2_ind on t2(x);Index created.14:49:38 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';INDEX_NAME TABLE_NAME NUM_ROWS------------------------------ ------------------------------ ----------T2_IND T2 3 在索引中只有3行，在最后一行字段全为null值，没有被存储！14:36:27 SCOTT@ prod >exec dbms_stats.gather_index_stats(user,'T2_IND');PL/SQL procedure successfully completed.14:37:29 SCOTT@ prod >select * from t2 where x=1; X Y---------- ---------- 1 1 1Execution Plan----------------------------------------------------------Plan hash value: 1173409066--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 8 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 8 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | T2_IND | 2 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("X"=1)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 519 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed 14:37:45 SCOTT@ prod >select * from t2 where x is not null; X Y---------- ---------- 1 1 1Execution Plan----------------------------------------------------------Plan hash value: 463061910--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 8 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 8 | 2 (0)| 00:00:01 ||* 2 | INDEX FULL SCAN | T2_IND | 2 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("X" IS NOT NULL)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 519 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed 14:38:00 SCOTT@ prod >select * from t2 where x is null; X Y---------- ---------- 1Execution Plan----------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 8 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T2 | 2 | 8 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("X" IS NULL)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 508 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed当x通过"is null"访问时，Oracle选择了"full table scan"方式。2、通过建立常量复合索引
14:38:55 SCOTT@ prod >create index t2_ind on t2(x,0);Index created.14:49:38 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';INDEX_NAME TABLE_NAME NUM_ROWS------------------------------ ------------------------------ ----------T2_IND T2 4 索引块上存储了表中所用的行。14:39:50 SCOTT@ prod >select * from t2 where x is null; X Y---------- ---------- 1Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------Plan hash value: 1173409066--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 8 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 8 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | T2_IND | 2 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("X" IS NULL)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 508 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed 对于x通过"is null"访问时，也能通过索引访问了！3、建立复合索引（其他列为null）
13:59:40 SCOTT@ prod >create index x_ind on t2(x,y);Index created.14:08:29 SCOTT@ prod >EXEC dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T2');PL/SQL procedure successfully completed.14:09:22 SCOTT@ prod >EXEC dbms_stats.gather_index_stats(ownname=>USER,indname=>'X_IND');PL/SQL procedure successfully completed.14:09:58 SCOTT@ prod >select index_name,num_rows from user_indexes where index_name='X_IND';INDEX_NAME NUM_ROWS------------------------------ ----------X_IND 314:10:50 SCOTT@ prod >select count(*) from t2; COUNT(*)---------- 4 14:11:28 SCOTT@ prod >set autotrace on14:12:33 SCOTT@ prod >select * from t2 where x=1; X Y---------- ---------- 1 1 1Execution Plan----------------------------------------------------------Plan hash value: 3708139238--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 8 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| X_IND | 2 | 8 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("X"=1)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 512 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed 14:12:47 SCOTT@ prod >select * from t2 where x is not null; X Y---------- ---------- 1 1 1Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------Plan hash value: 3776680409--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 8 | 1 (0)| 00:00:01 ||* 1 | INDEX FULL SCAN | X_IND | 2 | 8 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("X" IS NOT NULL)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 512 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed 14:13:08 SCOTT@ prod >select * from t2 where x is null; X Y---------- ---------- 1Execution Plan----------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 8 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T2 | 2 | 8 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("X" IS NULL)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 508 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed 如果，复合索引列其他列也为null，在查询使用'is null'条件时，仍然为"full table scan"。 14:13:52 SCOTT@ prod >select * from t2 where x=1 and y is null; X Y---------- ---------- 1Execution Plan----------------------------------------------------------Plan hash value: 3708139238--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| X_IND | 1 | 4 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("X"=1 AND "Y" IS NULL)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 471 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 14:16:16 SCOTT@ prod >select * from t2 where x is null and y=1; X Y---------- ---------- 1Execution Plan----------------------------------------------------------Plan hash value: 3708139238--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| X_IND | 1 | 4 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("X" IS NULL AND "Y"=1) filter("Y"=1)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 471 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed4、建立复合索引（其他列为 not null）
15:13:38 SCOTT@ prod >desc t2; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- X NUMBER(38) Y NUMBER(38) 15:13:43 SCOTT@ prod >alter table t2 modify (y NUMBER(38) not null);Table altered.15:14:01 SCOTT@ prod >desc t2; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- X NUMBER(38) Y NOT NULL NUMBER(38) 15:12:54 SCOTT@ prod >insert into t2 values (1,1);1 row created.Elapsed: 00:00:00.0215:13:02 SCOTT@ prod >insert into t2 values (null,1);1 row created.Elapsed: 00:00:00.0015:13:12 SCOTT@ prod >insert into t2 values (null,2);1 row created.Elapsed: 00:00:00.0015:13:36 SCOTT@ prod >commit;Commit complete.15:15:00 SCOTT@ prod >create index t2_ind on t2 (x,y);Index created.15:15:29 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);PL/SQL procedure successfully completed.15:16:09 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';INDEX_NAME TABLE_NAME NUM_ROWS------------------------------ ------------------------------ ----------T2_IND T2 315:17:20 SCOTT@ prod >set autotrace trace15:17:26 SCOTT@ prod >SELECT * from t2 where x is nullElapsed: 00:00:00.00Execution Plan----------------------------------------------------------Plan hash value: 2876512201---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 10 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| T2_IND | 2 | 10 | 1 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("X" IS NULL)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 510 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed在复合索引中，如果其他列为not null，则在"is null"条件下，仍然可以使用索引访问。结论：
对于普通的索引，null值不能进行索引的正确理解应该是，对于某一行，索引的所有列的值都是null值时，该行才不能被索引。
索引
存储
案例
普通
一行
方式
条件
只有
字段
常量
情况
所用
数据
数据库
案例分析
结论
分析
查询
演示
选择
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
如何增强中国互联网科技水平
邯郸微信软件开发费用
网络安全管理大格局
全国考研院校专业数据库网站
福建烟草行业网络安全供应商
税务网络安全股票
公共网络安全例子
域名邮箱服务器
王者荣耀是否关闭服务器
成都云南网络技术服务
腾讯云服务器正反向代理
第三方网络安全责任书
服务器电源加接线柱
武汉优美网络技术怎么样
前端项目怎么连接数据库
mcice服务器被炸现场
sql如何分批删除数据库
浙江服务器空调维修价格
电子支付网络安全分析
深圳网络安全维护公司名单
数据库怎么存入map集合
配置邮箱时无法安全连接到服务器
网络安全管理大格局
软件开发员工可以带走源码吗
数据库语句新建查询
老牌的重庆虚拟主机服务器
高级软件开发工程师待遇如何
在线网络安全测试题库
移动互联软件开发
软件开发学习流程6
