My Life My Dream!

守信 求实 好学 力行
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

       月底高峰期,对一个典型项目抽查分析时,发现了一个超级慢、全表扫描的SQL,语句很简单,AWR中赫然在列,在我统计的截止时间内还没有结束。。。

image

 

使用v$active_session_history进一步确认:该SQL执行了接近3个小时!

image


获取SQL的完整信息,发现该语句并不复杂,但看到 IS NULL 似乎就明白了问题所在,索引失效、全表扫描。。。

虽然该表上已经创建有 period和year两列的索引,但选择性太低了,优化器还是决定使用 Table Access Full,即使在该索引的后面增加EXPENSETYPE列,也会因为该列使用的is null条件而失效。貌似陷入了绝境,难道就没有任何转机了?

image


仔细考虑一下,该语句真正更新的数据量其实非常少,也就是说绝大部分数据行的EXPENSETYPE列应该是有值的。。。,马上确认一下,果然该表1.2亿的数据,从统计信息看EXPENSETYPE的唯一性虽然不高,但为空的数据量基本可以忽略不计。如果Year或者Period列的属性是非空的,就可以借助它们的组合索引(或者调整现有索引列的顺序),从而进行索引查找了!!!

image


经过确认,Period和Year果然被定义为非空列,呵呵,天助我也。。。

image


果断创建索引试试,经过近20分钟的等待,哈哈,成功了!!!

CREATE INDEX IDX_ROFYJTPZ2017_EXPENSETYPE ON ROFYJTPZ2017(EXPENSETYPE, PERIOD) NOLOGGING ONLINE PARALLEL 8;
ALTER INDEX  IDX_ROFYJTPZ2017_EXPENSETYPE NOPARALLEL;

image


总结:

  1. B树索引本身不存储键值全为NULL的记录,所以 IS NULL 的操作是无法使用单列索引的;但复合索引(例如本场景,因为后续列非空、有值)可能会存储一部分NULL值,进而使得 IS NULL 也是可以使用索引的,还是要看Cost最小,而非其他;
  2. 当然,这个问题最好的建议是将EXPENSETYPE列在设计上值为非空,并设置“NULL”、“NONE”之类的初始值。