程序是根据客户帐号,客户分支号及币种类别进行查询,代码如下:
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
解决方案 »
- 求Oracle语句
- 使用链接服务器,更新Oracle的数据,出现实际值: 4, 最大值: 1
- 向大家请教个关于decode函数的问题
- oracle版本升级后程序链接出错
- WINXP下10g客户端连接linux 10g服务端问题及如何plsqldev如何连接linux 10g服务端问题!
- 两个oracle 之间 导入导出表
- ◆如何完全删除ORALCE客户端
- 我在oracle中只能使用长度为4000的vchar类型,有没有更长一点的类型啊,或者用其它方法来解决。
- 高手请进!!!!!关于ORACLE中的sql语句包含NOT EXISTS的问题
- Help! ora-4031
- 如何写两个表的关联和模糊查询同时存在的sql
- 帮忙看一下此SQL如何优化?有点晕了
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还是必须的