Java functions and the Oracle optimizer

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 Received on Tue Aug 08 2006 - 17:05:58 CDT