时间戳

首页 > 解决方案 > 用于报表的大表的 Oracle 查询优化

sql - 用于报表的大表的 Oracle 查询优化

问题描述

我正在尝试获取以下查询的输出。它需要永远并且没有输出。你能帮忙优化查询吗？我尝试使用提示并行但仍然没有输出。TabA 有大约 120 万条记录，TabB 有 70 万条记录。数据库是甲骨文。

WITH TAB1 as (
SELECT
P_TXN_ID,PROD_CD,P_TYPE1,P_TYPE2,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='FBO' AND LAST_UPD_DT='20-NOV-17' ) FBO,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='ORG' AND LAST_UPD_DT='20-NOV-17' ) ORG,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='SEND' AND LAST_UPD_DT='20-NOV-17' )  SEND,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='RCV' AND LAST_UPD_DT='20-NOV-17' ) RCV,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='SCND' AND LAST_UPD_DT='20-NOV-17' ) SCND,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='BENE' AND LAST_UPD_DT='20-NOV-17' ) BENE,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='INT1' AND LAST_UPD_DT='20-NOV-17' ) INT1,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='INT2' AND LAST_UPD_DT='20-NOV-17' ) INT2,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='INT3' AND LAST_UPD_DT='20-NOV-17' ) INT3,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='INT4' AND LAST_UPD_DT='20-NOV-17' ) INT4,
(SELECT COALESCE(FT.V_PARTY_ID_TYPE,'XX') FROM TabA FT WHERE FT.P_TXN_ID=ST.P_TXN_ID AND FT.P_ROLE='INT5' AND LAST_UPD_DT='20-NOV-17' ) INT5
FROM 
(SELECT PROD_CD,P_TYPE1,P_TYPE2,LAST_UPD_DT,P_TXN_ID 
FROM TabB
) ST
WHERE LAST_UPD_DT>='20-NOV-17' AND LAST_UPD_DT<='22-NOV-17'  
)

SELECT /*+ parallel(100)*/
COUNT(*),
PROD_CD,
P_TYPE1,
FBO,ORG,SEND,RCV,SCND,BENE,
INT1,INT2,INT3,INT4,INT5
FROM 
TAB1 
GROUP BY PROD_CD,
P_TYPE1,
FBO,ORG,SEND,RCV,SCND,BENE,
INT1,INT2,INT3,INT4,INT5
ORDER BY PROD_CD;

提前致谢。只是为了让事情变得简单，下面是上面查询试图实现的示例。假设有TXN以下数据的表：

TXN
Col1 Col2 Col3 Type
1    AA    abc  FBO
2    null  abc  FBO
3    BB    abc  ORG
4    CC    def  ORG
5    DD    def  ORG
6    EE    pqr  SCND
7    EE    pqr  SCND
8    CC    def  RCV

然后查询的输出将是：

 Col2   Col3 FBO ORG SCND RCV
 AA     abc    1   0    0   0
 null   abc    1   0    0   0
 BB     abc    1   0    0   0
 CC     def    0   1    0   1
 DD     def    0   1    0   0
 EE     pqr    0   0    2   0

标签： sqloracleperformance

解决方案

我发现很难遵循您的代码和您的解释。但是，我很确定条件聚合就是您所需要的。

像这样的东西：

SELECT b.PROD_CD, b.P_TYPE1, b.P_TYPE2,
       MAX(CASE WHEN FT.P_ROLE = 'FBO' THEN a.V_PARTY_ID_TYPE END) as FBO,
       MAX(CASE WHEN FT.P_ROLE = 'ORG' THEN a.V_PARTY_ID_TYPE END) as ORG,
       MAX(CASE WHEN FT.P_ROLE = 'SEND' THEN a.V_PARTY_ID_TYPE END) as SEND,
       MAX(CASE WHEN FT.P_ROLE = 'RCV' THEN a.V_PARTY_ID_TYPE END) as RCV,
       MAX(CASE WHEN FT.P_ROLE = 'SCND' THEN a.V_PARTY_ID_TYPE END) as SCND,
       MAX(CASE WHEN FT.P_ROLE = 'BENE' THEN a.V_PARTY_ID_TYPE END) as BENE,
       MAX(CASE WHEN FT.P_ROLE = 'FBO' THEN a.V_PARTY_ID_TYPE END) as FBO,
       MAX(CASE WHEN FT.P_ROLE = 'INT1' THEN a.V_PARTY_ID_TYPE END) as INT1,
       MAX(CASE WHEN FT.P_ROLE = 'INT2' THEN a.V_PARTY_ID_TYPE END) as INT2,
       MAX(CASE WHEN FT.P_ROLE = 'INT3' THEN a.V_PARTY_ID_TYPE END) as INT3,
       MAX(CASE WHEN FT.P_ROLE = 'INT4' THEN a.V_PARTY_ID_TYPE END) as INT4
       MAX(CASE WHEN FT.P_ROLE = 'INT5' THEN a.V_PARTY_ID_TYPE END) as INT5
FROM TabB b JOIN
     TabA a
      ON a.P_TXN_ID = b.P_TXN_ID AND
         a.LAST_UPD_DT = DATE '2017-11-20'
WHERE LAST_UPD_DT >= DATE '2017-11-20' AND LAST_UPD_DT <= DATE '2017-11-22'
GROUP BY PROD_CD, P_TYPE1, P_TYPE2;

推荐阅读