我可以在查询中使用一些指针。我有三个外部表，需要将它们组合起来创建一个.dat文件。但我的查询效率不高，需要一些帮助来修复/改进这个查询。它由两个第一部分的主表dc_item_loc_sourceing（1）和两个翻译表dc_ccn190_sid_vtb（2）和dc_item_loc_vert_pim（3）组成。如果项目+loc组合不存在于1&2应该检查1&3.
SET heading OFF;
SET feedback OFF;
SET verify OFF;
SET echo OFF;
SET linesize 1000;
SET trimspool ON;
SET termout OFF;
SET newpage NONE;
SPOOL ../data/dc_sourcing.dat;
SELECT DISTINCT TO_CHAR(item)
|| '|' ||store
|| '|' ||source_method
|| '|' ||primary_supp
|| '|' ||source_wh
|| '|' ||actie
|| '|' ||reward_eligible_ind
FROM ((SELECT dpac_tbl.item
,st.store
,dc_iloc.source_method
,dc_iloc.primary_supp
,dc_iloc.source_wh
,dc_iloc.actie
,dc_iloc.reward_eligible_ind
,MAX(dc_iloc.source_method) OVER (PARTITION BY dpac_tbl.item,st.store) max_src_pack
FROM dc_item_loc_sourcing dc_iloc ,
dc_ccn190_sid_vtb dpac_tbl ,
store st ,
item_master im ,
item_loc il
WHERE dc_iloc.dpac = dpac_tbl.dpac
AND dpac_tbl.item = im.item
AND CAST(dc_iloc.loc AS VARCHAR2(150byte)) = st.store_name_secondary
AND st.store = il.loc
AND dpac_tbl.item = il.item
AND im.status = 'A'
AND st.store_close_date >= SYSDATE
AND il.status = 'A'
AND dpac_tbl.ITEM NOT IN
(SELECT IA.ITEM
FROM ITEM_ATTRIBUTES IA
WHERE IA.SH_STORE_ORDER_UNIT = 'N'
AND IA.SH_TRADE_UNIT = 'Y')
UNION
SELECT DISTINCT pi.item
,st.store
,dc_iloc.source_method
,dc_iloc.primary_supp
,dc_iloc.source_wh
,dc_iloc.actie
,dc_iloc.reward_eligible_ind
,MAX(dc_iloc.source_method) OVER (PARTITION BY pi.item,st.store) max_src_pack
FROM dc_item_loc_sourcing dc_iloc ,
dc_ccn190_sid_vtb dpac_tbl ,
store st ,
packitem pi ,
item_master im ,
item_loc il
WHERE dc_iloc.dpac = dpac_tbl.dpac
AND pi.pack_no = dpac_tbl.item
AND pi.item = im.item
AND CAST(dc_iloc.loc AS VARCHAR2(150byte)) = st.store_name_secondary
AND il.item = pi.item
AND il.loc = st.store
AND im.status = 'A'
AND im.dept NOT IN (900,910,920,930)
AND st.store_close_date >= SYSDATE
AND il.status = 'A'
AND PI.ITEM NOT IN
(SELECT IA.ITEM
FROM ITEM_ATTRIBUTES IA
WHERE IA.SH_STORE_ORDER_UNIT = 'N'
AND IA.SH_TRADE_UNIT = 'Y'))
UNION
(SELECT dpac_tbl.item ,
st.store ,
dc_iloc.source_method ,
dc_iloc.primary_supp ,
dc_iloc.source_wh ,
dc_iloc.actie ,
dc_iloc.reward_eligible_ind
,MAX(dc_iloc.source_method) OVER (PARTITION BY dpac_tbl.item,st.store) max_src_pack
FROM dc_item_loc_sourcing dc_iloc ,
dc_item_loc_vert_pim dpac_tbl ,
store st ,
item_master im ,
item_loc il
WHERE dc_iloc.dpac = dpac_tbl.dpac
AND dpac_tbl.item = im.item
AND CAST(dc_iloc.loc AS VARCHAR2(150 byte)) = st.store_name_secondary
AND il.item = dpac_tbl.item
AND il.loc = st.store
AND im.status = 'A'
AND dpac_tbl.artikel_type_lms NOT IN ('V','S')
AND st.store_close_date >= SYSDATE
AND il.status = 'A'
AND inventory_item_status_code = 'Active'
AND dpac_tbl.ITEM NOT IN
(SELECT IA.ITEM
FROM ITEM_ATTRIBUTES IA
WHERE IA.SH_STORE_ORDER_UNIT = 'N'
AND IA.SH_TRADE_UNIT = 'Y')
UNION
SELECT DISTINCT pi.item ,
st.store ,
dc_iloc.source_method ,
dc_iloc.primary_supp ,
dc_iloc.source_wh ,
dc_iloc.actie ,
dc_iloc.reward_eligible_ind
,MAX(dc_iloc.source_method) OVER (PARTITION BY pi.item,st.store) max_src_pack
FROM dc_item_loc_sourcing dc_iloc ,
dc_item_loc_vert_pim dpac_tbl ,
store st ,
packitem pi ,
item_master im ,
item_loc il
WHERE dc_iloc.dpac = dpac_tbl.dpac
AND pi.pack_no = dpac_tbl.item
AND pi.item = im.item
AND CAST(dc_iloc.loc AS VARCHAR2(150 byte)) = st.store_name_secondary
AND il.item = pi.item
AND il.loc = st.store
AND im.status = 'A'
AND dpac_tbl.artikel_type_lms NOT IN ('V','S')
AND im.dept NOT IN (900,910,920,930)
AND st.store_close_date >= SYSDATE
AND il.status = 'A'
AND inventory_item_status_code = 'Active'
AND pi.ITEM NOT IN
(SELECT IA.ITEM
FROM ITEM_ATTRIBUTES IA
WHERE IA.SH_STORE_ORDER_UNIT = 'N'
AND IA.SH_TRADE_UNIT = 'Y')))
WHERE source_method = max_src_pack;
SPOOL OFF;
这还没有经过测试-我现在不在SQL实例中，显然我无法访问您的表。我也不保证性能，因为我不知道目前的指标。
您最大的问题似乎源于使用隐式联接语法。不要使用它，这是一个反模式；它也允许一些"令人惊讶"的行为。始终显式指定联接，并在ON子句中放入（尽可能多的）相关条件-在处理FROM子句中的表引用时仅使用WHERE子句。
我对这句话也有很大的问题：AND CAST(dc_iloc.loc AS VARCHAR2(150byte)) = st.store_name_secondary
您将而不是（可能……有一些注意事项）在此比较中使用索引，这将没有帮助。从语义上讲，加入也是一件可怕的事情——为什么一个项目位置表（显然）是由商店名称键控的？它应该由存储id键控，存储id应该是相同的数据类型（没有转换），并且应该是内部id，而不是像"name"这样的瞬态id。
WITH Excluded_Item (item) as (SELECT DISTINCT item
FROM Item_Attributes
WHERE sh_store_order_unit = 'N'
AND sh_trade_unit = 'Y'), -- These should be 1/0 flags
-- (char or numeric)
-- or boolean, if supported
SELECT CAST(item as CHAR)
|| '|' || loc
|| '|' || source_method
|| '|' || primary_supp
|| '|' || source_wh
|| '|' || actie
|| '|' || reward_eligible_ind
FROM(SELECT DISTINCT c.item, c.loc,
a.source_method, a.primary_supp, a.source_wh, a.actie,
a.reward_eligible_ind,
MAX(a.source_method)
OVER(PARTITION BY c.item, c.loc) as maxSourcePack
-- this should be indexed
FROM dc_item_loc_sourcing as a
JOIN store as b
ON b.store_name_secondary = CAST(a.loc as VARCHAR2(150 byte))
AND b.store_close_date >= CURRENT_DATE
JOIN item_loc as c
ON c.loc = b.store
AND c.status = 'A'
JOIN item_master as d
ON d.status = 'A'
AND d.item = c.item
LEFT JOIN Excluded_Item as e
ON e.item = d.item
LEFT JOIN dc_ccn190_sid_vtb as f
ON f.dpac = a.dpac
LEFT JOIN dc_item_loc_vert_pim as g
ON g.dpac = a.dpac
AND g.artikel_type_lms NOT IN ('V', 'S')
AND inventory_item_status_code = 'Active' -- really? And where is this from?
LEFT JOIN packitem as h
ON (h.pack_no = f.item OR h.pack_no = g.item)
AND h.item = d.item
AND d.dept NOT IN (900, 910, 920, 930)
WHERE e.item IS NULL
AND (h.item IS NOT NULL
OR (h.item IS NULL AND (f.item = d.item OR g.item = d.item))) ) as h
WHERE source_method = maxSourcepack
我相信这是正确的，尽管表（和示例数据）的定义在这方面会有很大帮助，尤其是在如何定义packitem方面。