在查询具有继承的字段时，我可以阻止 Hibernate 生成联合查询吗？

Question

我有一个 JPA 层次结构，其中一个对象包含一组抽象类型，由不同数量的子类实现。问题是，当 Hibernate 尝试加载这些类时，它会生成一个正常获取主对象的查询，但（惰性地）使用联合（在子选择中）加载这些附加对象。大致意思是：

select ...
from (
    select ...
    from subtable1
    union
    select ...
    from subtable2
)
where parent.id = ?

这是一个极其效率低下的查询（父表中有 10k 行，所有行的每个子类有 1 个，获取父类中 100 条左右记录的整个层次结构）需要几分钟，急切查询最多需要 50 分钟）。

我不明白的是为什么它们不只是来自父表的左连接（select ... fromparent p left join subtable1 ... left join subtable2 ...）在几分之一秒内获取所有必需的数据。有什么方法可以避免这种愚蠢的联合查询吗？

到目前为止，我见过的唯一可行的解​​决方案是将继承类型从 TABLE_PER_CLASS 更改为 SINGLE_TABLE，但这样做的权衡并不理想（包括无法在列上使用“not null”、自扩展以来的维护问题）类将属于不同的团队，甚至可能使用不同版本的软件等）。

为了以防万一它对任何人有帮助，我将粘贴由 Hibernate 生成的具有此关系的真实查询（JPQL 作为开头的注释），这个特定查询自行执行需要 34 秒（重写为 0.6 秒）左连接）：

/* select distinct p
   from PersonJpa p left join fetch p.addressOfRecords a
   left join fetch a.extensions e left join fetch a.commsType c
   left join fetch p.credentials cr left join fetch cr.type t
   left join fetch p.serviceProfiles s left join fetch s.extensions ex
   where p.id in (?1) */
SELECT DISTINCT personjpa0_.person_id AS person1_902_0_,
                addressofr1_.aor_id AS aor1_899_1_,
                extensions2_.id AS id903_2_,
                commstypej3_.comms_type AS comms1_908_3_,
                credential4_.credentials_id AS credenti1_905_4_,
                credential5_.credentials_type AS credenti1_904_5_,
                servicepro6_.service_profile_id AS service1_901_6_,
                extensions7_.id AS id907_7_,
                personjpa0_.display_name AS display2_902_0_,
                personjpa0_.first_name AS first3_902_0_,
                personjpa0_.initials AS initials902_0_,
                personjpa0_.last_name AS last5_902_0_,
                personjpa0_.title AS title902_0_,
                personjpa0_.user_name AS user7_902_0_,
                addressofr1_.comms_type AS comms9_899_1_,
                addressofr1_.display_form AS display2_899_1_,
                addressofr1_.domain AS domain899_1_,
                addressofr1_.label AS label899_1_,
                addressofr1_.parameters AS parameters899_1_,
                addressofr1_.person_id AS person10_899_1_,
                addressofr1_.preference AS preference899_1_,
                addressofr1_.send_html AS send7_899_1_,
                addressofr1_.service_profile_id AS service11_899_1_,
                addressofr1_.user_part AS user8_899_1_,
                addressofr1_.person_id AS person10_0__,
                addressofr1_.aor_id AS aor1_0__,
                extensions2_.aor_id AS aor2_903_2_,
                extensions2_.resolution_status AS resolution1_910_2_,
                extensions2_.certificate_serial_number AS certific1_914_2_,
                extensions2_.device_type_name AS device2_914_2_,
                extensions2_.provisioned AS provisio3_914_2_,
                extensions2_.provisioning_token AS provisio4_914_2_,
                extensions2_.provisioning_unique_id AS provisio5_914_2_,
                extensions2_.sip_password AS sip6_914_2_,
                extensions2_.username AS username914_2_,
                extensions2_.voicemail_long_timeout AS voicemail8_914_2_,
                extensions2_.voicemail_short_timeout AS voicemail9_914_2_,
                extensions2_.clazz_ AS clazz_2_,
                extensions2_.aor_id AS aor2_1__,
                extensions2_.id AS id1__,
                credential4_.algorithm_name AS algorithm3_905_4_,
                credential4_.password AS password905_4_,
                credential4_.person_id AS person4_905_4_,
                credential4_.realm_id AS realm5_905_4_,
                credential4_.credentials_type AS credenti6_905_4_,
                credential4_.person_id AS person4_2__,
                credential4_.credentials_id AS credenti1_2__,
                servicepro6_.name AS name901_6_,
                servicepro6_.person_id AS person3_901_6_,
                servicepro6_.person_id AS person3_3__,
                servicepro6_.service_profile_id AS service1_3__,
                extensions7_.service_profile_id AS service2_907_7_,
                extensions7_.seqprofile_id AS seqprofile1_911_7_,
                extensions7_.certificate_port AS certific1_915_7_,
                extensions7_.email_aor_id AS email23_915_7_,
                extensions7_.emergency_numbers AS emergency2_915_7_,
                extensions7_.from_domain AS from3_915_7_,
                extensions7_.outbound_domain AS outbound4_915_7_,
                extensions7_.pbx_aor_id AS pbx24_915_7_,
                extensions7_.provisioning_delivery AS provisio5_915_7_,
                extensions7_.provisioning_host AS provisio6_915_7_,
                extensions7_.provisioning_port AS provisio7_915_7_,
                extensions7_.server_access_call_control_port AS server8_915_7_,
                extensions7_.server_access_host AS server9_915_7_,
                extensions7_.server_access_number AS server10_915_7_,
                extensions7_.server_access_secure_mode AS server11_915_7_,
                extensions7_.sip_domain AS sip12_915_7_,
                extensions7_.sip_enabled AS sip13_915_7_,
                extensions7_.sip_expires AS sip14_915_7_,
                extensions7_.sip_proxy AS sip15_915_7_,
                extensions7_.sip_realm AS sip16_915_7_,
                extensions7_.sip_transport AS sip17_915_7_,
                extensions7_.voicemail_long_timeout AS voicemail18_915_7_,
                extensions7_.voicemail_message_server AS voicemail19_915_7_,
                extensions7_.voicemail_number AS voicemail20_915_7_,
                extensions7_.voicemail_short_timeout AS voicemail21_915_7_,
                extensions7_.voicemail_userid AS voicemail22_915_7_,
                extensions7_.clazz_ AS clazz_7_,
                extensions7_.service_profile_id AS service2_4__,
                extensions7_.id AS id4__
