Question

oracle 查询指定索引

select /*+ index(PTF_ORDER_DATE B2C_PAY_TRANFLOW_IDX4)*/ * from B2C_PAY_TRANFLOW
WHERE PTF_MER_CODE = '123789'
AND PTF_ORDER_NUM = '20130116165831979'
我在网上看到是这么写的,可是我也这么写却不走指定的索引,是为什么???

Answer 1

select /*+ index(B2C_PAY_TRANFLOW B2C_PAY_TRANFLOW_IDX4)*/ * from B2C_PAY_TRANFLOW
WHERE PTF_MER_CODE = '123789'
AND PTF_ORDER_NUM = '20130116165831979'
试试看，你前面表名写的不对吧，
可以给表指定别名的，如下：
select /*+ index(t,B2C_PAY_TRANFLOW_IDX4)*/ * from B2C_PAY_TRANFLOW t
WHERE PTF_MER_CODE = '123789'
AND PTF_ORDER_NUM = '20130116165831979'

Answer 2

/*+ index(PTF_ORDER_DATE B2C_PAY_TRANFLOW_IDX4)*/ 部分注释掉了。
相当于下面的语句：
select * from B2C_PAY_TRANFLOW WHERE PTF_MER_CODE = '123789' AND
PTF_ORDER_NUM = '20130116165831979';
这只是简单的条件限定查询。你的数据库中也有B2C_PAY_TRANFLOW表才好用。