多表关联查询索引使用疑问



但看图片上的执行计划,却是先Nested Loops,而且iesk_stocktransfer还是全表扫描,整个查询才用了"IESK_STOCKTRANSITEM_Name2"一个索引.

按我的理解,应该是先执行c.resName='c' 以减少返回的数据量,再进行Nested Loops
先执行c.resName='c' -- 可以使用 IESK_STOCKTRANSITEM_Name 索引
再执行Nested Loops,应该可以使用iesk_stocktransfer_billNo索引
这种走索引方式才是最优的?

---------下面是SQL语句----------
create index IESK_STOCKTRANSITEM_Name on IESK_STOCKTRANSITEM(resname);
create index iesk_stocktransfer_billNo on iesk_stocktransfer(billNo,resourceID);
create index IESK_STOCKTRANSITEM_Name2 on IESK_STOCKTRANSITEM(billId ,resid);

select c.resid,c.resCode,c.resName ,m.billNo,m.resourceID,m.resourceName from 
(select c.resid,c.resCode,c.resName, c.billId from IESK_STOCKTRANSITEM c where c.resName='a') c,
iesk_stocktransfer m where m.billNo = c.billId and m.resourceID = c.resid ;



------解决方案--------------------

探讨

SQL code

SELECT /* ordered use_nl(c m)*/
C.RESID,
C.RESCODE,
C.RESNAME,
M.BILLNO,
M.RESOURCEID,
M.RESOURCENAME
FROM (SELECT C.RESID, C.RESCODE, C.RESNAME……