Re: subquery taking a VERY LONG TIME

ssingh1961_at_yahoo.com (Sanjiv Singh) wrote in message news:<36ee465c.0206121336.669f609a_at_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);

NOT IN will make Oracle scan the entire index on app_yesterday for each record in app_today. Set operator works best here,   insert into new_app_results select app_id from app_today minus   select app_id from app_yesterday;
You can also use NOT EXISTS instead of NOT IN in your where clause. It will be a lot faster too.

>
> app_id is indexed on both TODAY and YESTERDAY table.
>
> thanks for your help!
>
> ssingh
Received on Wed Jun 12 2002 - 21:05:04 CDT