Tough query

From: Sashafay <afayfman_at_mwh.com>
Date: 18 May 2004 07:50:22 -0700
Message-ID: <a13f8a22.0405180650.48e79eea@posting.google.com>

Need a little help with tuning of my query. The following select statement going to compare two tables for missing records. One of the table local and the other remote. There is any hints exist for remote databases?

SELECT
COUNT(A.CUSTID)
FROM
DBW_PROMOC A
WHERE NOT EXISTS
(SELECT 'X' FROM PROMOC_at_RPT B
WHERE A.CUSTID = B.CUSTID AND A.PROMO = B.PROMO AND A.EDATE = B.EDATE); NOTE: This query takes over an hour to run, even all three fields are Primary keys in both tables.

A little bit more info about my query: local database 9.0.1 and CBO, but remote is 7.3.4 and RBO. Both table have over 23 millions records. Execution plan force to do INDEX FAST FULL SCAN.

Thanks in advance,
Alex Received on Tue May 18 2004 - 09:50:22 CDT