--SQL段A:
SELECT MIN(pp.transaction_log_fk)
FROM pol_prem pp,
pol_term_history pth
WHERE pp.record_mode_code = 'OFFICIAL'
AND pp.rate_period_type_code = 'TERM_COVG'
AND pp.coverage_component_code = 'NETPREM'
AND pp.accounting_to_date = to_date('01/01/3000','MM/DD/YYYY')
AND pp.transaction_log_fk = pth.last_transaction_fk
and pth.pol_term_history_pk = (
select min(pth.pol_term_history_pk)
from pol_term_history pth
where pth.policy_fk = 28478970
and pth.base_record_b = 'N' )
AND pp.policy_fk = 28478970;--SQL段B:
SELECT MIN(pp.transaction_log_fk)
FROM pol_prem pp,
pol_term_history pth
WHERE pp.record_mode_code = 'OFFICIAL'
AND pp.rate_period_type_code = 'TERM_COVG'
AND pp.coverage_component_code = 'NETPREM'
AND pp.accounting_to_date = to_date('01/01/3000','MM/DD/YYYY')
AND pp.transaction_log_fk = pth.last_transaction_fk
AND pth.pol_term_history_pk = (
SELECT MIN(pth.pol_term_history_pk)
FROM pol_term_history pth2
WHERE pth2.policy_fk = 28478970
AND pth2.base_record_b = 'N' )
AND pp.policy_fk = 28478970; 这两段SQL的执行结果是一样的,但执行时间却大相径庭:SQL-A只需要0.2~0.3秒,SQL-B却足足花了47秒钟!怎么会差这么多啊?只不过其子查询中表的别名不一样罢了。望诸位高人指点一二,多谢了!
SELECT MIN(pp.transaction_log_fk)
FROM pol_prem pp,
pol_term_history pth
WHERE pp.record_mode_code = 'OFFICIAL'
AND pp.rate_period_type_code = 'TERM_COVG'
AND pp.coverage_component_code = 'NETPREM'
AND pp.accounting_to_date = to_date('01/01/3000','MM/DD/YYYY')
AND pp.transaction_log_fk = pth.last_transaction_fk
and pth.pol_term_history_pk = (
select min(pth.pol_term_history_pk)
from pol_term_history pth
where pth.policy_fk = 28478970
and pth.base_record_b = 'N' )
AND pp.policy_fk = 28478970;--SQL段B:
SELECT MIN(pp.transaction_log_fk)
FROM pol_prem pp,
pol_term_history pth
WHERE pp.record_mode_code = 'OFFICIAL'
AND pp.rate_period_type_code = 'TERM_COVG'
AND pp.coverage_component_code = 'NETPREM'
AND pp.accounting_to_date = to_date('01/01/3000','MM/DD/YYYY')
AND pp.transaction_log_fk = pth.last_transaction_fk
AND pth.pol_term_history_pk = (
SELECT MIN(pth.pol_term_history_pk)
FROM pol_term_history pth2
WHERE pth2.policy_fk = 28478970
AND pth2.base_record_b = 'N' )
AND pp.policy_fk = 28478970; 这两段SQL的执行结果是一样的,但执行时间却大相径庭:SQL-A只需要0.2~0.3秒,SQL-B却足足花了47秒钟!怎么会差这么多啊?只不过其子查询中表的别名不一样罢了。望诸位高人指点一二,多谢了!
SELECT MIN(pp.transaction_log_fk)
FROM pol_prem pp,
pol_term_history pth
WHERE pp.record_mode_code = 'OFFICIAL'
AND pp.rate_period_type_code = 'TERM_COVG'
AND pp.coverage_component_code = 'NETPREM'
AND pp.accounting_to_date = to_date('01/01/3000','MM/DD/YYYY')
AND pp.transaction_log_fk = pth.last_transaction_fk
AND pth.pol_term_history_pk = (
SELECT MIN(pth2.pol_term_history_pk)
FROM pol_term_history pth2
WHERE pth2.policy_fk = 28478970
AND pth2.base_record_b = 'N' )
AND pp.policy_fk = 28478970;
SORT AGGREGATE Cardinality=1 Bytes=63
TABLE ACCESS BY INDEX ROWID Object owner=ICME Object name=POL_PREM Cost=1 Cardinality=1 Bytes=49
NESTED LOOPS Cost=2 Cardinality=1 Bytes=63
TABLE ACCESS BY INDEX ROWID Object owner=ICME Object name=POL_TERM_HISTORY Cost=1 Cardinality=1 Bytes=14
INDEX RANGE SCAN Object owner=ICME Object name=PTH_0 Cost=1 Cardinality=1
SORT AGGREGATE Cardinality=1 Bytes=16
TABLE ACCESS BY INDEX ROWID Object owner=ICME Object name=POL_TERM_HISTORY Cost=1 Cardinality=3 Bytes=48
INDEX RANGE SCAN Object owner=ICME Object name=PTH_1 Cost=1 Cardinality=7
INDEX RANGE SCAN Object owner=ICME Object name=PM_FPREM_N1 Cost=1 Cardinality=198
这是SQL-B的:
SELECT STATEMENT, GOAL = ALL_ROWS Cost=149 Cardinality=1 Bytes=63
SORT AGGREGATE Cardinality=1 Bytes=63
FILTER
HASH JOIN Cost=148 Cardinality=1 Bytes=63
TABLE ACCESS BY INDEX ROWID Object owner=ICME Object name=POL_PREM Cost=7 Cardinality=1 Bytes=49
INDEX RANGE SCAN Object owner=ICME Object name=PM_FPREM_N4 Cost=1 Cardinality=1004
TABLE ACCESS FULL Object owner=ICME Object name=POL_TERM_HISTORY Cost=140 Cardinality=27285 Bytes=381990
SORT AGGREGATE Cardinality=1 Bytes=9
TABLE ACCESS BY INDEX ROWID Object owner=ICME Object name=POL_TERM_HISTORY Cost=1 Cardinality=3 Bytes=27
INDEX RANGE SCAN Object owner=ICME Object name=PTH_1 Cost=1 Cardinality=7 再次感谢!