[eclipselink-users] Inefficient Query Criteria Delete for Super Class

[Rama Rao <ramaraochavali@xxxxxxxxx>]

Consider the following Model

IPSubnet is a sub class of NetworkAddressBlock

We are trying to Bulk Delete IPSubnet with the following code

    cb = (CriteriaBuilderImpl) em.getCriteriaBuilder();

    CriteriaDelete<IPSubnetDAO> subnetDelete =            cb.createCriteriaDelete(IPSubnetDAO.class);

                    Root subnetRoot = subnetDelete.from(IPSubnetDAO.class);

                    subnetDelete.where(cb.greaterThanOrEqualTo(subnetRoot.get(IPSubnetDAO._numericLowerAddress.getFieldName()),

                                                               net.getNumericLowerAddress()),

                                       cb.lessThanOrEqualTo(subnetRoot.get(IPSubnetDAO._numericUpperAddress.getFieldName()),

                                                            net.getNumericLowerAddress()),

                                       cb.or(cb.equal(subnetRoot.get(IPSubnetDAO._rootParentBlock.getFieldName()).get(IPSubnetDAO._ipAddressDomain.getFieldName()),

                                                      net.getIpAddressDomain()),

                                             cb.equal(subnetRoot.get(IPSubnetDAO._ipAddressDomain.getFieldName()),

                                                      net.getIpAddressDomain()))); 

The following queries were generated

DELETE FROM IPSubnet WHERE EXISTS(SELECT t0.ENTITYID FROM IPSubnet t3, NetworkAddressBlock t2, IPSubnet t1, NetworkAddressBlock t0 WHERE (((((t1.NUMERICLOWERADDRESS >= ?) AND (t1.NUMERICUPPERADDRESS <= ?)) AND ((t3.IPADDRESSDOMAIN = ?) OR (t1.IPADDRESSDOMAIN = ?))) AND ((t1.ENTITYID = t0.ENTITYID) AND (t0.ENTITYCLASS = ?))) AND ((t2.ENTITYID = t1.ROOTPARENTBLOCK) AND ((t3.ENTITYID = t2.ENTITYID) AND (t2.ENTITYCLASS = ?)))) AND t1.ENTITYID = IPSubnet.ENTITYID)

                bind => [369098752, 385875967, 150000, 150000, IPSubnetDAO, IPSubnetDAO]

 

DELETE FROM NetworkAddressBlock WHERE (ENTITYCLASS = ?) AND NOT EXISTS(SELECT t0.ENTITYID FROM NetworkAddressBlock t0, IPSubnet t1 WHERE (t1.ENTITYID = t0.ENTITYID) AND t0.ENTITYID = NetworkAddressBlock.ENTITYID)

                bind => [IPSubnetDAO]

The second query is very inefficient and has bad explain plan (involves Full Table Scan). My question is why is NOT Eclipselink applying the same criteria that it applied to child table to the parent table as well? Why is it trying to generate a different query that looks at all NetworkAddressBlock entries that does NOT exist in child table?

We have huge number of records in this table and is taking lot of time for the second query.

Is there a way to customize this behavior and generate the same query for parent table as well?

Thanks,

Rama