大于运算符的优化 [英] Optimization of greater than operator

问题描述

credit 表和 validtransaction 表有 2008 年以后的百万条记录数据.

credit table and validtransaction table have million record data from year 2008 onwards.

我们正在进行迁移.所以我需要找出2017年(periodseq=1055)之后不再使用的credittypeids，这样就不需要迁移了.

We are doing a migration. So I need to find out the credittypeids which are no longer in use after 2017 (periodseq=1055), so that these need not be migrated.

这是查询，>= 部分导致了巨大的成本.请提出替代方案.

This is the query and the >= part is resulting in huge cost. Please suggest an alternative.

SELECT CREDITTYPEID
FROM CREDITTYPE ct
WHERE NOT EXISTS
  (SELECT 1
  FROM CREDIT C
  WHERE C.PERIODSEQ>=1055
  AND C.CREDITTYPEID=CT.CREDITTYPEID
  ); 

推荐答案

这应该返回您的(不同的！)CREDITTYPEID 列表，这些列表过去使用过，但当前未使用(在 PERIODSEQ 之后)第1055章)

This should return your (distinct!) list of CREDITTYPEID that were used in the past, but are not used curretnly (after PERIODSEQ 1055)

SELECT CREDITTYPEID  /* used before 1055 */
FROM CREDITTYPE ct
WHERE PERIODSEQ < 1055
MINUS
SELECT CREDITTYPEID /* used after 1055 */
FROM CREDITTYPE ct
WHERE PERIODSEQ>=1055;

正如列名所暗示的 CREDITTYPEID 是一个 type 所以表中有几行具有相同的 typeId.

As the column name suggest CREDITTYPEID is a type so there are several rows in the table with the same typeId.

上面的查询只返回distinct list，没有使用hash anti join.

The query above return only the distinct list and uses no hash anti join.

您可以添加并行选项(使用PARALLEL 提示)，如果您的硬件允许.

You may add parallel option (with the PARALLEL hint) if your HW allows it.

这篇关于大于运算符的优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！