Re: Tough query

Sashafay wrote:
> 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

In addition to the advise you have already received ... a substantial improvement might be found by:

1. Making sure statistics are current on your 9i database.
2. Running explain plan to make sure the indexes are actually being used. Just because they are there is no guarantee
3. Upgrading one or both systems to 9.2.0.4 or above.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)