Re: Nested Loop Semi Join

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Wed, 30 Jun 2004 14:01:16 -0400
Message-ID: <BAY9-DAV22B5oL4p2o900028cbe@hotmail.com>

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_at_netopia.com> To: <oracle-l_at_freelists.org> 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jun 30 2004 - 13:02:29 CDT