求视图优化方法,请各位高手不吝赐教!!!
各位大侠,小弟建了一个视图,发现查询效率十分低下,于是用排除法找到有三个字段对查询效率影响十分严重。
这三个字段分别是【小号列表】、【二维码】、【用户标识串】。
总记录条数为5385条,去掉这3个字段的查询时间为8秒。
加上【小号列表】字段,查询时间为38秒
加上【小号列表】、【二维码】字段,查询时间为1份11秒
加上【小号列表】、【二维码】、【用户标识串】字段,查询时间为1分30秒。
求各位高手伸以援手,解救小弟于水火之中!抱拳了,哥!!
create or replace view wms_item_inventory_v2 as
select
Wii.id,
...
...
(select wm_concat(Item_Sub_No) from Mes_item_order t
where wms_item_inventory_id = Wii.ID and t.Item_Produce_Status = '5') as Item_Order_str, --小号列表
(select wiobl.bar_code from Wms_inv_Out_Bill_List wiobl
where wiobl.mds_item_id = Wii.Mds_Item_id and rownum = 1) as QRCode, --二维码
(select Sys_Common_if_p.Get_Lookup_Name('WMS_USER_FLAG',wii.USER_FLAG) || ','
|| wm_concat(Sys_Common_If_p.Get_Lookup_name('WMS_USER_FLAG',user_flag))
from Mes_item_Order t where t.wms_item_inventory_id = Wii.ID) AS User_flag_str --用户标识串
from Wms_Item_Inventory Wii
INNER JOIN Mds_item Mi ON Mi.id = Wii.Mds_Item_id
LEFT JOIN Mds_item Pi ON pi.id = Wii.Pro_Mds_Item_Id
INNER JOIN Wms_Inventory Wi ON Wi.Id - Wii.Wms_Inventory_id
LFET JOIN Wms_Locator wl ON wl.id = Wii.wms_locator_id
WHERE wi.inventory_code in('100','101','102','103')