Oracle SQL索引优化: 即使在强制时也不使用索引提示

亲爱的先生，

我已经创建了一个具有复合主键和几个索引的表。在其中一个SQL中，where子句是索引列和非索引列的组合，但是查询不使用索引列，而是进行全表扫描。

因为，它没有使用索引列，我强制查询使用索引提示，但仍然不使用索引。我在现场会话中也得到相同的结果。我预计索引扫描应该有以下条件

sh.vin = 'MA3FJEB1S00986676'

即使它被或条件包围。

请启发。

实时SQL链接:https://livesql.oracle.com/apex/livesql/s/isz9vnmva9ugfw6qz4ua3yfkk

查询如下:

SELECT /* +index (sh IDX_VIN_POCINV) */ sh.inv_date, 
                    sh.sales_type,   
                    sh.invoice_amt, 
                    'TV' channel,
                    sh.fin_amt,
                    DECODE(sh.exchange_flag,'Y','Y','N') exh_flag, 
                    sh.exch_reg_num,
                    sh.tenure,
                    sh.fi_cd,
                    sh.parent_group,
                    sh.dealer_map_cd, loc_CD
               FROM am_temp sh
              WHERE 
              ((sh.vin = 'MA3FJEB1S00986676') OR
                     (sh.model_cd = 'DM' AND
                       substr(sh.chassis_num,-6) = substr('986676',-6) AND
                       sh.engine_num = '2887232') OR
                     (substr(sh.chassis_num,-6) = substr('986676',-6) AND
                       sh.reg_num = 'KL52K4856')
                     )
                AND sh.inv_status = 'I'  
                AND sh.inv_date BETWEEN '01-JAN-2010' AND '01-JAN-2019';

I'm expecting below condition should go for the index scan even though it's surrounded by OR condition.

通常，当您有或条件时，优化器很难使用索引。尤其是当索引列仅在条件的一侧时。

你为什么问？

因为数据库需要评估整个表达式!

当然，您可以使用索引来查看是否有vin = 'ma3fjeb1s00986676 '的条目。

但是对于每一行，这是假的，你仍然需要检查所有其他谓词。这些都不检查VIN。所以索引对于这些其他标准是无用的。

所以它进行全表扫描。

在某些情况下，优化器可以将许多索引组合在位图或但是您的查询中的其他列没有索引。排除此选项。

如果全扫描时间太长，您可能会从以下方面获得一些里程:

-在OR条件中的其他列上创建索引
-使用VIN与其他或

给出类似的东西:

create index ii 
  on am_temp ( substr(chassis_num,-6) , reg_num  );
create index iii 
  on am_temp ( substr(chassis_num,-6) , model_cd, engine_num  );
  
SELECT /* index (sh IDX_VIN_POCINV) */ sh.inv_date,  
    sh.sales_type,    
    sh.invoice_amt,  
    'TV' channel, 
    sh.fin_amt, 
    DECODE(sh.exchange_flag,'Y','Y','N') exh_flag,  
    sh.exch_reg_num, 
    sh.tenure, 
    sh.fi_cd, 
    sh.parent_group, 
    sh.dealer_map_cd, loc_CD 
FROM am_temp sh 
WHERE  sh.vin = 'MA3FJEB1S00986676'
AND sh.inv_status = 'I'   
AND sh.inv_date BETWEEN '01-JAN-2010' AND '01-JAN-2019'
union 
SELECT /* index (sh IDX_VIN_POCINV) */ sh.inv_date,  
    sh.sales_type,    
    sh.invoice_amt,  
    'TV' channel, 
    sh.fin_amt, 
    DECODE(sh.exchange_flag,'Y','Y','N') exh_flag,  
    sh.exch_reg_num, 
    sh.tenure, 
    sh.fi_cd, 
    sh.parent_group, 
    sh.dealer_map_cd, loc_CD 
FROM am_temp sh 
WHERE  
(
     (sh.model_cd = 'DM' AND 
       substr(sh.chassis_num,-6) = substr('986676',-6) AND 
       sh.engine_num = '2887232') OR 
     (substr(sh.chassis_num,-6) = substr('986676',-6) AND 
       sh.reg_num = 'KL52K4856') 
     ) 
AND sh.inv_status = 'I'   
AND sh.inv_date BETWEEN '01-JAN-2010' AND '01-JAN-2019';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

Plan hash value: 2119838223                                                 
                                                                            
-----------------------------------------------------------------           
| Id  | Operation                              | Name           |           
-----------------------------------------------------------------           
|   0 | SELECT STATEMENT                       |                |           
|   1 |  SORT UNIQUE                           |                |           
|   2 |   UNION-ALL                            |                |           
|   3 |    FILTER                              |                |           
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| AM_TEMP        |           
|   5 |      INDEX RANGE SCAN                  | IDX_VIN_POCINV |           
|   6 |    FILTER                              |                |           
|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED| AM_TEMP        |           
|   8 |      BITMAP CONVERSION TO ROWIDS       |                |           
|   9 |       BITMAP OR                        |                |           
|  10 |        BITMAP CONVERSION FROM ROWIDS   |                |           
|  11 |         INDEX RANGE SCAN               | II             |           
|  12 |        BITMAP CONVERSION FROM ROWIDS   |                |           
|  13 |         INDEX RANGE SCAN               | III            |           
-----------------------------------------------------------------