一条SQL语句调优，急急

[tandy_cs_201]

Select ifdl.ifdl_dp_number,
ifdh.ifdh_operations_type
from ifdpline ifdl, ifdphead ifdh
where ifdl.ifdl_pid = ifdh.ifdh_id
and ifdl.ifdl_dp_number like 'KMTC ULSAN/701S/KMTC/E%'
and ifdl.ifdl_org_id = '139'
and (ifdl.ifdl_cust_code = 'ESSA00100489' or ifdl.ifdl_vendor_code = 'ESSA00100489')
and exists(select 1
from FEXPENSE fexp, FCONSIGN fcsg
where fexp.FEXP_CONSIGN_ID = fcsg.FCSG_CONSIGN_ID
and ('F' || fexp.fexp_expense_id || 'C' = ifdl.ifdl_dp_line_number or 'F' || fexp.fexp_expense_id = ifdl.ifdl_dp_line_number))

这个语句的and ('F' || fexp.fexp_expense_id || 'C' = ifdl.ifdl_dp_line_number or 'F' || fexp.fexp_expense_id = ifdl.ifdl_dp_line_number)导致了索引失效，有什么写法可以满足该业务并提高效率，谢谢大家

[tandy_cs_201]

注：fexp.fexp_expense_id是NUMBER型，ifdl.ifdl_dp_line_number是VARCHAR2，是当初别人设计的，晕死，大家有什么办法，帮帮忙啊，很急

[bobfang]

建函数索引，如果版本是9i或更高。

[tandy_cs_201]

to bobfang(匆匆过客) ：||是没法用索引的，你能告诉我怎么建法吗

[bobfang]

给用户授予create indextype和query rewrite的权限；检查系统参数，query_rewrite_enabled要为true，query_rewrite_integrity要为enforced。
然后用户执行
create index INDEXNAME on fexpense('F'|| fexp_expense_id );
exec dbms_status.gather_table_stats('TABLEOWNERNAME','FEXPENSE')

[tandy_cs_201]

dbms_status.gather_table_stats('TABLEOWNERNAME','FEXPENSE')这句话是什么意思

[tandy_cs_201]

谢谢bobfang(匆匆过客)