今天在启动应用时发现一个sql执行非常之慢,几乎需要一小时都执行不完.
Select *
From v_Sm_Org_Relation v
Where v.Unit_Id In (Select Distinct Customer_Id
From v_Out_Bis_Md_Org_Rel_Child
Where Qryorg_Rel_Id In (-300347))
其中视图v_sm_org_relation内层视图如下:
Select To_Number(Org.Id) Org_Rel_Id,
To_Number(Org.Parent_Id) Par_Org_Rel_Id,
To_Number(Org.Dimension_Id) Bus_Org_Id,
To_Number(Org.Unit_Id) Customer_Id,
...
From Up_Org_Dimension_Unit Org, Up_Org_Unit T1
Where Org.Unit_Id = T1.Unit_Id
刚开始怀疑是没有为To_Number(Org.Unit_Id) Customer_Id建索引所致,后来建立索引后发现仍非常慢,然后再尝试为To_Number(Org.Id) Org_Rel_Id建立索引后问题解决!