Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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
![]() |
![]() |