在oracle中比较两个游标而不是使用MINUS [英] comparing two cursors in oracle instead of using MINUS

问题描述

由于表包含大量数据，因此以下查询需要运行3分钟以上的时间:

The following query takes more than 3 minutes to run because tables contain massive amounts of data:

  SELECT  RTRIM(LTRIM(A.HEAD)),
      A.EFFECTIVE_DATE,
    FROM   TABLE_1 A
    WHERE  A.TYPE_OF_ACTION='6'
    AND    A.EFFECTIVE_DATE >= ADD_MONTHS(SYSDATE,-15)  

    MINUS

    SELECT  RTRIM(LTRIM(B.head)),
      B.EFFECTIVE_DATE,
    FROM  TABLE_2 B

在我们的系统中，如果查询运行了8秒钟以上，则会被杀死.有没有一种方法可以单独运行查询..将它们放入游标..compare，然后获得结果?这样，每个查询将单独运行，而不是一个庞大的查询，该查询需要3分钟.

In our system a query gets killed if it is running for more than 8 seconds. Is there a way to run the queries individually ..put them in cursors..compare and then get the results? that way each query will be ran individually rather than as one massive query which takes 3 minutes.

如何比较两个光标以模仿MINUS?

How would two cursors be compared to mimic the MINUS?

推荐答案

MINUS是一种设置操作，它除了将第二个查询的结果带到第一个查询之外，还将删除重复的内容(如果它们出现在第一个查询中)放. 因此，显示的查询将始终必须先从TABLE_1构建完整的结果集，然后再将其返回给用户.

A MINUS is a set operation which, as well as taking the results of the second query away from the first, will also remove duplicates if they appear in the first set. As such, the query shown will always have to build the full result set from TABLE_1 before returning it to the user.

如果您可以确定第一组中截止日期/生效日期没有重复项(或者您不希望删除此类重复项)，可以尝试

If you can be sure that there are no duplicates for the trimemd head/effective date in the first set (or you don't want such duplicates removed) you can try

SELECT  RTRIM(LTRIM(A.HEAD)), A.EFFECTIVE_DATE,
    FROM   TABLE_1 A
    WHERE  A.TYPE_OF_ACTION='6'
    AND    A.EFFECTIVE_DATE >= ADD_MONTHS(SYSDATE,-15)
    AND NOT EXISTS 
         (select 1 from table_2 b 
          where RTRIM(LTRIM(b.head)) = RTRIM(LTRIM(a.head))
          and b.effective_date = a.effective_date) )

这样，查询可以开始更快地返回结果，尤其是在table_2很小或可以通过有效日期或开头的索引访问行的情况下.

That way the query can start returning results much quicker, especially if table_2 is very small or the rows can be accessed though an index on effective_date or head.

PS.如果可以，请删除RTRIM(LTRIM())位.

PS. If you can, remove the RTRIM(LTRIM()) bits.

PPS.仍然无法保证它会在8秒内返回.这取决于table_1的大小，以及type_of_action和/或有效日期的索引.

PPS. There's still no guarantee it will return in under 8 seconds. That would depend on how large table_1 is, and indexes on type_of_action and/or effective_date.

已添加:

您可以通过

SELECT  RTRIM(LTRIM(A.HEAD)), A.EFFECTIVE_DATE,
    FROM   TABLE_1 A
    WHERE  A.TYPE_OF_ACTION='6'
    AND    A.EFFECTIVE_DATE >= ADD_MONTHS(SYSDATE,-15)

，如果返回则忽略行

    select 1 from table_2 b 
      where RTRIM(LTRIM(b.head)) = :1
      and b.effective_date = :1
      and rownum =1

但是完全执行肯定会花费更长的时间.也许更长的数量级(即几个小时)，这取决于每个table_2检查所花费的时间.不完全确定用于截止的标准(调用的持续时间或打开的SQL游标的持续时间)，因此它可能会关闭外部游标.而且，根据table_1的大小/索引/内容，外部游标可能仍无法返回时间范围内的第一行.

But it would certainly take longer to execute entirely. Maybe orders of magnitude longer (ie hours) depending how long each table_2 check takes. Not exactly sure what criteria is used for the cutoff (duration of call or duration of open SQL cursor) ,so it might close the outer cursor. And depending on the size/index/contents of table_1, the outer cursor may still not return the first rows within the timeframe.

表_1，表_2中有多少行以及可用的索引是什么?

How many rows in table_1, table_2 and what indexes are available ?

这篇关于在oracle中比较两个游标而不是使用MINUS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！