Oracle 19c执行计划中如何让<>、IS NOT NULL走索引范围扫描

问题背景 #

• 业务场景为检索类查询，数据库使用Oracle 19c，初始查询语句如下：

select * from t1 
inner join t2 on t2.id = t1.id and t2.name <> 'AKA'
left outer join t3 Buyer on Buyer.id = t1.id and Buyer.userId is null
left outer join t3 Seller on Seller.id = t1.id and Seller.userId = t1.userId
where rownum < 500;

• 未创建索引时查询运行效率极低，返回结果耗时约14秒；对照explain plan为相关表创建匹配索引后，查询耗时降至3秒，仍未达到1秒内响应的预期目标。
• 重新核查autotrace结果发现，即使已创建对应索引，仍有两处操作走全扫描，未使用预期的索引范围扫描。

异常点1：t2表name <> 'AKA'过滤条件未命中索引范围扫描 #

对应执行计划片段如下：

INDEX       t2.name
                                                                
     - Filter Predicates 
                                                                                
           - t2.NAME<>'AKA' 

已尝试多种索引创建方案均未生效：

• 创建普通B树索引：create index t2_name_idx on t2 (name);
• 创建函数索引：create index t2_name_idx on t2 (case when name <> 'AKA' then name end);
  以上方案均无法让<>条件命中索引范围扫描。

异常点2：t3表Seller别名左外关联逻辑走索引全扫描 #

对应执行计划片段如下：

HASH JOIN RIGHT OUTER    890639      556473      400400      2906406 
 Access Predicates 
 AND 
 T3.ID=T1.ID 
 T3.USERID=T1.USERID                                            
 TABLE ACCESS 
 ADDRESSES   BY INDEX ROWID BATCHED      1225069     314696      384117      596570 
                                                        
 INDEX 
 T3_UERID    FULL SCAN   1225069     4708    4678    121216 
                                                                
 Filter Predicates
                                                                        
 T3.USERID IS NOT NULL 

具体表现：

• 执行left outer join t3 Seller on Seller.id = t1.id and Seller.userId = t1.userId逻辑时，优化器会自动添加t3.userid is not null过滤条件
• 现有索引为t3表userid字段上的普通非唯一索引T3_USERID，当前执行时走索引全扫描，未使用索引范围扫描。

现征集该场景下的优化建议与实现思路。

内容的提问来源于stack exchange，提问作者Shibo Ding