Select Ci.Customer_Id,
Ci.Customer_Name,
Mc.Card_Id,
t.Card_Type_Name,
f_Getcustomercontactmeans(Ci.Customer_Id) As Cust_Contactmeans
From v_Customer_Info Ci
Inner Join t_Mc_Card_Used Mc
On Mc.Card_Used_Id = (Select Card.Card_Used_Id
From v_Customer_Card_Used Card
Where Card.Customer_Id = Ci.Customer_Id
And Rownum = 1)
Inner Join t_Mc_Card_Type t
On t.Card_Type_Id = Mc.Card_Type_Id
Where 1 = 1
And Mc.Card_Id = '04001155750'
/* And (Ci.Customer_Name = '测试' Or Ci.Cust_Name_Jpinyin = '测试')
And Exists (Select a.Customer_Id
From t_c_Customer_Contactmeans a
Where a.Cust_Contactmeans = '13676898987'
And a.Customer_Id = Ci.Customer_Id)
*/
不注释后面那句话SQL是一秒就能出,但是注释掉后面那句话,只留 And Mc.Card_Id = '04001155750'
这个条件5分钟都跑不出来,这个mc.card_id列上是有非常好的索引的,但是执行计划非常糟糕,这里涉及
的视图太多,执行计划太长,就不仔细研究,
观察发现问题在于用到ci这个视图的就非常快,那么这里可以把where后面的card_id换一下
添加一个表连接,用上ci
Select Ci.Customer_Id,
Ci.Customer_Name,
Mc.Card_Id,
t.Card_Type_Name,
f_Getcustomercontactmeans(Ci.Customer_Id) As Cust_Contactmeans,
ci.customer_id
From v_Customer_Info Ci
Inner Join t_Mc_Card_Used Mc
On Mc.Card_Used_Id = (Select Card.Card_Used_Id
From v_Customer_Card_Used Card
Where Card.Customer_Id = Ci.Customer_Id
And Rownum = 1)
Inner Join t_Mc_Card_Type t
On t.Card_Type_Id = Mc.Card_Type_Id
Inner Join t_mc_card_used t4 On ci.customer_id=t4.customer_id
Where 1 = 1
And t4.Card_Id = '04001155750'
这样就非常快了,从5分钟变成一秒钟了。
总结:有的时候可以并不一定非要从执行计划入手,观察SQL,他涉及的视图以及业务需求,根据SQL表面现象就可以优化。
增加表连接来优化SQL
最新推荐文章于 2024-08-14 13:50:42 发布