oma,
ttex ttex_a,
ttex ttex_b,
tbooks,
tpdt,
trgppdt,
ttypobj
where ( ttraoma.txnum = topeoma.txnum )
and ( ttraoma.bkcod = tbooks.trscod )
and ( ttex_b.trscod = tbooks.permor )
and ( ttraoma.trscod = ttrcap_a.valnumcod )
and ( ttex_a.nttcod = ttrcap_b.valnumcod )
and ( ttypobj.objtyp = ttraoma.objtyp )
and ( ttraoma.trscod = ttex_a.trscod )
and ( ttrcap_a.colcod = :0 ) -- not selective
and ( ttrcap_b.colcod = :1 ) -- not selective
and ( ttraoma.pdtcod = tpdt.pdtcod )
and ( tpdt.risktyp = trgppdt.risktyp )
and ( tpdt.riskflg = trgppdt.riskflg )
and ( tpdt.pdtcod = trgppdt.pdtcod )
and ( trgppdt.risktyp = :2 ) -- not selective
and ( trgppdt.riskflg = :3 ) -- not selective
and ( ttraoma.txnum = tstg_a.txnum )
and ( ttrcap_a.refcod = :5 ) -- not selective
and ( ttrcap_b.refcod = :6 ) -- not selective
and ( tstg_a.risktyp = :4 ) -- not selective
and ( tstg_a.chncod = :7) -- not selective
and ( tstg_a.stgnum = :8 ) -- not selective
我们提供适当的参数(这里以:0 到:8 代表)执行此查询:耗时超过25 秒,返回记录不到20
条,做了3 000 次物理I/O,访问数据块3 000 000 次。上述统计数据反映了实际执行的情况,
这是必须首先明确的。下面,通过查询数据字典,得到表记录数情况:
TABLE_NAME NUM_ROWS
--------------------------- ----------
ttypobj 186
trgppdt 366
tpdt 5370
topeoma 12118
ttraoma 12118
tbooks 12268
ttex 102554
tt