--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秒钟!怎么会差这么多啊?只不过其子查询中表的别名不一样罢了。望诸位高人指点一二,多谢了!
解决方案 »
- OracleDBConsoleorcl服务启动不了!
- 国庆,祝大家快乐^_^
- [讨论]刚才发现like时Oracle貌似是不区分'_'和'_'
- 关于Toad的Debugger的问题
- 使用pl/sql dev的问题,顺便散分
- ORA-06553错误什么意思?
- 关于oracle数据库的一个简单问题---创建或删除数据表
- 我今天突然发现在Export数据库导出命令用不了?为什么?请大家帮忙指点.
- 能否更新oracle 8i库中按照某个字段降序排列后的前几条记录呢?
- 求解:ORA-01081: cannot start already-running ORACLE - shut it down first
- 询问一句按旬汇总的语句
- oracle如何导出table的c语言结构?
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 再次感谢!