获取所有属性是否保留在后台进行联接? [英] Does fetch all properties do left join under the hood?

问题描述

我的实体如下所示:为简化起见，我仅显示两个字段.我们决定全部提取一次以加载所有属性.

My entity looks like below: to simplify I am just showing two fields. We decided to do fetch all to load all properties at once.

    @Entity     
    public class Person{
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Set<PhoneNumber> phoneNumbers = new HashSet<>(0);
     @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "AGENCY_ID")
        private Agency agency;

左联接:(这将返回重复的记录.)

left join:(This returns duplicate records.)

select person
from Person person left join person.agency ag left join person.phoneNumbers 
where upper(person.name) LIKE '%SU%' and upper(person.status.rcode)   = 'ACTIVE'
order by person.agency.name asc, person.id asc

以不重复的方式加入左连接:，这给出了"ORA-01791:不是SELECTED表达式"，因为我在order子句中使用代理字段.

left join with distinct : This gives "ORA-01791: not a SELECTed expression" because i am using agency field in order clause.

select distinct person
    from Person person left join person.agency ag left join person.phoneNumbers 
    where upper(person.name) LIKE '%SU%' and upper(person.Status.rcode)   = 'ACTIVE'
    order by person.agency.name asc, person.id asc

左联接提取:此方法工作正常，没有重复项.但是在拉动2000人的记录方面表现出色.大约需要15秒，而加入左连接则需要1秒.

left join fetch : This works fine, no duplicates . but has big performance hit for pulling 2000 person records. taking about 15 seconds vs 1 with just left join.

select  person
        from Person person left join fetch person.agency ag left join fetch person.phoneNumbers 
        where upper(person.name) LIKE '%SU%' and upper(person.Status.rcode)   = 'ACTIVE'
        order by person.agency.name asc, person.id asc

获取所有属性:无重复.表现更好... 当我尝试查询如下所示的人(该人没有任何代理商)时:它返回人记录(这意味着它正在进行左联接).

fetch all properties: No duplicates. performs better.But.. When I try to query a person like below (this person doesn't have any agency): it returns the person record (that means it is doing a left join).

 select person
    from Person person 
    fetch all properties
    where upper(person.name) LIKE '%SU%' and upper(person.status) = 'ACTIVE'
    order by  person.id asc

但是当我在下面进行操作时，我不会找回该人的记录. (差异是按子句顺序添加代理机构的字段，在这种情况下，似乎没有进行左联接.)

But when I do below, I don't get the person record back. (Difference is added field of agency in order by clause, seems like it is not doing left join in this case.)

select person
from Person person 
fetch all properties
where upper(person.name) LIKE '%SU%' and upper(person.status) = 'ACTIVE'
order by person.agency.name asc, person.id asc

我正在寻找的是避免重复，并按表现良好的人员的所有领域进行排序.

推荐答案

导航路径语法(person.agency)始终转换为内部联接，这就是为什么没有代理人的人不包括在结果集中的原因.

The navigation path syntax (person.agency) is always translated into an inner join, that's why persons with no agency are not included in the result set.

您必须显式编写一个外部联接，以避免隐式内部联接:

You have to explicitly write an outer join to avoid the implicit inner join:

select person
from Person person left outer join person.agency a
where upper(person.name) LIKE '%SU%' and upper(person.status) = 'ACTIVE'
order by a.name asc, person.id asc

编辑

distinct不起作用，因为按select distinct子句中提到的列中未包含的列进行排序是没有意义的.

distinct in the left join case does not work because it does not make sense to order by columns that are not included in the columns mentioned in the select distinct clause.

如果您想在查询中急于加载惰性关联，则别无选择，只能执行left join fetch.但是也将返回重复项.要消除它们，只需将返回的列表添加到LinkedHashSet即可保留返回的订单:

If you want to eagerly load lazy associations in the query, then you have no choice but to do left join fetch. But duplicates are returned also. To eliminate them, simply add the returned list to a LinkedHashSet to preserve the returned order:

List<Persons> result = new ArrayList(new LinkedHashSet(list))

关于这种方法的不良表现，Hibernate与它无关.您可能在左联接获取的实体中有一些急切加载的实体(因此遭受了n + 1选择问题)，或者重复的实体确实很大(因此制作并传输了很大的结果集).

Regarding the bad performance of this approach, Hibernate has nothing to do with it. You maybe have some eagerly loaded entities in the entities that are left-join-fetched (thus suffering from the n+1 selects problem), or the duplicated entities are really large (thus making and transferring a large result set).

您可能要使用 @BatchSize 而不是左联接获取集合，以避免性能问题.

You may want to use @BatchSize instead of left-join-fetching collections to avoid performance issues.

这篇关于获取所有属性是否保留在后台进行联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！