oracle sql中涉及is null时如何优化(索引创建和直方图)
来源:互联网 发布:大数据相关的岗位 编辑:程序博客网 时间:2024/05/08 19:39
适用场景
试想下面的sql
Select*
fromBD_INVMANDOC_bak t
where t.negallowedis null
or t.negallowed='N'
如果negallowed列只有两个值:Y和N,并且存在NULL
那么我们在negallowed列创建一个索引,这个语句仍然不能走索引,原因为索引中不存储null值。
BD_INVMANDOC_bak表106万数据,其中5万行值为N,100万行为Y,其余为null。
如何走索引
需要创建一个包含虚拟列的复合索引,这样可以实现索引中包含negallowed列的null值
创建索引如下:
CREATEINDEX ID_BD_INVMANDOC_bak_negallowed ON BD_INVMANDOC_bak (negallowed,1);
查询执行计划变为了走索引:
SQL> set autotrace trace
SQL>
SQL>
SQL> select-- /*+ INDEX(T ID_BD_INVMANDOC_bak_negallowed) */
2 *
3 from BD_INVMANDOC_bak t where
4 t.negallowed is null or
5 t.negallowed ='N'
6 ;
61399 rows selected.
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows |
Bytes | Cost |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 58559 |
9492K| 1774 |
| 1 | CONCATENATION | | |
| |
| 2 | TABLE ACCESS BY INDEX ROWID| BD_INVMANDOC_BAK | 49153 |
7968K| 1475 |
| 3 | INDEX RANGE SCAN | ID_BD_INVMANDOC_BAK_NEGALLOWED | 49153 |
| 113 |
| 4 | TABLE ACCESS BY INDEX ROWID| BD_INVMANDOC_BAK | 9406 |
1524K| 299 |
| 5 | INDEX RANGE SCAN | ID_BD_INVMANDOC_BAK_NEGALLOWED | 9867 |
| 25 |
--------------------------------------------------------------------------------
---------------
分析:
当索引中包含了查询列存在null的时候,语句可以顺利走索引,语句执行代价也明显降低了。
但是通过进一步测试,发现在negallowed ='N'或negallowed ='Y'时均走索引,执行计划在negallowed ='Y'时非最优,所以需要采取直方图信息,以便执行计划更准确。
直方图histogram的影响
这个表的negallowed列只包含Y和N,并且存在null,那么直方图是不是对这列存在影响呢
使用如下语句收集,没有直方图
execdbms_stats.gather_table_stats(user,'BD_INVMANDOC_BAK',method_opt => 'FOR ALLINDEXED COLUMNS SIZE 1');
执行计划:
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0| SELECT STATEMENT | | 530K| 84M| 7750 |
| 1| TABLE ACCESS FULL| BD_INVMANDOC_BAK| 530K| 84M| 7750 |
----------------------------------------------------------------------
可以看到执行计划为全表扫描
统计直方图
execdbms_stats.gather_table_stats(user,'BD_INVMANDOC_BAK',method_opt => 'FOR ALLINDEXED COLUMNS SIZE auto');
执行计划:
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
---------------
| Id | Operation |Name |Rows |
Bytes | Cost |
--------------------------------------------------------------------------------
---------------
| 0| SELECT STATEMENT | | 64950 |
10M| 1969 |
| 1| CONCATENATION | | |
| |
| 2| TABLE ACCESS BY INDEX ROWID|BD_INVMANDOC_BAK | 55605 |
9014K| 1670 |
| 3| INDEX RANGE SCAN | ID_BD_INVMANDOC_BAK_NEGALLOWED |55605 |
| 128 |
| 4| TABLE ACCESS BY INDEX ROWID|BD_INVMANDOC_BAK | 9345 |
1514K| 299 |
| 5| INDEX RANGE SCAN | ID_BD_INVMANDOC_BAK_NEGALLOWED| 9867 |
| 25 |
--------------------------------------------------------------------------------
---------------
--执行计划走索引
总结
当一个查询中涉及IS NULL的情况,我们需要在此列上创建适合的复合索引,如果没有好的候选列,可以添加虚拟列创建复合索引,以达到语句执行计划走索引的目的。
当这一列的值只有少数几个唯一值,并且每个值涉及的行数多少严重倾斜时,建议收集直方图,以便达到正确走索引的目的。
- oracle sql中涉及is null时如何优化(索引创建和直方图)
- ORACLE-017:SQL优化-is not null和nvl
- SQL SERVER 中is null 和 is not null 将会导致索引失效吗?
- oracle索引和sql优化
- ORACLE查询优化之is null和is not null优化
- oracle之 is null 优化
- oracle高级查询之数据优化(1)------如何创建效率高sql-建立索引
- oracle is null 和is not null
- oracle 性能优化操作六: 去掉Where子句中的IS NULL和IS NOT NULL
- oracle 性能优化操作六: 去掉Where子句中的IS NULL和IS NOT NULL
- Oracle优化器和直方图
- Oracle is null不走索引问题
- IS NULL和=NULL的区别(SQL)
- is null 和 is not null 都不能利用 索引
- 如何在SQL Server和Oracle中创建job
- SQL 如何创建索引
- Oracle性能优化读书笔记(2)-SQL语句和索引优化
- oracle sql 优化-索引(摘二)
- Android 用XML 设置 背景色
- Android—AppWidget
- cmake 的编写
- 市场调查公司前10强
- Linux socket recv send
- oracle sql中涉及is null时如何优化(索引创建和直方图)
- 使用backgroundPosition来定位背景图片
- 2013移动开发工具盘点:最火原型设计工具
- C语言指针强制类型转换
- Java 排序算法之冒泡排序小归纳
- Hadoop --Aggregate 包使用 Streaming
- Mysql隔离级别
- 禅道程序员的10条原则
- WinDbg调试器使用