- SQL code
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 [b]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[/b] 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
- SQL code
SELECT STATEMENT, GOAL = ALL_ROWS 6633362 339223431 28494768204 575484025529 6607856 HASH GROUP BY 6633362 339223431 28494768204 575484025529 6607856 [color=#FF0000]34307646000[/color] 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执行时是占用的内存吗？单位是什么
我将上边黑体的部分，做了如下修改：
- SQL code
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
- SQL code
SELECT STATEMENT, GOAL = ALL_ROWS 14472 60920 5117280 3920002183 14298 HASH GROUP BY 14472 60920 5117280 3920002183 14298 [color=#FF0000]6177000[/color] 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) 上建一个组合索引
- SQL code
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