一个SQL的优化过程

　　1652
　　ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
　　
　　0
　　select GAME_CARD_TYPE.NAME,
　　GAME.NAME GameName,
　　sum(V_SALE_TMP_LOG.GAME_CARD_NUM) as num,
　　sum(V_SALE_TMP_LOG.CITY_AGENT_COST) as sumSalePrice,
　　sum(V_SALE_TMP_LOG.PROVINCE_AGENT_COST) as basePrice,
　　V_SALE_TMP_LOG.SALE_MODE
　　from V_SALE_TMP_LOG,
　　GAME_CARD_TYPE,
　　GAME,
　　RESELLER_BASE,
　　AGENT_BASE c,
　　AGENT_BASE d
　　where (V_SALE_TMP_LOG.SALE_MODE=2 or V_SALE_TMP_LOG.SALE_MODE=3 or V_SALE_TMP_LOG.SALE_MODE=4)
　　and V_SALE_TMP_LOG.GAME_CARD_TYPE_ID=GAME_CARD_TYPE.GAME_CARD_TYPE_ID(+)
　　and GAME_CARD_TYPE.GAME_ID=GAME.GAME_ID(+)
　　and V_SALE_TMP_LOG.RESELLER_ID=RESELLER_BASE.RESELLER_ID
　　and RESELLER_BASE.AGENT_ID=c.AGENT_ID
　　and c.PARENT_AGENT_ID = d.AGENT_ID
　　and V_SALE_TMP_LOG.IS_SUCCESS='Y'
　　and d.AGENT_ID=52080
　　and V_SALE_TMP_LOG.LOG_TIME>=to_date('2004-05-04 00:00:00','yyyy-mm-dd HH24:MI:SS') and V_SALE_TMP_LOG.LOG_TIME<=to_date('2004-07-04 23:59:59','yyyy-mm-dd HH24:MI:SS')
　　GROUP BY GAME.name,
　　GAME_CARD_TYPE.NAME,
　　V_SALE_TMP_LOG.SALE_MODE
　　ORDER BY -sum(V_SALE_TMP_LOG.CITY_AGENT_COST) DESC,sum(V_SALE_TMP_LOG.GAME_CARD_NUM) DESC
　　
　　这个SQL在执行时将2G的TEMP表空间溢出来，通过lecco sql ecpert对该SQL做了分析
　　一个SQL的优化过程

