可能是你公司id没有几个，或者就一个。时间一天数据远小于一个公司的id数据量。选择了更加有的trunc的那个索引。

首先申明,没有正式优化经验,但还是想参与讨论:

看了下之后个人觉得有几个问题需要确认:

1. U表(BA_USER_INFO_T U)中的MEMBER_ID是否有不存在于M表(BA_MEMBER_INFO_T)之外的?

如果没有则不需要关联M表,如果有可以考虑在U表上建立(USER_ID,MEMBER_ID)的联合索引.

2. E表(AU_ORDER_EXT)与R表(AU_ORDER_ACTIVITIES_RELATION_T)中 ORDER_ID 字段是否唯一?

如果唯一则不需要关联.如果不唯一参考第三点.

3.如果上诉表确实都是需要关联的,那么考虑D表(AU_ORDER)只是取一天的数据,可以考虑用with语句将D表变成小表,并且考虑让D走索引.

这个索引可以考虑(COMPANY_ID ,ORDER_TYPE_DTCODE ,ORDER_DT ,ORDER_USER_ID )

其中如果ORDER_DT 不被选择,考虑是否使用trunc(ORDER_DT ),这个得考虑字段类型是否有比较特殊的地方.

with D as (select a.ORDER_ID ,a.ORDER_USER_ID from AU_ORDER a where a.COMPANY_ID =2

select count(1) from D,

BA_USER_INFO_T U ,

BA_MEMBER_INFO_T M, 

AU_ORDER_EXT E, 

AU_ORDER_ACTIVITIES_RELATION_T R

WHERE

 D.ORDER_USER_ID = U.USER_ID
AND U.MEMBER_ID = M.MEMBER_ID
AND D.ORDER_ID = E.ORDER_ID(+)
AND D.ORDER_ID = R.ORDER_ID(+);

这个company_id数据分布情况，AU_ORDER表的 ORDER_DT字段是date类型

试试：

SELECT count(1)
FROM AU_ORDER D,

BA_USER_INFO_T U ,

BA_MEMBER_INFO_T M,

AU_ORDER_EXT E,

AU_ORDER_ACTIVITIES_RELATION_T R

WHERE

D.ORDER_USER_ID = U.USER_ID
AND U.MEMBER_ID = M.MEMBER_ID
AND D.ORDER_ID = E.ORDER_ID(+)
AND D.ORDER_ID = R.ORDER_ID(+)
AND D.ORDER_TYPE_DTCODE = ‘redeem’
AND D.COMPANY_ID =2
AND D.ORDER_DT = TO_DATE(‘2021-12-23’, ‘YYYY-MM-DD’)

1、建议你简化一下。只保留对AU_ORDER表的查询及相关条件。然后看看是否仍然是走同样的，你认为错误的索引。2、然后对简化后的SQL做10053跟踪（记录了优化器制定执行计划的过程）。通过解读10053的跟踪事件，会有助于搞清数据库这样做的原因。

题主的问题是为何没有走IDX_AU_ORDER_N3组合索引。那还是着重研究这个问题。如果这个表存了10年的记录，那么23--24日两天的记录相当于10年记录的2/(10X365). 可以说这两天记录占总记录比例相当小。优化器也许有查询改写功能吧，它认为先定位到具体日期段更便利，所以在执行计划里，它于STEP 6直接将查询改写了，并因此调用ORDER_N2索引，再经过步骤5，ACCESS BY ROWID，加了FILTER条件company_id=xxx,order_type_dtcode=xxx,就可以取出AU_ORDER 里符合条件的记录了。也可以加个HINT，直接引用IDX_AU_ORDER_N3, 对比下看哪个执行计划效率高