Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: subquery taking a VERY LONG TIME

Re: subquery taking a VERY LONG TIME

From: Saikat Chakraborty <saikatchak_at_hotmail.com>
Date: Thu, 13 Jun 2002 05:08:50 +0000 (UTC)
Message-ID: <266fdafc234e4931815aff1bdb036166.16981@mygate.mailgate.org>


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
Received on Thu Jun 13 2002 - 00:08:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US