查询速度非常慢

Question

我正在寻找一些关于我们每周运行的查询的建议。查询返回所有行的时间太长(大约16000行)，大约需要1个小时。当您最初运行查询时，这50行将在几分钟内返回，但是如果您按CTRL-A来获取所有行，这将花费太多时间。下面的查询中是否有我可以修改以运行更快的内容？谢谢

SELECT
  Contract,
  Part_No,
  Description,
  Warehouse,
  Bay, 
  Location,
  HANDLING_UNIT_ID,
  Handling_Unit_API.Get_Alt_Handling_Unit_Label_Id(HANDLING_UNIT_ID) AS pallet_id,
  Lot_Batch_No,
  Qty,
  Uom,
  (Catch_Qty/1000) AS cATCH_QTY_T,
  Catch_Uom,
  Point_In_Time_Part_Cost AS Part_Cost,
  Qty * Point_In_Time_Part_Cost AS Value
  --Part_Value_Offset,
  --Qty * Point_In_Time_Part_Cost + Part_Value_Offset AS Expected_GL_Balance
FROM
(
SELECT
  s.Contract,
  s.Part_No,
  s.Description,
  s.Warehouse,
  s.Bay,
  s.Location,
  s.HANDLING_UNIT_ID,
  Handling_Unit_API.Get_Alt_Handling_Unit_Label_Id(s.HANDLING_UNIT_ID) AS ALT_HND,
  s.Lot_Batch_No,
  s.Qty,
  s.Uom,
  s.Catch_Qty,
  s.Catch_Uom,
  FM_Inventory_Util_Api.Get_PIT_Part_Cost(Contract, Part_No, Warehouse, To_Date('&EFFECTIVE_DATE', 'DD/MM/YYYY')) AS Point_In_Time_Part_Cost
FROM
(
SELECT
  ip.Contract,
  ip.Part_No,
  ip.Description,
  ip.Warehouse,
  ip.Bay_No AS Bay, 
  ip.Location_No AS Location,
  ip.HANDLING_UNIT_ID,
  Handling_Unit_API.Get_Alt_Handling_Unit_Label_Id(ip.HANDLING_UNIT_ID) AS ALT_HND,
  ip.Lot_Batch_No,
  SUM(ip.Qty) AS Qty,
  MIN(ip.UOM) AS UOM,
  SUM(ip.Catch_Qty) AS Catch_Qty,
  MIN(ip.Catch_UOM) AS Catch_UOM
FROM
(
SELECT
  ipis.Contract, 
  ipis.Part_No, 
  Inventory_Part_Api.Get_Description(ipis.Contract, ipis.Part_No) AS Description,
  ipis.Warehouse, 
  ipis.Bay_No, 
  ipis.Location_No,
  ipis.HANDLING_UNIT_ID,
  IFSAPP.Handling_Unit_API.Get_Alt_Handling_Unit_Label_Id(ipis.HANDLING_UNIT_ID) AS ALT_HND,
  ipis.Lot_Batch_No,
  SUM(ipis.Qty_Onhand) AS Qty,
  MIN(Inventory_Part_API.Get_Unit_Meas(ipis.Contract, ipis.Part_No)) UOM, 
  SUM(ipis.Catch_Qty_Onhand) AS Catch_Qty,
  (SELECT unit_code
      FROM   PART_CATALOG_TAB
      WHERE  part_no = IPIS.part_no
      AND cATCH_UNIT_ENABLED = 'TRUE') AS CATCH_UOM
FROM
  Inventory_Part_In_Stock ipis
WHERE
  ipis.Contract LIKE Nvl('&SITE', '%')
AND  ipis.Warehouse LIKE Nvl('&WAREHOUSE', '%')
AND  (ipis.Bay_No LIKE Nvl('&BAY', '%') or ipis.Bay_No is null)

GROUP BY
  ipis.Contract, 
  ipis.Part_No, 
  Inventory_Part_Api.Get_Description(ipis.Contract, ipis.Part_No),
  ipis.Warehouse, 
  ipis.Bay_No, 
  ipis.Location_No,
  ipis.HANDLING_UNIT_ID,
  ifsapp.Handling_Unit_API.Get_Alt_Handling_Unit_Label_Id(ipis.HANDLING_UNIT_ID),
  ipis.Lot_Batch_No
HAVING 
  SUM(ipis.Qty_Onhand)  <> 0
UNION ALL
SELECT DISTINCT
  i.Contract, 
  i.Part_No,
  Inventory_Part_Api.Get_Description(i.Contract, i.Part_No) AS Description,
  Inventory_Location_API.Get_Warehouse(i.Contract, i.Location_No) AS Warehouse,
  Inventory_Location_API.Get_Bay_No(i.contract, i.location_no) AS Bay_No,
  i.Location_No,
  i.HANDLING_UNIT_ID,
  Handling_Unit_API.Get_Alt_Handling_Unit_Label_Id(i.HANDLING_UNIT_ID) AS ALT_HND,
  i.Lot_Batch_No,
  Nvl(SUM(Decode(i.Direction, '+', 0 - i.Quantity, i.Quantity)), 0) AS Qty,
  MIN(Inventory_Part_API.Get_Unit_Meas(i.Contract, i.Part_No)) UOM,
  Nvl(SUM(Decode(i.Catch_Direction, '+', 0 - i.Catch_Quantity, i.Catch_Quantity)), 0) AS Catch_Qty,
  (SELECT unit_code
      FROM   PART_CATALOG_TAB
      WHERE  part_no = i.part_no
      AND cATCH_UNIT_ENABLED = 'TRUE') AS CATCH_UOM
FROM
  Inventory_Transaction_Hist2 i
WHERE
  i.Contract LIKE Nvl('&SITE', '%')
AND
  i.Date_Applied BETWEEN To_Date('&EFFECTIVE_DATE', 'DD/MM/YYYY') + 1 AND Trunc(SYSDATE)
AND
  i.Direction IN ('+', '-')
AND
  Inventory_Location_API.Get_Warehouse(i.Contract, i.Location_No) LIKE Nvl('&WAREHOUSE', '%')
AND
  (Inventory_Location_API.Get_Bay_No(i.contract, i.location_no) LIKE Nvl('&BAY', '%') or 
   inventory_Location_API.Get_Bay_No(i.contract, i.location_no) is null)
GROUP BY
  i.Contract, 
  i.Part_No,
  Inventory_Part_Api.Get_Description(i.Contract, i.Part_No),
  Inventory_Location_API.Get_Warehouse(i.Contract, i.Location_No),
  Inventory_Location_API.Get_Bay_No(i.contract, i.location_no),
  i.Location_No,
  i.HANDLING_UNIT_ID,
  Handling_Unit_API.Get_Alt_Handling_Unit_Label_Id(i.HANDLING_UNIT_ID),
  i.Lot_Batch_No
HAVING 
  Nvl(SUM(Decode(i.Direction, '+', 0 - i.Quantity, i.Quantity)), 0)  <> 0
) ip
GROUP BY
  ip.Contract,
  ip.Part_No,
  ip.Description,
  ip.Warehouse,
  ip.Bay_No,
  ip.Location_No,
  ip.HANDLING_UNIT_ID,
  Handling_Unit_API.Get_Alt_Handling_Unit_Label_Id(ip.HANDLING_UNIT_ID),
  ip.Lot_Batch_No
HAVING
  SUM(ip.Qty) <> 0
) s
)

Answer 1

根据我的经验，子查询在性能方面可能是致命的，特别是在处理大量数据的情况下。如果这个问题不能通过连接表来处理，我建议在WITH语句中预定义子查询，然后引用这些子查询。相信我，它会显著提高你的查询速度。您可以在一个WITH语句中定义多个查询。有关使用WITH语句here的详细信息