多表关联查询索引使用疑问
但看图片上的执行计划,却是先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 ;
[解决办法]
