我是否可以知道为什么索引无法加速某些查询的查询处理?

我试图解释并显示此查询的执行计划:

explain plan for
SELECT *
FROM LINEITEM
WHERE l_quantity = 6
OR l_shipMode = 'MAIL';

SELECT * FROM table(dbms_xplan.display);

输出为:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 98068815

------------------------------------------------------------------------------
| Id  | Operation     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   287K|    34M|  8802   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| LINEITEM |   287K|    34M|  8802   (1)| 00:00:01 |
------------------------------------------------------------------------------

然后我在l_quantity和l_shipMode上创建了一个索引:

CREATE INDEX lineItemIdx ON LINEITEM(l_quantity, l_shipMode);

然后,我解释并再次显示执行计划:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 98068815

------------------------------------------------------------------------------
| Id  | Operation     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   287K|    34M|  8802   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| LINEITEM |   287K|    34M|  8802   (1)| 00:00:01 |
------------------------------------------------------------------------------

没有区别。查询现在不应该使用索引吗?

评论
  • 泪眸人
    泪眸人 回复

    Alas, most databases do a very poor job optimizing or expressions (Oracle is an exception).

    You can get what you want using union all:

    SELECT li.*
    FROM LINEITEM li
    WHERE l_quantity = 6
    UNION ALL
    SELECT li.*
    FROM LINEITEM li
    WHERE l_shipMode = 'MAIL' AND l_quantity <> 6;
    

    For this, you need two indexes: LINEITEM(l_quantity) and LINEITEM(l_shipMode, l_quantity).

  • uvero
    uvero 回复

    在您的查询中,您有两个基于OR子句的条件。

     WHERE l_quantity = 6
     OR l_shipMode = 'MAIL';
    

    这意味着对于过滤条件的评估,索引

     INDEX lineItemIdx ON LINEITEM(l_quantity, l_shipMode);
    

    不能对OR子句的两个部分中的每一个进行rpoperly操作。

    因此查询需要全面扫描,而索引中没有用

    在这种情况下,索引仅对条件有效

    WHERE l_quantity = 6
    

    要么

    WHERE l_quantity = 6
     AND l_shipMode = 'MAIL';
    

    或使用两个索引并使用基于UNION的两个独立查询重建查询