让优化器用上可为空的索引.
来源:互联网 发布:农资王软件豪华版 编辑:程序博客网 时间:2024/04/29 04:58
首先创建一张测试表:
CREATE TABLE FOO(
USERID NUMBER(20) NULL,
USERNAME VARCHAR2(100),
USERDEPT NUMBER(20),
USERDEPTNAME VARCHAR2(100)
)
/
模拟上10万条数据进去:
BEGIN
FOR I IN 0..100000 LOOP
INSERT INTO FOO
VALUES
(I,'ZIWEN'||i,MOD(I,30),'USERDEPTNAME'||MOD(I,30));
END LOOP;
IF MOD(I,100) =0 THEN
COMMIT;
END IF;
END;
/
在可以为NULL的USERID上面增加一个索引:
CREATE INDEX INDEX_FOO_USERID ON FOO(USERID);
进行表分析:
ANALYZE TABLE FOO
COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS;
执行下面的语句,按照道理而言,应该走INDEX,INDEX RANGE SCAN,实际上也是走的索引范围扫描。
SELECT USERID FROM FOO WHERE USERID = 100;
修改其中的某些USERID为空再试一下,仍旧走的是索引范围扫描。
UPDATE FOO SET FOO.USERID = NULL WHERE MOD(USERID,1000)=0;
给查询的SQL增加HINT提示,看一下执行计划:
SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO;
执行计划如下:
Plan hash value: 1245013993
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 3515K| 171 (1)| 00:00:03 |
| 1 | TABLE ACCESS FULL| FOO | 100K| 3515K| 171 (1)| 00:00:03 |
--------------------------------------------------------------------------
为什么即使给了HINT也不走索引INDEX_FOO_USERID呢?是因为USERID允许为空么?
SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO WHERE USERID IS NOT NULL;
执行计划如下:
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 3515K| 756 (1)| 00:00:10 |
| 1 | TABLE ACCESS BY INDEX ROWID| FOO | 100K| 3515K| 756 (1)| 00:00:10 |
|* 2 | INDEX FULL SCAN | INDEX_FOO_USERID | 100K| | 224 (1)| 00:00:03 |
------------------------------------------------------------------------------------------------
增加了IS NOT NULL之后,索引走了INDEX_FOO_USERID。
换一个角度,如果让USERID列,设置为非空的话,会不会走索引呢?
UPDATE FOO SET FOO.USERID =990000 WHERE USERID IS NULL;
ALTER TABLE FOO MODIFY(USERID NOT NULL);
然后再执行刚刚的语句,只是这个时候不加IS NOT NULL:
SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO;
这个时候的执行计划:
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 3515K| 756 (1)| 00:00:10 |
| 1 | TABLE ACCESS BY INDEX ROWID| FOO | 100K| 3515K| 756 (1)| 00:00:10 |
| 2 | INDEX FULL SCAN | INDEX_FOO_USERID | 100K| | 224 (1)| 00:00:03 |
------------------------------------------------------------------------------------------------
发现通过HINT,优化器走了索引。
将索引设置为NULL,但是里面不存在NULL值的时候:
ALTER TABLE FOO MODIFY(USERID NULL);
SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO;
执行计划如下:
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 3515K| 171 (1)| 00:00:03 |
| 1 | TABLE ACCESS FULL| FOO | 100K| 3515K| 171 (1)| 00:00:03 |
--------------------------------------------------------------------------
这里得到的一个结论就是:
索引能不为空就不为空,也可以通过在索引列加上IS NOT NULL让优化器使用为空的索引。
- 让优化器用上可为空的索引.
- 无法在表 'Test' 中可为空的列上定义 PRIMARY KEY 约束
- 可为空的日期控件
- ssh-agent 转发:让服务器用我们电脑上的 SSH Key 验证身份
- 让dropdownlist的值为空
- SQL优化(SQL TUNING)可大幅提升性能的实战技巧之一——让计划沿着索引跑
- 外键值可为空
- 外键值可为空
- 关于DateTime?(可为空),Decimal?类型的格式化方法
- 为什么我创建的索引总为空?郁闷
- oracle的索引字段可以设置为空?
- 索引器和可空类型
- 索引上优化细节
- 如何让table表的null列由不走索引变为可走索引
- MySQL中可为空的字段设置为NULL还是NOT NULL
- TimerTask,Timer,Handler。监听edittext的内容是否为空, 为空:保存按钮不可点击 不为空:保存按钮可点击
- 无法在为空的列上定义主键约束
- windows上loadrunner录制脚本为空的解决方法
- 图形学网站
- 网络通信
- C++ STL容器线程安全的模版类
- java web 文件上传
- 学习《C Primer Plus》---存储类和函数
- 让优化器用上可为空的索引.
- 基于C编程的Gstreamer简单的播放器实现的思路(非命令行)
- Java中的JavaScript处理方法
- iOS开发常用的宏
- 对于Oracle数据库全文索引设置步骤
- 【linux】前后台进程切换
- 在Unix服务器上设置Oracle全文检索
- 【源代码】Image Deformation Using Moving Least Squares算法的实现
- emacs快捷键总结