62,025
社区成员
发帖
与我相关
我的任务
分享
SELECT SUM(ATTRIB.STANDARD_VALUE+NVL(TT2.STANDARD_ADJMNT_VALUE,0)) FROM TT1 ,TT2, TT3
WHERE TT1.ATRBT_ID = 1015
AND TT1.SRS_ID = 1000
AND TT1.DATE_DIMNSN_KEY = TT3.DATE_MONTH_DIMNSN_KEY
AND TT1.ATRBT_ID=TT2.ATRBT_ID(+)
AND TT1.SRS_ID = TT2.SRS_ID(+)
AND TT1.DATE_DIMNSN_KEY=TT2.DATE_MONTH_DIMNSN_KEY(+)
AND TT3.CALENDAR_YEAR_MONTH_CDE <= 200401
AND TT3.CALENDAR_YEAR_MONTH_CDE >= 200401
一个shcema下的执行计划(很慢)
SELECT STATEMENT, GOAL = CHOOSE Cost=5 Cardinality=1 Bytes=74 IO cost=5
SORT AGGREGATE Cardinality=1 Bytes=74
SORT AGGREGATE Cardinality=1 Bytes=74
NESTED LOOPS Cost=5 Cardinality=1 Bytes=74 IO cost=5
NESTED LOOPS OUTER Cost=4 Cardinality=3 Bytes=195 IO cost=4
TABLE ACCESS FULL Object owner=WJZ Object name=TT1 Cost=4 Cardinality=3 Bytes=39 IO cost=4
TABLE ACCESS BY INDEX ROWID Object owner=WJZ Object name=TT2 Cardinality=1 Bytes=52
INDEX UNIQUE SCAN Object owner=WJZ Object name=TT2_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=WJZ Object name=TT3 Cost=1 Cardinality=1 Bytes=9 IO cost=1
INDEX UNIQUE SCAN Object owner=WJZ Object name=DATE_MONTH_DIMNSN_PK Cardinality=1
另一个shcema下的执行计划(很快)
SELECT STATEMENT, GOAL = CHOOSE Cost=3 Cardinality=1 Bytes=122 IO cost=3
SORT AGGREGATE Cardinality=1 Bytes=122
NESTED LOOPS Cost=3 Cardinality=1 Bytes=122 IO cost=3
NESTED LOOPS OUTER Cost=2 Cardinality=1 Bytes=104 IO cost=2
TABLE ACCESS BY INDEX ROWID Object owner=TEST_PRC Object name=TT1 Cost=1 Cardinality=1 Bytes=52 IO cost=1
INDEX RANGE SCAN Object owner=TEST_PRC Object name=TT1_PK Cost=2 Cardinality=1 IO cost=2
TABLE ACCESS BY INDEX ROWID Object owner=TEST_PRC Object name=TT2 Cost=1 Cardinality=1 Bytes=52 IO cost=1
INDEX UNIQUE SCAN Object owner=TEST_PRC Object name=TT2_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=TEST_PRC Object name=TT3 Cost=1 Cardinality=1 Bytes=18 IO cost=1
INDEX UNIQUE SCAN Object owner=TEST_PRC Object name=DATE_MONTH_DIMNSN_PK Cardinality=1