KODO 4.2 EAGER FECHING NOT QUERYING EFFICIENTLY WITH ONE-TO-ONE-TO-MANY RELATION

(Doc ID 1122843.1)

Last updated on SEPTEMBER 21, 2017

Applies to:

Oracle Kodo - Version: 4.1 and later   [Release: and later ]
Information in this document applies to any platform.

Symptoms

Kodo 4.2 JDO. 
If you have a one-to-one-to-many relationship A(1) --> B(1) --> C(*), for example,  (1)Parent -> (1) Child -->  (*) Friend, when eager fetching this object graph the SQL generated is inefficient.  In the test case eager fetching was defined programmatically. For example:

JDBCFetchPlan fetchPlan = (JDBCFetchPlan)query.getFetchPlan();
fetchPlan.setEagerFetchMode(JDBCFetchPlan.EAGER_PARALLEL);
fetchPlan.addField(Parent.class,"id");
fetchPlan.addField(Parent.class,"child");
fetchPlan.addField(Child.class,"id");
fetchPlan.addField(Child.class,"friendList");
fetchPlan.addField(Friend.class,"id");

If we run an eager query on this object graph by requesting the 'Parent', Kodo generates a SQL statement to get all of the Parents, a statement to get all of the 'Child' objects, and then one statement for each 'Child' object to get the Friends.  For example, for each Child object, there is a statement like:

SELECT t0.ORDR, t0.ID, t0.VERSION, t0.CHILD FROM FRIEND t0 WHERE t0.CHILD =
? ORDER BY t0.ORDR ASC [params=(long) 51]
SELECT t0.ORDR, t0.ID, t0.VERSION, t0.CHILD FROM FRIEND t0 WHERE t0.CHILD =
? ORDER BY t0.ORDR ASC [params=(long) 201]
....

With a workaround (see below), it is possible to get all of the Friend  objects with a single statement:

SELECT t1.ID, t2.ORDR, t2.ID, t2.VERSION, t2.CHILD FROM PARENT t0, CHILD t1,  FRIEND t2 WHERE t0.ID = t1.PARENT AND t1.ID = t2.CHILD ORDER BY t1.ID ASC,  t2.ORDR ASC

Changes

None.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms