索引优化

检查awr报告时看到elapsed time 最长的sql，如下：

select * from (select k.*, rownum rn from ( select distinct p.capitalSurplus, p.totalInterest,

p.overdueDays, p.actid, p.dinfId as id, p.loanId, p.lbi_pact_number as pactNumber,

p.lbi_frist_name || p.lbi_last_name as clientName, p.lbi_fact_loan_limit as factLoanLimit,

p.lbi_account_code as accountCode, p.dinf_app_state as appState, p.dpe_name as

settledPerson , trunc(p.dinf_allot_date) as allotDate, p.dpe_no as dpeNumber from ( select

finance.calc_Principal_Sur(c.act_id) as capitalSurplus, finance.calc_Over_Due_Money(c.act_id, 0,

to_char(sysdate, 'yyyy-MM-dd')) as overdueInterest, finance.calc_Over_Due_Money(c.act_id, 0,

to_char(sysdate,

'yyyy-MM-dd'))+finance.calc_All_Fee(c.act_id)+finance.calc_Over_Due_Interest(c.act_id, 0,

to_char(sysdate, 'yyyy-MM-dd')) as totalInterest, trunc(sysdate) - trunc(c.act_next_paydate) as

overdueDays, (case when (f.eni_cooa_date is not null) then f.eni_cooa_date else

a.dinf_allot_date end) as dinf_allot_date, z.dpe_name, a.dinf_app_state, a.dinf_overtime_date

as overtime, (case when (o.den_oca is not null) then o.den_oca else t.dpe_no end) as dpe_no,

b.lbi_account_code, b.lbi_fact_loan_limit, b.lbi_last_name, b.lbi_frist_name, b.lbi_pact_number,

c.act_id as actid, b.lbi_id as loanId, a.dinf_id as dinfId , (case when a.dinf_allot_date is not null

then dg.dgr_name else null end) dgrName from collect_loan a inner join copy_loan_bills b on

a.lbi_id = b.lbi_id inner join account c on b.lbi_id = c.act_clbi_id inner join loan_product g on

g.lpr_id= b.lbi_lpr_id inner join copy_clientinfo cl on cl.lbi_id=b.lbi_id and cl.cli_status=1 left join

copy_spouse spe on spe.cli_id = cl.cli_id left join collection_staff t on a.dpe_id=t.dpe_id left join

collection_staff z on z.dpe_id=a.dinf_settled_person left join oca_assignment f on

f.eni_id=a.dinf_current_entrust_id left join oca_master o on o.den_id=f.den_id left join

debt_group dg on dg.dgr_id=a.dinf_dgr_id left join EMPLOYEE_DPE_REL y on t.dpe_id=y.dpe_id

left join employee x on x.emp_id=y.emp_id where 1=1 and

x.emp_id='2c90928a3025fa9e01302b69e3920480' and trunc(sysdate) >

trunc(c.act_next_paydate) and (trunc(a.DINF_CHECK_TIME)<=trunc(sysdate) or

a.DINF_CHECK_TIME is null) and b.lbi_account_code not like 'ACCO%' and b.lbi_account_code

not like 'RWOCO%' and b.lbi_account_code not like 'WO%' and b.lbi_account_code not like '%XX'

and b.lbi_id in (select distinct a.lbi_id from copy_loan_bills a left join copy_clientinfo b on b.lbi_id

= a.lbi_id left join copy_address ad on ad.cli_id = b.cli_id left join copy_liaison_info alia on

alia.add_id = ad.add_id left join copy_spouse spo on spo.cli_id = b.cli_id left join copy_address

sadd on sadd.spo_id = spo.spo_id left join copy_liaison_info slia on slia.add_id = sadd.add_id left

join copy_profession_info_summary pro on pro.cli_id = b.cli_id left join copy_address padd on

padd.pis_id = pro.pis_id left join copy_liaison_info plia on plia.add_id = padd.add_id left join

copy_linkman man on man.cli_id = b.cli_id left join copy_phone_link_data phone on

phone.cli_id = b.cli_id left join copy_phone_link_data sphone on sphone.spo_id = spo.spo_id left

join copy_phone_link_data lphone on lphone.lin_id = man.lin_id left join copy_phone_link_data

cphone on cphone.pld_clientid = b.cli_id where b.cli_mobile_phone like :a or

alia.lia_zone||'-'||alia.lia_telephone||'-'||alia.lia_extension like :b or spo.spo_mobile_phone

like :c or slia.lia_zone||'-'||slia.lia_telephone||'-'||slia.lia_extension like :d or

plia.lia_zone||'-'||plia.lia_telephone||'-'||plia.lia_extension like :e or man.lin_add_telephone

like :f or man.lin_mobile_telephon like :g or man.lin_unit_phone like :h or

phone.pld_area_code||'-'||phone.pld_tel_number||'-'||phone.pld_extension like :i or

sphone.pld_area_code||'-'||sphone.pld_tel_number||'-'||sphone.pld_extension like :j or

lphone.pld_area_code||'-'||lphone.pld_tel_number||'-'||lphone.pld_extension like :k or

cphone.pld_area_code||'-'||cphone.pld_tel_number||'-'||cphone.pld_extension like :l ) ) p

where 1=1 order by pactNumber ) k ) t where t.rn>=:m and t.rn<=:n

用autotrace分析一下该语句：

发现有table full scan。

于是在语句中找到该表的被查询字段，果然没有索引，马上建立索引，最后再次执行，时间缩短了三分之二。、

索引的确重要。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25915379/viewspace-730582/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25915379/viewspace-730582/