这个SQL语句,加了强制索引跟没加的效果一样,是不是我哪写错了,
SELECT distinct
/*+ index(a cust_carrier_cd) */ a.cust_carrier_cd,
/*+ index(a biz_pkg) */ a.biz_pkg,
c.virtual_flag,
d.rpt_file_nm_pattern,
a.rep_err_eff_day
FROM dcsdba.rpt_og_proc_state a,
PUBDBA.PARTNER_CARRIERS b,
dcsdba.rap_og_proc_state c,
rptdba.rpt_file_type d
where a.biz_line = 1
AND ((a.rep_send_tm IS NULL) OR
((sysdate - a.rep_send_tm) * 24 >= a.rep_send_cycle))
and b.CUST_CARRIER_CD = a.cust_carrier_cd
and b.PARTNER_CARRIER_CD = a.partner_carrier_cd
and c.cust_carrier_cd = a.cust_carrier_cd
and c.partner_carrier_cd = a.partner_carrier_cd
and c.biz_pkg = a.biz_pkg
and b.biz_pkg = a.biz_pkg
and d.rpt_file_type = a.biz_pkg || '06'
and b.expired_tm > sysdate
SELECT distinct
/*+ index(a cust_carrier_cd) */ a.cust_carrier_cd,
/*+ index(a biz_pkg) */ a.biz_pkg,
c.virtual_flag,
d.rpt_file_nm_pattern,
a.rep_err_eff_day
FROM dcsdba.rpt_og_proc_state a,
PUBDBA.PARTNER_CARRIERS b,
dcsdba.rap_og_proc_state c,
rptdba.rpt_file_type d
where a.biz_line = 1
AND ((a.rep_send_tm IS NULL) OR
((sysdate - a.rep_send_tm) * 24 >= a.rep_send_cycle))
and b.CUST_CARRIER_CD = a.cust_carrier_cd
and b.PARTNER_CARRIER_CD = a.partner_carrier_cd
and c.cust_carrier_cd = a.cust_carrier_cd
and c.partner_carrier_cd = a.partner_carrier_cd
and c.biz_pkg = a.biz_pkg
and b.biz_pkg = a.biz_pkg
and d.rpt_file_type = a.biz_pkg || '06'
and b.expired_tm > sysdate
2,一次查询这种方式只能对一个表使用一次;
3,如果想同时用多个索引,可以尝试使用index_combine
distinct
a.cust_carrier_cd,
a.biz_pkg,
c.virtual_flag,
d.rpt_file_nm_pattern,
a.rep_err_eff_day
FROM dcsdba.rpt_og_proc_state a,
PUBDBA.PARTNER_CARRIERS b,
dcsdba.rap_og_proc_state c,
rptdba.rpt_file_type d
where a.biz_line = 1
AND ((a.rep_send_tm IS NULL) OR
((sysdate - a.rep_send_tm) * 24 >= a.rep_send_cycle))
and b.CUST_CARRIER_CD = a.cust_carrier_cd
and b.PARTNER_CARRIER_CD = a.partner_carrier_cd
and c.cust_carrier_cd = a.cust_carrier_cd
and c.partner_carrier_cd = a.partner_carrier_cd
and c.biz_pkg = a.biz_pkg
and b.biz_pkg = a.biz_pkg
and d.rpt_file_type = a.biz_pkg || '06'
and b.expired_tm > sysdate