FROM   cdm.cdm_person AS personjpa0_
       LEFT OUTER JOIN
       cdm.cdm_address_of_record AS addressofr1_
       ON personjpa0_.person_id = addressofr1_.person_id
       LEFT OUTER JOIN
       (SELECT NULL AS device_type_name,
               id,
               NULL AS provisioning_unique_id,
               aor_id,
               NULL AS voicemail_long_timeout,
               NULL AS username,
               NULL AS certificate_serial_number,
               NULL AS provisioning_token,
               resolution_status,
               NULL AS voicemail_short_timeout,
               NULL AS provisioned,
               NULL AS sip_password,
               1 AS clazz_
        FROM   cdm.sb_aor_details
        UNION
        SELECT device_type_name,
               id,
               provisioning_unique_id,
               aor_id,
               voicemail_long_timeout,
               username,
               certificate_serial_number,
               provisioning_token,
               NULL AS resolution_status,
               voicemail_short_timeout,
               provisioned,
               sip_password,
               2 AS clazz_
        FROM   cdm.fmc_remote_number_address_of_record_extension) AS extensions2_
       ON addressofr1_.aor_id = extensions2_.aor_id
       LEFT OUTER JOIN
       cdm.cdm_comms_type AS commstypej3_
       ON addressofr1_.comms_type = commstypej3_.comms_type
       LEFT OUTER JOIN
       cdm.cdm_credentials AS credential4_
       ON personjpa0_.person_id = credential4_.person_id
       LEFT OUTER JOIN
       cdm.cdm_credentials_type AS credential5_
       ON credential4_.credentials_type = credential5_.credentials_type
       LEFT OUTER JOIN
       cdm.cdm_service_profile AS servicepro6_
       ON personjpa0_.person_id = servicepro6_.person_id
       LEFT OUTER JOIN
       (SELECT NULL AS server_access_host,
               NULL AS sip_domain,
               NULL AS voicemail_message_server,
               NULL AS outbound_domain,
               NULL AS emergency_numbers,
               NULL AS provisioning_delivery,
               id,
               NULL AS sip_expires,
               NULL AS pbx_aor_id,
               NULL AS server_access_call_control_port,
               NULL AS provisioning_port,
               NULL AS email_aor_id,
               NULL AS voicemail_number,
               NULL AS voicemail_long_timeout,
               NULL AS certificate_port,
               NULL AS sip_enabled,
               NULL AS sip_transport,
               NULL AS server_access_number,
               service_profile_id,
               NULL AS server_access_secure_mode,
               NULL AS voicemail_userid,
               seqprofile_id,
               NULL AS sip_realm,
               NULL AS voicemail_short_timeout,
               NULL AS provisioning_host,
               NULL AS from_domain,
               NULL AS sip_proxy,
               1 AS clazz_
        FROM   cdm.sb_service_profile_details
        UNION
        SELECT server_access_host,
               sip_domain,
               voicemail_message_server,
               outbound_domain,
               emergency_numbers,
               provisioning_delivery,
               id,
               sip_expires,
               pbx_aor_id,
               server_access_call_control_port,
               provisioning_port,
               email_aor_id,
               voicemail_number,
               voicemail_long_timeout,
               certificate_port,
               sip_enabled,
               sip_transport,
               server_access_number,
               service_profile_id,
               server_access_secure_mode,
               voicemail_userid,
               NULL AS seqprofile_id,
               sip_realm,
               voicemail_short_timeout,
               provisioning_host,
               from_domain,
               sip_proxy,
               2 AS clazz_
        FROM   cdm.fmc_service_profile_extension) AS extensions7_
       ON servicepro6_.service_profile_id = extensions7_.service_profile_id
WHERE  personjpa0_.person_id IN (1, 2, [continuous ids], 199, 200);