【转】Oracle索引列NULL值引发执行计划该表的测试示例
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【转】Oracle索引列NULL值引发执行计划该表的测试示例,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3376字,纯文字阅读大概需要5分钟。
内容图文
![【转】Oracle索引列NULL值引发执行计划该表的测试示例](/upload/InfoBanner/zyjiaocheng/1116/3c4a9d339eeb41ef9f00bcce09cb22a9.jpg)
有时开发进行表结构设计,对表字段是否为空过于随意,出现诸如id1=id2,如果允许字段为空,因为Oracle中空值并不等于空值,有可能得到意料之外的结果。除此之外,最关键的是,NULL会影响oracle的执行计划。
以下为NULL影响执行计划的测试示例。
/*1.构建test表,其中create table方式建立的test表结构object_id非空*,走索引/
SELECT Count(*) FROM all_objects WHERE object_id IS NOT NULL; --41790笔
DROP TABLE test;
CREATE TABLE test AS SELECT * FROM all_objects WHERE object_id IS NOT NULL; ----41791笔
CREATE INDEX idx_test ON test(object_id);
ANALYZE TABLE test compute STATISTICS FOR TABLE FOR ALL indexes FOR ALL indexed COLUMNS;
EXPLAIN PLAN FOR SELECT Count(*) FROM test;
SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 3508397080
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_TEST | 41791 | 23 (5)| 00:00:01 |
--------------------------------------------------------------------------
/*2.改变test表结构,使得object_id字段为NULL,并更新一笔资料为NULL*,走全表/
ALTER TABLE test MODIFY object_id NUMBER NULL;
UPDATE test SET object_id=NULL WHERE ROWNUM=1;
COMMIT;
EXPLAIN PLAN FOR SELECT Count(*) FROM test;
SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 135 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 41791 | 135 (2)| 00:00:02 |
-------------------------------------------------------------------
/*3.对SQL指令增加条件过滤NULL之资料*,走索引/
EXPLAIN PLAN FOR SELECT Count(*) FROM test WHERE object_id IS NOT NULL;
SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 3508397080
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 23 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_TEST | 41791 | 163K| 23 (5)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
/*4.将上面改的那笔object_id is NULL的资料delete掉,再查看plan,依然走全表*/
DELETE FROM test WHERE object_id IS NULL;
COMMIT;
ANALYZE TABLE test compute STATISTICS FOR TABLE FOR ALL indexes FOR ALL indexed COLUMNS;
EXPLAIN PLAN FOR SELECT Count(*) FROM test;
SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 135 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 41790 | 135 (2)| 00:00:02 |
-------------------------------------------------------------------
综上,看起来,假如索引列的表结构非空,则会走索引;若索引列表结构可空,则无论是否存在null资料,都会走全表;可以用where 索引列 is not null过滤空资料,则还是会走索引。
转自:http://www.linuxidc.com/Linux/2012-09/69938.htm
原文:http://www.cnblogs.com/autumnlj/p/5754284.html
内容总结
以上是互联网集市为您收集整理的【转】Oracle索引列NULL值引发执行计划该表的测试示例全部内容,希望文章能够帮你解决【转】Oracle索引列NULL值引发执行计划该表的测试示例所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。