mytable上有id列,设置为了primary keyselect * from mytable where id in(1)
这个会用到索引select * from mytable where id in(1,2)
不会用到select * from mytable where id=1 or id=2
也不会用到为什么呢?数据库是oracle10
这个会用到索引select * from mytable where id in(1,2)
不会用到select * from mytable where id=1 or id=2
也不会用到为什么呢?数据库是oracle10
id in(1,2) = id=1 or id=2
比
select * from mytable where id=1 or id=2
效率高啊。
还是可以使用到索引的。
TKHXX在KHID上有PK。
SQL> select * from tkhxx where KHID in ('a','b');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TKHXX'
3 2 INDEX (UNIQUE SCAN) OF 'PK_TKHXX' (UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TKHXX'
5 4 INDEX (UNIQUE SCAN) OF 'PK_TKHXX' (UNIQUE) ----
SQL> SELECT * FROM TKHXX WHERE JJRBH='A' OR JJRBH='B';Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TKHXX'
3 2 INDEX (UNIQUE SCAN) OF 'PK_TKHXX' (UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TKHXX'
5 4 INDEX (UNIQUE SCAN) OF 'PK_TKHXX' (UNIQUE) 所以,在9i以上,oracle的sql已经对于语句的分析已经大大的改进过。
lz的oracle环境是什么?
甚至当列表来与别的subquery的时候,如果苏北query的返回不是很多也一样会使用索引。
test_khh中大概只有5条左右的数据。
SQL> select * from tkhxx where khid in (select khid from test_khh);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 VIEW OF 'VW_NSO_1'
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (FULL) OF 'TEST_KHH'
5 1 TABLE ACCESS (BY INDEX ROWID) OF 'TKHXX'
6 5 INDEX (UNIQUE SCAN) OF 'PK_TKHXX' (UNIQUE)
select * from mytable where id in(1,2)
就会全表扫描,时间是15秒左右。
----------------
转。。可以看看