在Oracle中调整SQL查询 [英] Tuning SQL Query in Oracle
本文介绍了在Oracle中调整SQL查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
非常感谢您在调优以下SQL查询方面的帮助.当我取消它时，它保持运行了10分钟.
I would really appreciate your help in tuning the below SQL query. It kept running for 10 mins when I cancelled it.
MARC_SEL在52秒内给了我31,253条记录 和MVKE_SEL在22秒内给了我431,060条记录
MARC_SEL gave me 31,253 records in 52 seconds and MVKE_SEL gave me 431,060 records in 22 seconds
我将其重构为可与with子句一起使用，但没有太大变化.我还可以添加其他内容以使其更快.请帮忙.
I refactored it to use with clause but nothing much changed.What else can I incorporate to make it faster. Please help.
WITH ALL_XSAP_MATNR
AS (SELECT DISTINCT XSAP.MATNR,XSAP.MTART,XSAP.SOURCE FROM XXX_MAIN.XXX_XSAP XSAP
WHERE SOURCE = 'SP' )
, MARC_SEL AS
( SELECT DISTINCT A.SOURCE
,MARA.MATNR
,MARA.MTART
,MARA.MBRSH
,MARC.WERKS
,NVL(PX.WERKS,'/') DWERK
,NVL(MBEW.HKMAT,'/') HKMAT
,NVL(MBEW.EKALR,'/') EKALR
,NVL(MARC.STAWN,'/') STAWN
FROM ALL_XSAP_MATNR A
, XXX_MAIN.XXX_SAP_MARA MARA
, XXX_MAIN.XXX_SAP_MARC MARC
, XXX_MAIN.XXX_MP_WERKS_PLANT_XREF PX
, XXX_MAIN.XXX_SAP_MBEW MBEW
WHERE A.MATNR = MARA.MATNR
AND A.MTART = MARA.MTART
AND MARA.MATNR = MARC.MATNR
AND MARC.MATNR = MBEW.MATNR
AND MARC.WERKS = MBEW.BWKEY
AND PX.LEGACY_PLANT = MARC.WERKS
AND PX.SOURCE = 'SP'
)
, MVKE_SEL AS
( SELECT DISTINCT
MVKE.MATNR
,'/' LEGACY_ORG
,'/' LEGACY_MATNR
,NVL(MX_VKORG.SAP_DE_VAL,'/') VKORG
,NVL(SUBSTR(MX_VKORG.SAP_DE,6,2),'/') VTWEG
-- ,NVL(TVRKME.MSEH3,'/') VRKME
,NVL(MVKE.KONDM,'/') KONDM
,NVL(MVKE.VERSG,'/') VERSG
,'/' IPRKZ
,'/' MHDRZ,NVL(MVKE.VMSTA,'/') VMSTA
,NVL(TO_CHAR(MVKE.VMSTD ,'YYYYMMDD' ),'/') VMSTD
,NVL(MVKE.PMATN,'/') PMATN
,NVL(MVKE.MVGR2,'/') MVGR2
,NVL(MVKE.MVGR3,'/') MVGR3
,NVL(MVKE.VAVME,'/') VAVME
,'/' MVGR4
,'/' MVGR5
,NVL(MVKE.MTPOS,'/') MTPOS
,NVL(MVKE.PRAT1,'/') PRAT1
,NVL(MVKE.SKTOF,'/') SKTOF
,'/' AUMNG
,NVL(MVKE.PRODH,'/') PRODH
,'/' MVGR1
,NVL(MVKE.KTGRM,'/') KTGRM
,MX_VKORG.DESC4
FROM XXX_MAIN.XXX_SAP_MVKE MVKE
, XXX_MAIN.XXX_MP_VKVT_XREF MX_VKORG
WHERE MX_VKORG.SOURCE_DE_VAL = MVKE.VKORG
AND SUBSTR(MX_VKORG.SAP_DE,6,2) = MVKE.VTWEG
AND MX_VKORG.SOURCE_TBL = 'SP'
AND MX_VKORG.SOURCE_DE = 'MVKE'
AND SUBSTR(MX_VKORG.SAP_DE,1,5)= 'VKORG'
AND MX_VKORG.DESC2 IS NULL )
SELECT DISTINCT
MARC.SOURCE
,MARC.MATNR
,MARC.MTART
,MARC.MBRSH
,MARC.WERKS
,MARC.DWERK
,MARC.HKMAT
,MARC.EKALR
,MARC.STAWN
,MVKE.LEGACY_ORG
,MVKE.LEGACY_MATNR
,MVKE.VKORG
,MVKE.VTWEG
,MVKE.KONDM
,MVKE.VERSG
,MVKE.VMSTA
,MVKE.VMSTD
,MVKE.PMATN
,MVKE.MVGR2
,MVKE.MVGR3
,MVKE.VAVME
,MVKE.MTPOS
,MVKE.PRAT1
,MVKE.SKTOF
,MVKE.PRODH
,MVKE.KTGRM
FROM MARC_SEL MARC
, MVKE_SEL MVKE
WHERE MARC.MATNR = MVKE.MATNR
AND MARC.WERKS = MVKE.DESC4
推荐答案
在子查询中添加了提示，几分钟后又回来了.
Added hints to subqueries and it came back in a minute.
WITH ALL_XSAP_MATNR
AS (SELECT /*+ materialize */ DISTINCT XSAP.MATNR,XSAP.MTART,XSAP.SOURCE FROM XXX_MAIN.XXX_XSAP XSAP
WHERE SOURCE = 'SP' )
, MARC_SEL AS
( SELECT /*+ materialize */ DISTINCT A.SOURCE
,MARA.MATNR
,MARA.MTART
,MARA.MBRSH
,MARC.WERKS
,NVL(PX.WERKS,'/') DWERK
,NVL(MBEW.HKMAT,'/') HKMAT
,NVL(MBEW.EKALR,'/') EKALR
,NVL(MARC.STAWN,'/') STAWN
FROM ALL_XSAP_MATNR A
, XXX_MAIN.XXX_SAP_MARA MARA
, XXX_MAIN.XXX_SAP_MARC MARC
, XXX_MAIN.XXX_MP_WERKS_PLANT_XREF PX
, XXX_MAIN.XXX_SAP_MBEW MBEW
WHERE A.MATNR = MARA.MATNR
AND A.MTART = MARA.MTART
AND MARA.MATNR = MARC.MATNR
AND MARC.MATNR = MBEW.MATNR
AND MARC.WERKS = MBEW.BWKEY
AND PX.LEGACY_PLANT = MARC.WERKS
AND PX.SOURCE = 'SP'
)
, MVKE_SEL AS
( SELECT /*+ materialize */ DISTINCT
MVKE.MATNR
,'/' LEGACY_ORG
,'/' LEGACY_MATNR
,NVL(MX_VKORG.SAP_DE_VAL,'/') VKORG
,NVL(SUBSTR(MX_VKORG.SAP_DE,6,2),'/') VTWEG
-- ,NVL(TVRKME.MSEH3,'/') VRKME
,NVL(MVKE.KONDM,'/') KONDM
,NVL(MVKE.VERSG,'/') VERSG
,'/' IPRKZ
,'/' MHDRZ,NVL(MVKE.VMSTA,'/') VMSTA
,NVL(TO_CHAR(MVKE.VMSTD ,'YYYYMMDD' ),'/') VMSTD
,NVL(MVKE.PMATN,'/') PMATN
,NVL(MVKE.MVGR2,'/') MVGR2
,NVL(MVKE.MVGR3,'/') MVGR3
,NVL(MVKE.VAVME,'/') VAVME
,'/' MVGR4
,'/' MVGR5
,NVL(MVKE.MTPOS,'/') MTPOS
,NVL(MVKE.PRAT1,'/') PRAT1
,NVL(MVKE.SKTOF,'/') SKTOF
,'/' AUMNG
,NVL(MVKE.PRODH,'/') PRODH
,'/' MVGR1
,NVL(MVKE.KTGRM,'/') KTGRM
,MX_VKORG.DESC4
FROM XXX_MAIN.XXX_SAP_MVKE MVKE
, XXX_MAIN.XXX_MP_VKVT_XREF MX_VKORG
WHERE MX_VKORG.SOURCE_DE_VAL = MVKE.VKORG
AND SUBSTR(MX_VKORG.SAP_DE,6,2) = MVKE.VTWEG
AND MX_VKORG.SOURCE_TBL = 'SP'
AND MX_VKORG.SOURCE_DE = 'MVKE'
AND SUBSTR(MX_VKORG.SAP_DE,1,5)= 'VKORG'
AND MX_VKORG.DESC2 IS NULL )
SELECT DISTINCT /*+ use_hash(MARC,MVKE ) */
MARC.SOURCE
,MARC.MATNR
,MARC.MTART
,MARC.MBRSH
,MARC.WERKS
,MARC.DWERK
,MARC.HKMAT
,MARC.EKALR
,MARC.STAWN
,MVKE.LEGACY_ORG
,MVKE.LEGACY_MATNR
,MVKE.VKORG
,MVKE.VTWEG
,MVKE.KONDM
,MVKE.VERSG
,MVKE.VMSTA
,MVKE.VMSTD
,MVKE.PMATN
,MVKE.MVGR2
,MVKE.MVGR3
,MVKE.VAVME
,MVKE.MTPOS
,MVKE.PRAT1
,MVKE.SKTOF
,MVKE.PRODH
,MVKE.KTGRM
FROM MARC_SEL MARC
, MVKE_SEL MVKE
WHERE MARC.MATNR = MVKE.MATNR
AND MARC.WERKS = MVKE.DESC4
这篇关于在Oracle中调整SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
