sql - 重写查询替换 not in

Question

我想以删除 NOT IN 的方式重写以下查询，这可能吗？

select * from TRX_T TT, TRX_SUB TS
where TT.CODE=TS.CODE
and TT.SUBID= TS.ID
and TS.VALUE=1
and TS.CODE=1
AND TS.ID=17
AND TT.STATUS NOT IN('T','R','C')

如果状态为IN，我将使用union all

之所以要重写是因为下面的oracle推荐。

The predicate "TT"."STATUS"<>'C' used at line ID 5 of the execution plan contains an expression on indexed column "STATUS". This expression prevents the optimizer from efficiently using indices on table TT.

不同值的数量

    T       264
    C   5489709
    D      2987
    J       924
    L    529430
    R     39382
    S      5449

TRX_T 上的索引如下:(CODE,SUBID,TYPE,STATUS,NO_SL)

Answer 1

如果您想调整查询，您需要了解您的数据模型和数据。优化器表示它无法有效地使用 TRX_T 上的索引。让我们看看该复合索引:

• CODE:用于连接条件
• SUBID:用于加入条件
• TYPE:未使用
• STATUS:可以用作过滤器吗？
• NO_SL:未使用

您的查询使用五个索引列中的三个。但由于 STATUS 上有 NOT IN 表达式，优化器不会使用索引来评估过滤器。因此，它读取 TRX_T 中与 TRX_SUB 中的记录匹配的每条记录，并评估表上的过滤器。

也许如果您将条件明确地表达为 TT.STATUS IN ('D','J','L', 'S') 那么优化器可能能够使用 SKIP SCAN评估索引上的过滤器。

但是，如果将 TRX_T.TYPE 用作过滤器(或者重新排列索引列的顺序以具有 STATUS > 在 TYPE 之前，但不要这样做，因为它可能会破坏其他查询的稳定性)。

另一种选择是将表达式重写为 NOT IN 子查询(如果 (TRX_T.CODE, TRX_T.SUBID) 中没有空值，否则重写为 NOT IN 子查询)不存在子查询):

select * from TRX_T TT, TRX_SUB TS
where TT.CODE=TS.CODE
and TT.SUBID= TS.ID
and TS.VALUE=1
and TS.CODE=1
AND TS.ID=17
AND (TT.CODE, TT.SUBID) NOT IN
     (select x.CODE, x.SUBID
      from trx_t x
      where x.status in ('T','R','C')
     )

但是，该列表中具有 STATUS 值的 TRX_T 记录数量非常大 - 它们占表的大部分 - 因此评估该子查询很可能比您目前拥有的更昂贵。

请注意，通常的警告适用。在 StackOverflow 上调整查询是一件很困难的事情。缺少太多信息(数据量、偏差、其他索引、解释计划等)，让我们除了猜测之外无法做任何事情。