SELECT
.............(other column)
,NVL(SUM(GPS.AAAAAAA),0) AS A
,NVL(SUM(GPS.BBBBBBB),0)AS B
.............(other column)FROM
(SELECT
.............(other column)
-- 如此处这样的操作方法执行了四五次 查询了全表的所有数据 导致查询10年的数据时 执行的非常慢 哪位大侠有好的优化方法 不胜感激 越快越好 ,(SELECT SUM(****)
FROM SIZAI_UKEHARAI_T SU
,SIZAI_M ZM
,SIZAI_KIKAKU_L L
,SIZAIGRP_3_T Z3
,SIZAIGRP_2_T Z2
,SIZAIGRP_1_T Z1
WHERE SJ.SEISAN_DATE = SU.SEISAN_DATE
AND SJ.SEISAN_NO = SU.SEISAN_NO
AND SU.SIZAI_CD = ZM.SIZAI_CD
AND SU.KIKAKU_NO = L.KIKAKU_NO
AND ZM.SIZAIGRP_CD1 = Z2.SIZAIGRP_CD1
AND ZM.SIZAIGRP_CD2 = Z2.SIZAIGRP_CD2
AND (Z1.SIZAIGRP_CD1 = 'XX1' OR Z1.SIZAIGRP_CD1 = 'XX2' OR Z1.SIZAIGRP_CD1 = 'XX3')
AND (Z2.SIZAIGRP_CD2 = 'YY1')
AND Z2.BUDOMARI_FG = 'XX'
) AS AAAAAAA
,(SELECT
SUM(*****)
FROM AA SU
,BB ZM
,CC L
,DD Z3
,EE Z2
,FF Z1
WHERE SJ.SEISAN_DATE = SU.SEISAN_DATE
AND SJ.SEISAN_NO = SU.SEISAN_NO
AND SU.SIZAI_CD = ZM.SIZAI_CD
AND SU.KIKAKU_NO = L.KIKAKU_NO
AND ZM.SIZAIGRP_CD1 = Z3.SIZAIGRP_CD1
AND ZM.SIZAIGRP_CD2 = Z3.SIZAIGRP_CD2
AND ZM.SIZAIGRP_CD3 = Z3.SIZAIGRP_CD3
AND ZM.SIZAIGRP_CD1 = Z2.SIZAIGRP_CD1
AND ZM.SIZAIGRP_CD2 = Z2.SIZAIGRP_CD2
AND ZM.SIZAIGRP_CD1 = Z1.SIZAIGRP_CD1
AND Z1.SIZAIGRP_CD1 = 'XX4'
AND Z2.BUDOMARI_FG = 'XX'
AND Z2.SIZAIGRP_CD2 = 'YY'
) AS BBBBBBB
FROM
A SJ
,B SIM
,C HM
,D S4
,E S3
,F MM
,G S2
,H S1
,I KM
WHERE
SJ.SEISAN_CD = SIM.SIKAKARI_CD
AND SIM.SIKAGRP_CD1 = S1.SIKAGRP_CD1
AND SIM.SIKAGRP_CD1 = S2.SIKAGRP_CD1
AND SIM.SIKAGRP_CD2 = S2.SIKAGRP_CD2
AND SIM.SIKAGRP_CD1 = S3.SIKAGRP_CD1
AND SIM.SIKAGRP_CD2 = S3.SIKAGRP_CD2
AND SIM.SIKAGRP_CD3 = S3.SIKAGRP_CD3
AND SIM.SIKAGRP_CD1 = S4.SIKAGRP_CD1
AND SIM.SIKAGRP_CD2 = S4.SIKAGRP_CD2
AND SIM.SIKAGRP_CD3 = S4.SIKAGRP_CD3
AND SIM.SIKAGRP_CD4 = S4.SIKAGRP_CD4
AND SIM.HINMEI_CD = HM.HINMEI_CD
AND SJ.MACHINE_CD = MM.MACHINE_CD
AND MM.KOUJO_CD = KM.KOUJO_CD
AND MM.MC_FG = 'XX'
AND MM.GEPPO_FG = 'YY'
) GPS
GROUP BY GPS.MACHINE_CD, TO_CHAR(GPS.SEISAN_DATE,'YYYY/MM'), GPS.SIKAGRP_CD1 || GPS.SIKAGRP_CD2, GPS.HINMEI_CD
.............(other column)
,NVL(SUM(GPS.AAAAAAA),0) AS A
,NVL(SUM(GPS.BBBBBBB),0)AS B
.............(other column)FROM
(SELECT
.............(other column)
-- 如此处这样的操作方法执行了四五次 查询了全表的所有数据 导致查询10年的数据时 执行的非常慢 哪位大侠有好的优化方法 不胜感激 越快越好 ,(SELECT SUM(****)
FROM SIZAI_UKEHARAI_T SU
,SIZAI_M ZM
,SIZAI_KIKAKU_L L
,SIZAIGRP_3_T Z3
,SIZAIGRP_2_T Z2
,SIZAIGRP_1_T Z1
WHERE SJ.SEISAN_DATE = SU.SEISAN_DATE
AND SJ.SEISAN_NO = SU.SEISAN_NO
AND SU.SIZAI_CD = ZM.SIZAI_CD
AND SU.KIKAKU_NO = L.KIKAKU_NO
AND ZM.SIZAIGRP_CD1 = Z2.SIZAIGRP_CD1
AND ZM.SIZAIGRP_CD2 = Z2.SIZAIGRP_CD2
AND (Z1.SIZAIGRP_CD1 = 'XX1' OR Z1.SIZAIGRP_CD1 = 'XX2' OR Z1.SIZAIGRP_CD1 = 'XX3')
AND (Z2.SIZAIGRP_CD2 = 'YY1')
AND Z2.BUDOMARI_FG = 'XX'
) AS AAAAAAA
,(SELECT
SUM(*****)
FROM AA SU
,BB ZM
,CC L
,DD Z3
,EE Z2
,FF Z1
WHERE SJ.SEISAN_DATE = SU.SEISAN_DATE
AND SJ.SEISAN_NO = SU.SEISAN_NO
AND SU.SIZAI_CD = ZM.SIZAI_CD
AND SU.KIKAKU_NO = L.KIKAKU_NO
AND ZM.SIZAIGRP_CD1 = Z3.SIZAIGRP_CD1
AND ZM.SIZAIGRP_CD2 = Z3.SIZAIGRP_CD2
AND ZM.SIZAIGRP_CD3 = Z3.SIZAIGRP_CD3
AND ZM.SIZAIGRP_CD1 = Z2.SIZAIGRP_CD1
AND ZM.SIZAIGRP_CD2 = Z2.SIZAIGRP_CD2
AND ZM.SIZAIGRP_CD1 = Z1.SIZAIGRP_CD1
AND Z1.SIZAIGRP_CD1 = 'XX4'
AND Z2.BUDOMARI_FG = 'XX'
AND Z2.SIZAIGRP_CD2 = 'YY'
) AS BBBBBBB
FROM
A SJ
,B SIM
,C HM
,D S4
,E S3
,F MM
,G S2
,H S1
,I KM
WHERE
SJ.SEISAN_CD = SIM.SIKAKARI_CD
AND SIM.SIKAGRP_CD1 = S1.SIKAGRP_CD1
AND SIM.SIKAGRP_CD1 = S2.SIKAGRP_CD1
AND SIM.SIKAGRP_CD2 = S2.SIKAGRP_CD2
AND SIM.SIKAGRP_CD1 = S3.SIKAGRP_CD1
AND SIM.SIKAGRP_CD2 = S3.SIKAGRP_CD2
AND SIM.SIKAGRP_CD3 = S3.SIKAGRP_CD3
AND SIM.SIKAGRP_CD1 = S4.SIKAGRP_CD1
AND SIM.SIKAGRP_CD2 = S4.SIKAGRP_CD2
AND SIM.SIKAGRP_CD3 = S4.SIKAGRP_CD3
AND SIM.SIKAGRP_CD4 = S4.SIKAGRP_CD4
AND SIM.HINMEI_CD = HM.HINMEI_CD
AND SJ.MACHINE_CD = MM.MACHINE_CD
AND MM.KOUJO_CD = KM.KOUJO_CD
AND MM.MC_FG = 'XX'
AND MM.GEPPO_FG = 'YY'
) GPS
GROUP BY GPS.MACHINE_CD, TO_CHAR(GPS.SEISAN_DATE,'YYYY/MM'), GPS.SIKAGRP_CD1 || GPS.SIKAGRP_CD2, GPS.HINMEI_CD
具体要看一下执行的计划了,oracle devloper,F5具体看一下执行的计划了。
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 NESTED LOOPS
6 5 NESTED LOOPS
7 6 TABLE ACCESS (FULL) OF 'MACHINE_M'
8 6 TABLE ACCESS (BY INDEX ROWID) OF 'SEISAN_JISEK
I_T' 9 8 INDEX (RANGE SCAN) OF 'PK_SEISAN_JISEKI_T' (
UNIQUE) 10 5 TABLE ACCESS (BY INDEX ROWID) OF 'SIKAKARI_M'
11 10 INDEX (UNIQUE SCAN) OF 'PK_SIKAKARI_M' (UNIQUE
) 12 4 INDEX (UNIQUE SCAN) OF 'PK_SIKAGRP_1_T' (UNIQUE)
13 3 INDEX (UNIQUE SCAN) OF 'PK_SIKAGRP_2_T' (UNIQUE)
14 2 INDEX (UNIQUE SCAN) OF 'PK_SIKA_HINMEI_M' (UNIQUE)
統計
----------------------------------------------------------
762 recursive calls
0 db block gets
220820 consistent gets
0 physical reads
0 redo size
3868 bytes sent via SQL*Net to client
591 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
130 rows processedSQL>