Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsxfer.itd.umich.edu!newsfeed.yul.equant.net!news-lond.gip.net!news.gsl.net!gip.net!colt.net!news.tele.dk!feed118.news.tele.dk!postnews.google.com!n13g2000cwa.googlegroups.com!not-for-mail From: "Dan" Newsgroups: comp.databases.oracle.misc Subject: Java functions and the Oracle optimizer Date: 8 Aug 2006 15:05:58 -0700 Organization: http://groups.google.com Lines: 16 Message-ID: <1155074758.275912.210700@n13g2000cwa.googlegroups.com> NNTP-Posting-Host: 141.154.211.146 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1155074763 22527 127.0.0.1 (8 Aug 2006 22:06:03 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 8 Aug 2006 22:06:03 +0000 (UTC) User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: n13g2000cwa.googlegroups.com; posting-host=141.154.211.146; posting-account=XO6JpAwAAAAKVCw5S9iwMaPP6CcOgiN6 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:129149 We seem to be having an issue where SQL statements that call a function written in java behave very differently than the same sql without the java function. For example, with java in the SQL a hash join is used and a large (23G) table is hashed into memory instead of the tiny 64M table it is joining to. Even when we put a hint in the code that says USE_NL (use nested loop), the sql ignores it and still does a hash join. Is this common practice, does the optimizer get confused when non-native functions like java are called? Any help or advice as to how to get the nested loop to work or how to get the optimizer to behave would be greatly appreciated. Dan