Re: subquery taking a VERY LONG TIME

Hi,
Please try this.
> 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);
>

INSERT INTO new_app_results

    SELECT T.app_id
    FROM app_today T
    WHERE T.app_id NOT IN (SELECT /*+ hash_aj(app_yesterday) */     DISTINCT app_id from app_yesterday);

As per my observation, a "not in" sub query is much slower than a "hash join" when large dataset is involved , as a "not in" uses a filter. This hint tries to transform the filter to hash join. You should see performance improvement.
May be somebody will explain me why a filter is slower. Thanks,
Saikat Chakraborty
http://saikatchak.tripod.com

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG