Re: Nested Loop Semi Join
- From: "Michael McMullen" <ganstadba@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Jun 2004 14:01:16 -0400
9.2.0.4
I get the same thing. Mine goes from nested loop merge join to a hash
join(semi) merge join. The hash join goes from 2 minutes to 5 hours and
fills up the temp tablespace. The process is the same over 6 tables, all
partitioned with bitmap indexes and ranging in size from 10Gb to 300Gb. I've
done a multitude of analyzing and rewrites and ended up using an alter
session because the app doing the batch wouldn't take hints. Just tried your
hint and it gave me great results also.
Mike
----- Original Message -----
From: "Subbiah, Stalin" <SSubbiah@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, June 30, 2004 1:36 PM
Subject: Nested Loop Semi Join
> Has anyone seen this behavior on sql's with IN subqueries go into nested
> loop semi join which is 10x times slower than 8174. However after hinting
> with /*+ ordered no_expand */ I get much better performance than 8i.
> Query in 8i with explain plan.
>
> SQL> select count(*) from hffact where page_hit_key in (select
page_hit_key
> from page_hit where account_key=21152);
>
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
