我的SQL语句:
Select 1 from fexpense e, fconsign c, csubchargeitem s, ccustsuppview csv, cuser cu1, cuser cu2 where (e.fexp_creator = :1 or exists (select f.FCSG_CONSIGN_ID from FCONSIGN f where f.fcsg_consign_id=e.fexp_consign_id and f.fcsg_creator = :"SYS_B_04")) and e.fexp_consign_id = c.fcsg_consign_id(+) and e.fexp_charge_id = s.csci_ci_id(+) and e.fexp_settlerment_object = csv.accountNumber(+) and c.fcsg_creator = cu1.cusr_user_id(+) and c.fcsg_canvasser = cu2.cusr_user_id(+) and (e.fexp_forward_flag < :"SYS_B_05" or e.fexp_forward_flag > :"SYS_B_05") and (e.fexp_cancel_flag < :"SYS_B_06" or e.fexp_cancel_flag > :"SYS_B_06") and (e.fexp_lump_flag < :"SYS_B_07" or e.fexp_lump_flag > :"SYS_B_07") and (e.fexp_consign_audit_flag = :"SYS_B_08" or e.fexp_lock_flag = :"SYS_B_09") and e.fexp_org_id = :2 order by e.fexp_settlerment_object ASC, e.fexp_currency_code ASC
我想避免fexpense的全表扫描,fexpense已经存在的索引有FEXPENSE_P1 Normal FEXP_ORG_ID,FEXP_CREATE_TIME
IDX_FEXP_ORG_CONSIGN Normal FEXP_ORG_ID,FEXP_CONSIGN_ID
IDX_FEXP_ORG_NO Normal FEXP_ORG_ID,FEXP_BILL_NO
IDX_FEXP_ORG_SETTLEMENT Normal FEXP_ORG_ID,FEXP_SETTLERMENT_OBJECT
IDX_FEXP_ORG_TAX Normal FEXP_ORG_ID,FEXP_TAX_INVOICE_NO
PK_FEXPENSE Unique FEXP_EXPENSE_ID.我建立了一个WHERE条件中用到字段的一个联合索引,结果无效,我用的是PL/SQL,按F5看的执行计划,我不明白要怎么避免fexpense的全表扫描,请大家多提点意见。
Select 1 from fexpense e, fconsign c, csubchargeitem s, ccustsuppview csv, cuser cu1, cuser cu2 where (e.fexp_creator = :1 or exists (select f.FCSG_CONSIGN_ID from FCONSIGN f where f.fcsg_consign_id=e.fexp_consign_id and f.fcsg_creator = :"SYS_B_04")) and e.fexp_consign_id = c.fcsg_consign_id(+) and e.fexp_charge_id = s.csci_ci_id(+) and e.fexp_settlerment_object = csv.accountNumber(+) and c.fcsg_creator = cu1.cusr_user_id(+) and c.fcsg_canvasser = cu2.cusr_user_id(+) and (e.fexp_forward_flag < :"SYS_B_05" or e.fexp_forward_flag > :"SYS_B_05") and (e.fexp_cancel_flag < :"SYS_B_06" or e.fexp_cancel_flag > :"SYS_B_06") and (e.fexp_lump_flag < :"SYS_B_07" or e.fexp_lump_flag > :"SYS_B_07") and (e.fexp_consign_audit_flag = :"SYS_B_08" or e.fexp_lock_flag = :"SYS_B_09") and e.fexp_org_id = :2 order by e.fexp_settlerment_object ASC, e.fexp_currency_code ASC
我想避免fexpense的全表扫描,fexpense已经存在的索引有FEXPENSE_P1 Normal FEXP_ORG_ID,FEXP_CREATE_TIME
IDX_FEXP_ORG_CONSIGN Normal FEXP_ORG_ID,FEXP_CONSIGN_ID
IDX_FEXP_ORG_NO Normal FEXP_ORG_ID,FEXP_BILL_NO
IDX_FEXP_ORG_SETTLEMENT Normal FEXP_ORG_ID,FEXP_SETTLERMENT_OBJECT
IDX_FEXP_ORG_TAX Normal FEXP_ORG_ID,FEXP_TAX_INVOICE_NO
PK_FEXPENSE Unique FEXP_EXPENSE_ID.我建立了一个WHERE条件中用到字段的一个联合索引,结果无效,我用的是PL/SQL,按F5看的执行计划,我不明白要怎么避免fexpense的全表扫描,请大家多提点意见。
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDER BY中所有的列必须定义为非空. 建议你多看看 ORACLE SQL性能优化
我执行
select 1 from tab t where t.a=:"1"和
select 1 from tab t where t.b=:"1"
看执行计划,第一条有用到索引,第二条没有索引是什么原因??
Analyze Table fexpense compute Statistics; ---更新数据统计
还是不行,COST成本反而增加了些急救。。
我把order by的项目去掉还是一样全表扫描,有没有人可以解决一下。
fexp_creator, fexp_consign_id,
fexp_forward_flag, fexp_cancel_flag, fexp_lump_flag,
fexp_consign_audit_flag, fexp_lock_flag, fexp_org_id,
fexp_charge_id, fexp_settlerment_object,
--没有必要片面追求走索引
/* 比如
我有张表tab,分别建立索引IDX_A(字段a),IDX_B(字段b)
我执行
select 1 from tab t where t.a=:"1"和
select 1 from tab t where t.b=:"1"
看执行计划,第一条有用到索引,第二条没有索引
*/
/*
就是因为Oracle的优化器认为第二条如果走索引效率反而更低
*/