有这样一条语句:SELECT *
FROM a,
(SELECT b.bid, b.cid
FROM b, c
WHERE b.cid = c.cid
AND c.type = 1000) tmp
WHERE a.bid = tmp.bid(+);目的在于查出a表中的bid字段在b表中且符合c表的类型字段为1000的所有记录,如果a.bid在b表中不存在,则相应字段为空。问题是:tmp表没法走索引,导致速度很慢。我把它改成这样:SELECT *
FROM a, b, c
WHERE a.bid = b.bid(+)
AND b.cid = c.cid
AND c.type = 1000;却发现查询的结果少了很多,找不出a.bid在b表中不存在的记录。
FROM a,
(SELECT b.bid, b.cid
FROM b, c
WHERE b.cid = c.cid
AND c.type = 1000) tmp
WHERE a.bid = tmp.bid(+);目的在于查出a表中的bid字段在b表中且符合c表的类型字段为1000的所有记录,如果a.bid在b表中不存在,则相应字段为空。问题是:tmp表没法走索引,导致速度很慢。我把它改成这样:SELECT *
FROM a, b, c
WHERE a.bid = b.bid(+)
AND b.cid = c.cid
AND c.type = 1000;却发现查询的结果少了很多,找不出a.bid在b表中不存在的记录。
FROM b, c
WHERE b.cid = c.cid
AND c.type = 1000
这个应该是可以走索引的
速度慢并不定是不走索引的关系
你数据量大的话,CBO可能会选择全表扫描
你多表的关联一样是用NSLOOP的
FROM a, b, c
WHERE a.bid = b.bid(+)
AND b.cid = c.cid(+)
AND c.type(+) = 1000;
FROM a, b, c
WHERE a.bid = b.bid(+)
AND b.cid (+)= c.cid
AND c.type = 1000;改成这样试试
CREATE TABLE TEST_B(BID NUMBER,CID NUMBER);
CREATE TABLE TEST_C(CID NUMBER,CTYPE VARCHAR2(10)); SQL> SELECT * FROM TEST_A; BID
----------
1
2
3SQL> SELECT * FROM TEST_B; BID CID
---------- ----------
1 1
2 1
3 2
4 2
5 3SQL> SELECT * FROM TEST_C; CID CTYPE
---------- ----------
1 1000
2 1000
3 1000
4 2000
5 1000SQL> SELECT *
2 FROM TEST_A A,
3 TEST_B B,
4 TEST_C C
5 WHERE A.BID(+) = B.BID
6 AND B.CID = C.CID
7 AND C.CTYPE = 1000; BID BID CID CID CTYPE
---------- ---------- ---------- ---------- ----------
1 1 1 1 1000
2 2 1 1 1000
3 3 2 2 1000
5 3 3 1000
4 2 2 1000SQL> SELECT *
2 FROM TEST_A A,
3 (SELECT B.BID,B.CID
4 FROM TEST_B B,
5 TEST_C C
6 WHERE B.CID = C.CID
7 AND C.CTYPE = 1000)TMP
8 WHERE A.BID(+) = TMP.BID; BID BID CID
---------- ---------- ----------
1 1 1
2 2 1
3 3 2
5 3
4 2SQL>