Re: Tough query

Sashafay <afayfman_at_mwh.com> wrote:
>
>Need a little help with tuning of my query. The following select
>statement going to compare two tables for missing records.

In my experience, NOT IN and large joins over a db link tend not to perform well. You'll probably be better off using a MINUS operation. It'll use more memory (sort area) and place a heavy load on the network and cpu, but the elapsed time of the query should be considerably less. The query plan should be to suck over index B and do a local merge with index A.

>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);
SELECT COUNT(*)
FROM
 (
  SELECT A.CUSTID, A.PROMO, A.EDATE
  FROM DBW_PROMOC A
  MINUS
  SELECT B.CUSTID, B.PROMO, B.EDATE
  FROM PROMOC_at_RPT B
 );

This will give you a count of rows in DBW_PROMOC but not in PROMOC_at_RPT. To get the reverse, rows in PROMO but not in DBW_PROMPC, you'll need to reverse the tables.

Not sure of the details on Oracle 9, but I used to pre-allocate a large sort area for queries like this. If you have the memory, try setting aside 500MB or so for this baby:

alter session set sort_area_size = 500000000;

Of course, if you're just looking to mirror tables there are better ways. Never had good luck with snapshots on 20 million rows (early Oracle 8i), but there are other options like SQL*Plus COPY and transportable tablespaces.

Good luck. Let us know how it works out. Received on Tue May 18 2004 - 12:18:56 CDT