The Java OTF solution is based on a Java Table Operator that inherits all the limitations imposed by both the Java engine and the current table operator infrastructure in the database. The following factors that can impact the Java OTF performance:
- Object Filter Join (OFJ) is not available for table operators, which impacts queries with complex join conditions, such as those found in TPC-DS benchmarks, resulting in reduced performance. There is no workaround for this issue now.
- Lack of IPE results feedback for derived tables / table operators. This issue is seen for sub-queries that yield zero results. It continues to evaluate the remaining expressions in an AND chain, even when one sub-query in the chain has already returned zero rows. Abench Query 4 and Query 21 are examples. There is no workaround for this issue now.
- For VantageCloud Lake, lack of IPE on Compute cluster can cause bad plans with high memory estimations. This can cause the OTF queries to be held in the delay queue and not sent to the Compute Cluster for execution.
The performance impact from these discussed factors will be resolved when the Native OTF solution becomes available.