对SCOTT用户EMP表的DEPTNO和ENAME字段建了一个复合索引. 在SQL的WHERE子句中用了一个OR条件, 看Plan:
SQL> SELECT /*+ first_rows no_expand */ * FROM EMP
2 WHERE deptNO=10 AND (ENAME IS NULL OR ENAME > 'A');
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 330 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 330 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 3 | |
---------------------------------------------------------------
1 - filter("ENAME" IS NULL OR "ENAME">'A')
2 - access("DEPTNO"=10)
将SQL语句中的OR条件去掉, 再看Plan:
SQL> SELECT /*+ first_rows no_expand */ * FROM EMP
2 WHERE deptNO=10 AND NVL(ENAME,'B') > 'A';
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 330 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 330 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | |
---------------------------------------------------------------
2 - access("DEPTNO"=10)
filter(NVL("ENAME",'B')>'A')
其实在真实的案例中, 记录中ENAME字段已经没有空值了.
留言 (3)
这是什么鬼玩意儿, 仔细一想,又是合理的,
Index 里面不存 Null, 所以改成 非Null 值,
就可以用到 Index 存储值了.
我碰到一个更神奇的,
做表连接时, 因为链接列 有空值 Null,
Decode() 比 Nvl()快了约2倍,
两个表均有2百万条记录, 都采用了 Hash Join.
Here it is,
1) Decode()
select
t.LISTINGSID, s.CLIENTID, s.QUANTITY, s.PRICE, s.AUTHORNAME, s.TITLENAME, ...
from abelisting.list_part_src partition(v165046) s left outer join abelisting.list_part_target partition(v165046) t
on ( Nvl(s.VENDORLISTINGID,' ')=Nvl(t.VENDORLISTINGID,' ')
and decode(s.PUBLISHCITY, t.PUBLISHCITY,1,0) = 1
and decode(s.PUBLISHISO, t.PUBLISHISO,1,0) = 1
and Nvl(s.PUBLISHYEAR,0) = Nvl(t.PUBLISHYEAR,0)
and decode(s.PUBLISHERNAME, t.PUBLISHERNAME,1,0) = 1
and decode(s.AUTHORNAME, t.AUTHORNAME,1,0) = 1
and decode(s.TITLENAME, t.TITLENAME,1,0) = 1
and decode(s.DESCRIPTION, t.DESCRIPTION,1,0) = 1
);
2) Nvl()
select
t.LISTINGSID, s.CLIENTID, s.QUANTITY, s.PRICE, s.AUTHORNAME, s.TITLENAME, ...
from abelisting.list_part_src partition(v165046) s left outer join abelisting.list_part_target partition(v165046) t
on ( Nvl(s.VENDORLISTINGID,' ')=Nvl(t.VENDORLISTINGID,' ')
and Nvl(s.PUBLISHCITY,' ')=Nvl(t.PUBLISHCITY,' ')
and Nvl(s.PUBLISHISO ,' ')=Nvl(t.PUBLISHISO ,' ')
and Nvl(s.PUBLISHYEAR, 0)=Nvl(t.PUBLISHYEAR, 0)
and Nvl(s.PUBLISHERNAME,' ')=Nvl(t.PUBLISHERNAME,' ')
and Nvl(s.AUTHORNAME,' ') = Nvl(t.AUTHORNAME,' ')
and Nvl(s.TITLENAME,' ')=Nvl(t.TITLENAME,' ')
and Nvl(s.DESCRIPTION,' ')=Nvl(t.DESCRIPTION,' ') -- 80% more CPU time
);
Posted by 木匠 | Jun 7, 2007 1:32 AM
木匠：
composite index里面除非所有的列都为空才不存储改行，否则还是会存部分列的空值的
这其实感觉上像是oracle设计上的一个问题，如果你把select字段换为index中存在的，就会在index上做filter，如果select中有非index中的column，你要去touch表，那么oracle就会在表上去做filter，其实还是可以在index上做filter的
Posted by Eagle Fan | Jun 9, 2007 11:32 AM
Eagle Fan, 谢谢你的深究, 我不够严谨, 知道却忘了写.
出于好奇, Eagle Fan 和 Eagle 是个啥关系?
你们都在eBay 或者 阿里 吗? (这个可能触犯隐私了, 哈哈)
Posted by 木匠 | Jun 12, 2007 4:03 AM