SELECT
cli_id,
co_id,
rel_sys_id,
rel_sys_ref_id,
rel_typ_cd,
MAX(INSERTDATE) INSERTDATE
FROM KPIODS.ODS_TRL_ING B
WHERE B.INSERTDATE < DATE'2012-8-7'
AND cli_id || co_id || rel_sys_id || rel_sys_ref_id || rel_typ_cd IN
(SELECT cli_id || co_id || rel_sys_id || rel_sys_ref_id ||
rel_typ_cd
FROM KPIODS.ODS_TRL_ING B
WHERE B.INSERTDATE >=DATE'2012-8-7'
and B.INSERTDATE <= DATE'2012-8-8')
GROUP BY cli_id, co_id, rel_sys_id, rel_sys_ref_id, rel_typ_cdSELECT STATEMENT, GOAL = ALL_ROWS 6633362 339223431 28494768204 575484025529 6607856
HASH GROUP BY 6633362 339223431 28494768204 575484025529 6607856 34307646000
HASH JOIN 14752 339223431 28494768204 37019067111 13111
TABLE ACCESS BY INDEX ROWID KPIODS ODS_TRL_ING 66 4596 193032 2778275 66
INDEX RANGE SCAN KPIODS IDX_TRL_INSERTDATE 15 4525 1012022 15
TABLE ACCESS FULL KPIODS ODS_TRL_ING 13182 7381513 310023546 3081974783 13045 问题1:如上sql执行计划中,临时空间 为:34307646000,这个临时空间,在sql执行时是占用的内存吗?单位是什么
我将上边黑体的部分,做了如下修改:
SELECT
cli_id,
co_id,
rel_sys_id,
rel_sys_ref_id,
rel_typ_cd,
MAX(INSERTDATE) INSERTDATE
FROM KPIODS.ODS_TRL_ING B
WHERE B.INSERTDATE < DATE'2012-8-7'
AND cli_id /*|| co_id || rel_sys_id || rel_sys_ref_id || rel_typ_cd*/ IN
(SELECT cli_id || co_id || rel_sys_id || rel_sys_ref_id ||
rel_typ_cd
FROM KPIODS.ODS_TRL_ING B
WHERE B.INSERTDATE >=DATE'2012-8-7'
and B.INSERTDATE <= DATE'2012-8-8')
GROUP BY cli_id, co_id, rel_sys_id, rel_sys_ref_id, rel_typ_cd
SELECT STATEMENT, GOAL = ALL_ROWS 14472 60920 5117280 3920002183 14298
HASH GROUP BY 14472 60920 5117280 3920002183 14298 6177000
HASH JOIN 13281 60920 5117280 3834875311 13111
TABLE ACCESS BY INDEX ROWID KPIODS ODS_TRL_ING 66 4596 193032 2778275 66
INDEX RANGE SCAN KPIODS IDX_TRL_INSERTDATE 15 4525 1012022 15
TABLE ACCESS FULL KPIODS ODS_TRL_ING 13182 7381513 310023546 3081974783 13045
问题2:临时空间,就减少至 6177000,我查过了cli_id || co_id || rel_sys_id || rel_sys_ref_id ||rel_typ_cd,并到一起的最大长度是31个字符。也不是很大,为什么会差这么多临时空间?
cli_id,
co_id,
rel_sys_id,
rel_sys_ref_id,
rel_typ_cd,
MAX(INSERTDATE) INSERTDATE
FROM KPIODS.ODS_TRL_ING B
WHERE B.INSERTDATE < DATE'2012-8-7'
AND cli_id || co_id || rel_sys_id || rel_sys_ref_id || rel_typ_cd IN
(SELECT cli_id || co_id || rel_sys_id || rel_sys_ref_id ||
rel_typ_cd
FROM KPIODS.ODS_TRL_ING B
WHERE B.INSERTDATE >=DATE'2012-8-7'
and B.INSERTDATE <= DATE'2012-8-8')
GROUP BY cli_id, co_id, rel_sys_id, rel_sys_ref_id, rel_typ_cdSELECT STATEMENT, GOAL = ALL_ROWS 6633362 339223431 28494768204 575484025529 6607856
HASH GROUP BY 6633362 339223431 28494768204 575484025529 6607856 34307646000
HASH JOIN 14752 339223431 28494768204 37019067111 13111
TABLE ACCESS BY INDEX ROWID KPIODS ODS_TRL_ING 66 4596 193032 2778275 66
INDEX RANGE SCAN KPIODS IDX_TRL_INSERTDATE 15 4525 1012022 15
TABLE ACCESS FULL KPIODS ODS_TRL_ING 13182 7381513 310023546 3081974783 13045 问题1:如上sql执行计划中,临时空间 为:34307646000,这个临时空间,在sql执行时是占用的内存吗?单位是什么
我将上边黑体的部分,做了如下修改:
SELECT
cli_id,
co_id,
rel_sys_id,
rel_sys_ref_id,
rel_typ_cd,
MAX(INSERTDATE) INSERTDATE
FROM KPIODS.ODS_TRL_ING B
WHERE B.INSERTDATE < DATE'2012-8-7'
AND cli_id /*|| co_id || rel_sys_id || rel_sys_ref_id || rel_typ_cd*/ IN
(SELECT cli_id || co_id || rel_sys_id || rel_sys_ref_id ||
rel_typ_cd
FROM KPIODS.ODS_TRL_ING B
WHERE B.INSERTDATE >=DATE'2012-8-7'
and B.INSERTDATE <= DATE'2012-8-8')
GROUP BY cli_id, co_id, rel_sys_id, rel_sys_ref_id, rel_typ_cd
SELECT STATEMENT, GOAL = ALL_ROWS 14472 60920 5117280 3920002183 14298
HASH GROUP BY 14472 60920 5117280 3920002183 14298 6177000
HASH JOIN 13281 60920 5117280 3834875311 13111
TABLE ACCESS BY INDEX ROWID KPIODS ODS_TRL_ING 66 4596 193032 2778275 66
INDEX RANGE SCAN KPIODS IDX_TRL_INSERTDATE 15 4525 1012022 15
TABLE ACCESS FULL KPIODS ODS_TRL_ING 13182 7381513 310023546 3081974783 13045
问题2:临时空间,就减少至 6177000,我查过了cli_id || co_id || rel_sys_id || rel_sys_ref_id ||rel_typ_cd,并到一起的最大长度是31个字符。也不是很大,为什么会差这么多临时空间?
你的sql如下改造
同时在 (CLI_ID, CO_ID, REL_SYS_ID, REL_SYS_REF_ID, REL_TYP_CD) 上建一个组合索引SELECT CLI_ID,
CO_ID,
REL_SYS_ID,
REL_SYS_REF_ID,
REL_TYP_CD,
MAX(INSERTDATE) INSERTDATE
FROM KPIODS.ODS_TRL_ING B
WHERE B.INSERTDATE < DATE '2012-8-7'
AND (CLI_ID, CO_ID, REL_SYS_ID, REL_SYS_REF_ID, REL_TYP_CD) IN
(SELECT CLI_ID, CO_ID, REL_SYS_ID, REL_SYS_REF_ID, REL_TYP_CD
FROM KPIODS.ODS_TRL_ING B
WHERE B.INSERTDATE >= DATE '2012-8-7'
AND B.INSERTDATE <= DATE '2012-8-8')
GROUP BY CLI_ID, CO_ID, REL_SYS_ID, REL_SYS_REF_ID, REL_TYP_CD
SELECT STATEMENT, GOAL = ALL_ROWS 13282 4596 386064 3859957675 13111
HASH GROUP BY 13282 4596 386064 3859957675 13111
HASH JOIN 13281 4596 386064 3834875311 13111
TABLE ACCESS BY INDEX ROWID KPIODS ODS_TRL_ING 66 4596 193032 2778275 66
INDEX RANGE SCAN KPIODS IDX_TRL_INSERTDATE 15 4525 1012022 15
TABLE ACCESS FULL KPIODS ODS_TRL_ING 13182 7381513 310023546 3081974783 13045
竟然不占用 临时空间了,
临时空间的单位,大大知道吗?
是记录在执行这段SQL时总共占的字节数是多少