请问这条sql语句能否优化

显示全部楼层 · 2006-7-20 13:19:20

SELECT a.PackDetailID,
b.CustomerName,
a.Vehicle,
a.TranNo,
a.PkgPlanNum,
a.PkgFactNum,
a.ExpdisBatNo,
c.TransportRouteName
FROM Tbl_PackTranTask_Detail a,
v_Transport_Customerb,
v_TransportRoutec
WHERE (a.OwnerCode = 0002)
AND (a.ExpdisBatNo = 'P20070104000311')
AND (a.OwnerCode = b.OwnerCode)
AND (a.CustomerType = b.CustomerType)
AND (a.CustomerID = b.CustomerID)
AND (b.transportroute = c.transportrouteid)
AND (a.CustomerType >= 1)
AND (NVL(a.Status, '0') = 0)
AND (a.checkoutid = 0)

千问 · 2006-7-20 13:19:20

能否给出statistics和execute plan?

千问 · 2006-7-20 13:19:20

感觉三个表关联要看表的数量级，选好驱动表，找出常用的查询条件的规律创建有针对性地索引，如果表太大可以对某一个字段建立分区。没有详细的说明只能给点建议。

千问 · 2006-7-20 13:19:20

试试这样~~~
SELECT a.PackDetailID,
b.CustomerName,
a.Vehicle,
a.TranNo,
a.PkgPlanNum,
a.PkgFactNum,
a.ExpdisBatNo,
c.TransportRouteName
From
(Select * From Tbl_PackTranTask_Detail Where OwnerCode = 0002 And ExpdisBatNo = 'P20070104000311' And CustomerType >= 1 And NVL(Status, '0') = 0 And checkoutid = 0)a,
v_Transport_Customer b,
v_TransportRoute c
Where (a.OwnerCode = b.OwnerCode)
AND (a.CustomerType = b.CustomerType)
AND (a.CustomerID = b.CustomerID)
AND (b.transportroute = c.transportrouteid)

千问 · 2006-7-20 13:19:20

楼上的思路是先缩小查询Tbl_PackTranTask_Detail 后的结果集。那么是否应把它作为驱动表呢？