Exclude w/o using Cartesian Product

From: Paul <paulvon_at_my-deja.com>
Date: Thu, 12 Aug 1999 19:14:03 GMT
Message-ID: <7ov6dj$imr$1@nnrp1.deja.com>

I am having a problem with the Cartesian Product this is causing my query to take too long.

The 1st and 2nd queries when run independent of each other, take about 3 minutes. When I try and exclude the result of the 2nd query from being reported in the results of the 1st query, the time to run increases into the hours to get results.

I thought that by using a sub-query in the FROM portion of the SQL this would help but it has not. I am trying to avoid having to put data into a temp table, or use PS/SQL, and reduce the execution time to six minutes.

TERM DEFINATIONS

1st query - SELECT A1 , A2 FROM ...
2nd query - SELECT C1  FROM ...
results   - SELECT a.A1, a.A2

SAMPLE SQL
SELECT a.A1, a.A2
FROM (SELECT A1 , A2 FROM ... ) A
WHERE A.1 NOT IN (SELECT C1 FROM ... ) ORDER BY a.A1

Thank you

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 12 1999 - 14:14:03 CDT