程序是根据客户帐号,客户分支号及币种类别进行查询,代码如下:
select f.broker_account,
a.branch_no,
a.money_type,
sum(a.post_balance) as post_balance,
min(f.broker_name) as broker_name
from hisfundjour a, client b, brokerclient e, broker f
where 1 = 1
and a.fund_account = e.fund_account
and e.fund_account = b.fund_account
and e.broker_account = f.broker_account
and a.business_flag in (4001, 4002)
group by f.broker_account, a.branch_no, a.money_type
order by f.broker_account, a.branch_no, a.money_type我plan了一下,结果如下,请各位帮帮忙,如何才能优化?
SELECT STATEMENT, GOAL = CHOOSE
SORT GROUP BY
TABLE ACCESS BY INDEX ROWID Object owner=LHCS Object name=HISFUNDJOUR
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL Object owner=LHCS Object name=BROKER
INDEX RANGE SCAN Object owner=LHCS Object name=IDX_BROKERCLIENT
INDEX UNIQUE SCAN Object owner=LHCS Object name=IDX_CLIENT
INDEX RANGE SCAN Object owner=LHCS Object name=IDX_HISFUNDJOUR_FUND
select f.broker_account,
a.branch_no,
a.money_type,
sum(a.post_balance) as post_balance,
min(f.broker_name) as broker_name
from hisfundjour a, client b, brokerclient e, broker f
where 1 = 1
and a.fund_account = e.fund_account
and e.fund_account = b.fund_account
and e.broker_account = f.broker_account
and a.business_flag in (4001, 4002)
group by f.broker_account, a.branch_no, a.money_type
order by f.broker_account, a.branch_no, a.money_type我plan了一下,结果如下,请各位帮帮忙,如何才能优化?
SELECT STATEMENT, GOAL = CHOOSE
SORT GROUP BY
TABLE ACCESS BY INDEX ROWID Object owner=LHCS Object name=HISFUNDJOUR
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL Object owner=LHCS Object name=BROKER
INDEX RANGE SCAN Object owner=LHCS Object name=IDX_BROKERCLIENT
INDEX UNIQUE SCAN Object owner=LHCS Object name=IDX_CLIENT
INDEX RANGE SCAN Object owner=LHCS Object name=IDX_HISFUNDJOUR_FUND
to bobfang 其实这四张表的数据量我也不是很清楚,
索引为 hisfundjour unique position_int(查询时的定位串,用来确定唯一一条数据)
normal branch_no,init_date(客户分支与初始化日期)
normal fund_account,position_int
client unique fund_account
normal fund_card
normal id_no
normal client_name
brokerclient unique broker_account,fund_account
normal fund_account
broker unique broker_account
brokerclient不完全是client子集,有些比如client_name字段在borkerclient中没有,而查询过程中实际有时候需要查client_name,所以关联表client还是必须的