时间戳

首页 > 解决方案 > 重写查询替换不在

sql - 重写查询替换不在

问题描述

我想以删除的方式重新编写以下查询，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 推荐。

在执行计划的第 5 行使用的谓词 "TT"."STATUS"<>'C' 包含索引列 "STATUS" 上的表达式。此表达式阻止优化器有效地使用表 TT 上的索引。

不同值的数量

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

上的索引TRX_T是这样的：(CODE,SUBID,TYPE,STATUS,NO_SL)

标签： sqloraclequery-optimization

解决方案

如果您想调整查询，您需要了解您的数据模型和数据。优化器说它不能有效地使用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 EXISTS 子查询）：

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 上调优查询是个小游戏。缺少太多信息（数据量、偏差、其他索引、解释计划等），我们只能做猜测之外的任何事情。

推荐阅读