comparing two cursors in oracle instead of using MINUS

Question

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

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.

How would two cursors be compared to mimic the MINUS?

Answer 1

MINUS is the same as saying "get all the rows of the first query, then from that set remove the rows that are also in the second query", so you could like load the results from the first query into an array in-memory, then loop through the second query results and check them one-by-one against the first query results and remove them if they exist.

I'm not sure that will actually perform better though (depends on a lot of things). You might also want to consider using NOT EXISTS instead and check that performance, i.e.

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 fFROM TABLE_2 B
  WHERE RTRIM(LTRIM(A.HEAD)) = RTRIM(LTRIM(B.HEAD))
  AND A.EFFECTIVE_DATE = B.EFFECTIVE_DATE
)

Some functional indexing may also be needed on RTRIM(LTRIM(A.HEAD))

Answer 2

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) )

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. If you can, remove the RTRIM(LTRIM()) bits.

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.

Added:

You could cursor through

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 ignore rows if it returned

    select 1 from table_2 b 
      where RTRIM(LTRIM(b.head)) = :1
      and b.effective_date = :1
      and rownum =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.

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