　　SQL> l
　　1　select GAME_CARD_TYPE.NAME,
　　2　　　　　　　　　　　　　　　　　GAME.NAME GameName,
　　3　　　　　　　　　　　　　　　　　sum(V_SALE_TMP_LOG.GAME_CARD_NUM) as num,
　　4　　　　　　　　　　　　　　　　　sum(V_SALE_TMP_LOG.CITY_AGENT_COST) as sumSalePrice,
　　5　　　　　　　　　　　　　　　　　sum(V_SALE_TMP_LOG.PROVINCE_AGENT_COST) as basePrice,
　　6　　　　　　　　　　　　　　　　　V_SALE_TMP_LOG.SALE_MODE
　　7　　　　　　　　　　　　　 from V_SALE_TMP_LOG,
　　8　　　　　　　　　　　　　　　　GAME_CARD_TYPE,
　　9　　　　　　　　　　　　　　　　GAME,
　　10　　　　　　　　　　　　　　　　RESELLER_BASE,
　　11　　　　　　　　　　　　　　　　AGENT_BASE c,
　　12　　　　　　　　　　　　　　　　AGENT_BASE d
　　13　　　　　　　　　　　　　 where (V_SALE_TMP_LOG.SALE_MODE=2 or V_SALE_TMP_LOG.SALE_MODE=3 or V_SALE_TMP_LOG.SALE_MODE=4)
　　14　　　　　　　　　　　　　　　　 and V_SALE_TMP_LOG.GAME_CARD_TYPE_ID=GAME_CARD_TYPE.GAME_CARD_TYPE_ID(+)
　　15　　　　　　　　　　　　　　　　 and GAME_CARD_TYPE.GAME_ID=GAME.GAME_ID(+)
　　16　　　　　　　　　　　　　　　　 and V_SALE_TMP_LOG.RESELLER_ID=RESELLER_BASE.RESELLER_ID
　　17　　　　　　　　　　　　　　　　 and RESELLER_BASE.AGENT_ID=c.AGENT_ID
　　18　　　　　　　　　　　　　　　　 and c.PARENT_AGENT_ID = d.AGENT_ID
　　19　　　　　　　　　　　　　　　　 and V_SALE_TMP_LOG.IS_SUCCESS='Y'
　　20　　　　　　　　　　　　　　　　 and d.AGENT_ID=52080
　　21　　　　　　　　　　　　　　　　　and V_SALE_TMP_LOG.LOG_TIME>=to_date('2004-05-04 00:00:00','yyyy-mm-dd HH24:MI:SS') and V_SALE_TMP_LOG.LOG_TIME<
=to_date('2004-07-04 23:59:59','yyyy-mm-dd HH24:MI:SS')
　　22　　　　　　　　　　　　　 GROUP BY GAME.name,
　　23　　　　　　　　　　　　　　　　　　GAME_CARD_TYPE.NAME,
　　24　　　　　　　　　　　　　　　　　　V_SALE_TMP_LOG.SALE_MODE
　　25　　　　　　　　　　　　　 ORDER BY -sum(V_SALE_TMP_LOG.CITY_AGENT_COST) DESC,sum(V_SALE_TMP_LOG.GAME_CARD_NUM) DESC
　　26*
　　SQL>
　　
　　162 rows selected.
　　
　　Elapsed: 00:00:00.77
　　
　　Execution Plan
　　----------------------------------------------------------
　　0　　　SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2043 Card=334 Byte
　　s=26386)
　　
　　1　　0　 SORT (ORDER BY) (Cost=2043 Card=334 Bytes=26386)
　　2　　1　　 SORT (GROUP BY) (Cost=2043 Card=334 Bytes=26386)
　　3　　2　　　 HASH JOIN (OUTER) (Cost=1969 Card=334 Bytes=26386)
　　4　　3　　　　 HASH JOIN (OUTER) (Cost=1964 Card=334 Bytes=21710)
　　5　　4　　　　　 NESTED LOOPS (Cost=1959 Card=334 Bytes=16032)
　　6　　5　　　　　　 NESTED LOOPS (Cost=13 Card=47 Bytes=893)
　　7　　6　　　　　　　 NESTED LOOPS (Cost=4 Card=1 Bytes=12)
　　8　　7　　　　　　　　 INDEX (UNIQUE SCAN) OF 'SYS_C0010232' (UNIQU
　　E) (Cost=1 Card=1 Bytes=4)
　　
　　9　　7　　　　　　　　 TABLE Access (BY INDEX ROWID) OF 'AGENT_BASE
　　' (Cost=3 Card=1 Bytes=8)
　　
　　10　　9　　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_BASE_AGENT_ID'
　　(NON-UNIQUE) (Cost=1 Card=1)
　　
　　11　　6　　　　　　　 TABLE ACCESS (BY INDEX ROWID) OF 'RESELLER_BAS
　　E' (Cost=10 Card=47 Bytes=329)
　　
　　12　 11　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_RESELLER_BASE_AGE
　　NT_ID' (NON-UNIQUE) (Cost=1 Card=47)
　　
　　13　　5　　　　　　 VIEW OF 'V_SALE_TMP_LOG' (Cost=42 Card=7 Bytes=2
　　03)
　　
　　14　 13　　　　　　　 UNION-ALL (PARTITION)
　　15　 14　　　　　　　　 TABLE ACCESS (BY INDEX ROWID) OF 'SALE_TMP_L
　　OG' (Cost=22 Card=26 Bytes=728)
　　
　　16　 15　　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_RESEID_STL' (NO
　　N-UNIQUE) (Cost=3 Card=79)
　　
　　17　 14　　　　　　　　 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'SAL
　　E_TMP_LOG_DELETED' (Cost=2 Card=1 Bytes=28)
　　
　　18　 17　　　　　　　　　 INDEX (RANGE SCAN) OF 'IDX_SALE_DELETED_LO
　　G_TIME' (NON-UNIQUE) (Cost=3 Card=1)
　　
　　19　　4　　　　　 TABLE ACCESS (FULL) OF 'GAME_CARD_TYPE' (Cost=4 Ca
　　rd=1084 Bytes=18428)
　　
　　20　　3　　　　 TABLE ACCESS (FULL) OF 'GAME' (Cost=4 Card=218 Bytes
　　=3052)
　　
　　Statistics
　　----------------------------------------------------------
　　357　recursive calls
　　0　db block gets
　　41519　consistent gets
　　924　physical reads
　　0　redo size
　　7929　bytes sent via SQL*Net to client
　　609　bytes received via SQL*Net from client
　　12　SQL*Net roundtrips to/from client
　　2　sorts (memory)
　　0　sorts (disk)
　　162　rows processed
　　
　　这是源SQL执行后的结果
　　
　　SQL> l
　　1　select /*+ USE_HASH(V_SALE_TMP_LOG) */ GAME_CARD_TYPE.NAME,
　　2　　　　 GAME.NAME GameName,
　　3　　　　 sum(V_SALE_TMP_LOG.GAME_CARD_NUM) as num,
　　4　　　　 sum(V_SALE_TMP_LOG.CITY_AGENT_COST) as sumSalePrice,
　　5　　　　 sum(V_SALE_TMP_LOG.PROVINCE_AGENT_COST) as basePrice,
　　6　　　　 V_SALE_TMP_LOG.SALE_MODE
　　7　　from V_SALE_TMP_LOG,
　　8　　　　 GAME_CARD_TYPE,
　　9　　　　 GAME,
　　10　　　　 RESELLER_BASE,
　　11　　　　 AGENT_BASE c,
　　12　　　　 AGENT_BASE d
　　13　 where (V_SALE_TMP_LOG.SALE_MODE = 2
　　14　　　　　 or V_SALE_TMP_LOG.SALE_MODE = 3
　　15　　　　　 or V_SALE_TMP_LOG.SALE_MODE = 4)
　　16　　 and V_SALE_TMP_LOG.GAME_CARD_TYPE_ID = GAME_CARD_TYPE.GAME_CARD_TYPE_ID (+)
　　17　　 and GAME_CARD_TYPE.GAME_ID = GAME.GAME_ID (+)
　　18　　 and V_SALE_TMP_LOG.RESELLER_ID = RESELLER_BASE.RESELLER_ID
　　19　　 and RESELLER_BASE.AGENT_ID = c.AGENT_ID
　　20　　 and c.PARENT_AGENT_ID = d.AGENT_ID
　　21　　 and V_SALE_TMP_LOG.IS_SUCCESS = 'Y'
　　22　　 and d.AGENT_ID = 52080
　　23　　 and V_SALE_TMP_LOG.LOG_TIME >
= to_date('2004-05-04 00:00:00', 'yyyy-mm-dd HH24:MI:SS')
　　24　　 and V_SALE_TMP_LOG.LOG_TIME <= to_date('2004-07-04 23:59:59', 'yyyy-mm-dd HH24:MI:SS')
　　25　 GROUP BY GAME.name,
　　26　　　　　　GAME_CARD_TYPE.NAME,
　　27　　　　　　V_SALE_TMP_LOG.SALE_MODE
　　28　 ORDER BY -sum(V_SALE_TMP_LOG.CITY_AGENT_COST) DESC,
　　29　　　　　　sum(V_SALE_TMP_LOG.GAME_CARD_NUM) DESC
　　30*
　　
　　162 rows selected.
　　
　　Elapsed: 00:00:04.09
　　
　　Execution Plan
　　----------------------------------------------------------
　　0　　　SELECT STATEMENT Optimizer=ALL_ROWS (Cost