subquery taking a VERY LONG TIME

From: Sanjiv Singh <ssingh1961_at_yahoo.com>
Date: 12 Jun 2002 14:36:21 -0700
Message-ID: <36ee465c.0206121336.669f609a@posting.google.com>

Please help!

I have a subquery that is taking too long (like 2 hours) to run with roughly 30,000 rows in each of the two query tables.

problem context:

---

There exists two tables APP_ID_TODAY and APP_ID_YESTERDAY; each table contains a unique key <app_id> and have the same type of data; except one table is from 'today' and the other table is from 'yesterday';

The problem is to determine which <app_id> (denoting new applications) are in TODAY's table and not in YESTERDAY's table. For a variety of business (read political) reasons ... we don't have access to the OLTP database to write a trigger to do inserts when new applications resulting in a new app_id is generated.

query:

---

INSERT INTO new_app_results

   SELECT T.app_id
   FROM app_today T
   WHERE T.app_id NOT IN (SELECT DISTINCT app_id from app_yesterday);

app_id is indexed on both TODAY and YESTERDAY table.

thanks for your help!

ssingh Received on Wed Jun 12 2002 - 16:36:21 CDT