Re: Tough query

afayfman_at_mwh.com (Sashafay) wrote in message news:<a13f8a22.0405190631.56c9c82f_at_posting.google.com>...
> 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

I found a way around it.

Thanks all for your input,
Alex Received on Wed May 19 2004 - 17:24:51 CDT