Re: Tough query

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1084942134.2580_at_yasure>...
> 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.

All of those above have been done... includes statistics and making sure that index is using. I need any kind of fresh idea besides MINUS and UNION approach. May be PL/SQL can help with it???

Thanks,
Alex Received on Wed May 19 2004 - 09:31:46 CDT