数据量 30W一个SQL语句里面穿插四个子表用到FULL JOIN 再最后 有OR整合同样的SQL我在MSSQL里运行 不出五秒 就出数据但在PSQL里运行 就是无尽的等待啊求教高人 这是什么问题以下下是SQL原文
SELECT T4.D_CPCODE,T4.D_NAME,T4.D_JHJE,T1.A_JHJE,T1.A_PJTSL,T1.A_JHJE/T4.D_JHJE,T2.B_JHJE,T2.B_PJTSL,T2.B_JHJE/T4.D_JHJE,T3.C_JHJE,T3.C_PJTSL,T3.C_JHJE/T4.D_JHJE FROM
(
SELECT CPCODE AS A_CPCODE,NAME AS A_NAME,SUM(JHJE) AS A_JHJE,SUM(TSE)/SUM(JHJE) AS A_PJTSL
FROM TS_JG_PUB
WHERE (SB_YM>=200701 AND SB_YM<=200905) AND USER_GROUP='1' AND GHFNS_NO LIKE '4407%' --AND CPCODE='4407960102'
GROUP BY CPCODE,NAME
) T1 FULL JOIN
(
SELECT CPCODE AS B_CPCODE,NAME AS B_NAME,SUM(JHJE) AS B_JHJE,SUM(TSE)/SUM(JHJE) AS B_PJTSL
FROM TS_JG_PUB
WHERE (SB_YM>=200701 AND SB_YM<=200905) AND USER_GROUP='1' AND GHFNS_NO LIKE '44%' AND GHFNS_NO NOT LIKE '4407%' --AND CPCODE='4407960102'
GROUP BY CPCODE,NAME
) T2
ON T1.A_CPCODE = T2.B_CPCODE
FULL JOIN
(
SELECT CPCODE AS C_CPCODE,NAME AS C_NAME,SUM(JHJE) AS C_JHJE,SUM(TSE)/SUM(JHJE) AS C_PJTSL
FROM TS_JG_PUB
WHERE (SB_YM>=200701 AND SB_YM<=200905) AND USER_GROUP='1' AND GHFNS_NO NOT LIKE '44%' --AND CPCODE='4407960102'
GROUP BY CPCODE,NAME
) T3
ON T2.B_CPCODE= T3.C_CPCODE
FULL JOIN
(
SELECT CPCODE AS D_CPCODE,NAME AS D_NAME,SUM(JHJE) AS D_JHJE FROM TS_JG_PUB
WHERE (SB_YM>=200701 AND SB_YM<=200905) AND USER_GROUP='1' --AND CPCODE='4407960102'
GROUP BY CPCODE,NAME
) T4
ON T4.D_CPCODE=T1.A_CPCODE OR T4.D_CPCODE=T2.B_CPCODE OR T4.D_CPCODE=T3.C_CPCODE
ORDER BY T4.D_CPCODE DESC
SELECT T4.D_CPCODE,T4.D_NAME,T4.D_JHJE,T1.A_JHJE,T1.A_PJTSL,T1.A_JHJE/T4.D_JHJE,T2.B_JHJE,T2.B_PJTSL,T2.B_JHJE/T4.D_JHJE,T3.C_JHJE,T3.C_PJTSL,T3.C_JHJE/T4.D_JHJE FROM
(
SELECT CPCODE AS A_CPCODE,NAME AS A_NAME,SUM(JHJE) AS A_JHJE,SUM(TSE)/SUM(JHJE) AS A_PJTSL
FROM TS_JG_PUB
WHERE (SB_YM>=200701 AND SB_YM<=200905) AND USER_GROUP='1' AND GHFNS_NO LIKE '4407%' --AND CPCODE='4407960102'
GROUP BY CPCODE,NAME
) T1 FULL JOIN
(
SELECT CPCODE AS B_CPCODE,NAME AS B_NAME,SUM(JHJE) AS B_JHJE,SUM(TSE)/SUM(JHJE) AS B_PJTSL
FROM TS_JG_PUB
WHERE (SB_YM>=200701 AND SB_YM<=200905) AND USER_GROUP='1' AND GHFNS_NO LIKE '44%' AND GHFNS_NO NOT LIKE '4407%' --AND CPCODE='4407960102'
GROUP BY CPCODE,NAME
) T2
ON T1.A_CPCODE = T2.B_CPCODE
FULL JOIN
(
SELECT CPCODE AS C_CPCODE,NAME AS C_NAME,SUM(JHJE) AS C_JHJE,SUM(TSE)/SUM(JHJE) AS C_PJTSL
FROM TS_JG_PUB
WHERE (SB_YM>=200701 AND SB_YM<=200905) AND USER_GROUP='1' AND GHFNS_NO NOT LIKE '44%' --AND CPCODE='4407960102'
GROUP BY CPCODE,NAME
) T3
ON T2.B_CPCODE= T3.C_CPCODE
FULL JOIN
(
SELECT CPCODE AS D_CPCODE,NAME AS D_NAME,SUM(JHJE) AS D_JHJE FROM TS_JG_PUB
WHERE (SB_YM>=200701 AND SB_YM<=200905) AND USER_GROUP='1' --AND CPCODE='4407960102'
GROUP BY CPCODE,NAME
) T4
ON T4.D_CPCODE=T1.A_CPCODE OR T4.D_CPCODE=T2.B_CPCODE OR T4.D_CPCODE=T3.C_CPCODE
ORDER BY T4.D_CPCODE DESC
解决方案 »
- 一个简单的触发器,可是它在报错。。。
- impdp 导入问题
- oracle 表因为update 大长度字段,导致表空间增长过快问题,高手请进
- oracle job
- Oracle数据清理遇到的困难
- 如何通过代码更新一个 package 里面的某个 procedure
- 竖表转横表的问题
- 请问使用jsp + oracle 的大侠:4000字符以上的字段用什么类型?如何处理添加、修改?
- OEM 中没有数据库的问题
- select 语句报错“ORA-00923: FROM keyword not found where expected”
- 急救:PL/Sql Developer
- 关于 /*+ INDEX(RMSM_PUR_D SEX_INDEX) */
就可以查出数据了
但是.
效率虽然慢 但也只是十几秒的事....加上OR 那就是无尽等待