oracle 优化JPA动态计数查询

vmpqdwk3 于 2023-01-20 发布在 Oracle

关注(0)|答案(2)|浏览(150)

bounty将在6天后过期。回答此问题可获得+200声望奖励。anat0lius希望引起更多人关注此问题。

具有返回分页结果的典型方法，使用CriteriaBuilder并执行2个查询：

计算结果总数的方法
另一个为指定页面提供子集

我们注意到，第一个查询JPA根本没有优化它，因为它使用的是exists（来自Oracle）。
Java代码：

Root<Foo> from = criteriaQuery.from(Foo.class);
//... predicates
CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class)
        .select(criteriaBuilder.countDistinct(from))
        .where(predicates.toArray(new Predicate[predicates.size()]));
Long numberResults = entityManager.createQuery(countQuery).getSingleResult();

SQL生成的查询：

SELECT COUNT(t0.REFERENCE) 
FROM foo t0 
WHERE EXISTS (
  SELECT t1.REFERENCE 
  FROM foo t1 
  WHERE ((((t0.REFERENCE = t1.REFERENCE) AND (t0.VERSION_NUM = t1.VERSION_NUM)) AND (t0.ISSUER = t1.ISSUER)) AND (t1.REFERENCE LIKE ? AND (t1.VERSION_STATUS = ?)))
);

如何避免使用exists？java代码有什么问题吗？

oracle

来源：https://stackoverflow.com/questions/74772255/optimize-jpa-dinamyc-count-query

2条答案

按热度按时间

e5nqia271#

不管有没有EXISTS，查询计划都是相同的。唯一的优化是返回COUNT（）和结果在同一个查询中，容易在SQL中用“OVER”来完成（）"。但是将Foo.classMap到视图上并添加一个 transient 列来包含计数将使应用程序的许多其他部分复杂化，并且将分页查询的结果Map到新的CountedFoo.class也会使解决方案复杂化。

赞(0）回复(0）举报 2023-01-20

xqkwcwgp2#

不知道如何生成它，但是-如果主要的事情是避免使用EXISTS，那么您可以尝试使用LEFT JOIN，排除左连接表列的Null值。
你可以这样试试

SELECT  Count(t0.REFERENCE)     -- t0.*
FROM    foo t0 
LEFT JOIN foo t1    ON( t0.REFERENCE =  t1.REFERENCE AND 
                        t0.VERSION_NUM = t1.VERSION_NUM AND 
                        t0.ISSUER = t1.ISSUER AND 
                        t1.REFERENCE LIKE '%Ref 1%' AND
                        t1.VERSION_STATUS = 'A')                                    
WHERE t1.REFERENCE Is Not Null

使用一些示例数据，如

WITH
    foo AS
        (
            Select  1 "ID", 'Ref 1.0' "REFERENCE",  'ver. 1.11.01' "VERSION_NUM", 'Some Issuer'     "ISSUER",   'A' "VERSION_STATUS" , 'Something else' "SOME_OTHER_COL" From Dual Union All
            Select  2 "ID", 'Ref 1.1' "REFERENCE",  'ver. 1.11.01' "VERSION_NUM", 'Some Issuer'     "ISSUER",   'A' "VERSION_STATUS" , 'Something else' "SOME_OTHER_COL" From Dual Union All
            Select  3 "ID", 'Ref 1.2' "REFERENCE",  'ver. 1.11.01' "VERSION_NUM", 'Some Issuer'     "ISSUER",   'I' "VERSION_STATUS" , 'Something else' "SOME_OTHER_COL" From Dual Union All
            Select  4 "ID", 'Ref 2.0' "REFERENCE",  'ver. 1.11.01' "VERSION_NUM", 'Some Other Issuer' "ISSUER", 'A' "VERSION_STATUS" , 'Something else' "SOME_OTHER_COL" From Dual Union All
            Select  5 "ID", 'Ref 3.0' "REFERENCE",  'ver. 1.11.01' "VERSION_NUM", 'Some Other Issuer' "ISSUER", 'I' "VERSION_STATUS" , 'Something else' "SOME_OTHER_COL" From Dual Union All
            Select  6 "ID", 'Ref 3.1' "REFERENCE",  'ver. 1.11.01' "VERSION_NUM", 'Some Third Issuer' "ISSUER", 'A' "VERSION_STATUS" , 'Something else' "SOME_OTHER_COL" From Dual
        )

结果（聚合和非聚合）将为

--  IF Count(t0.REFERENCE) Is Selected 
COUNT(T0.REFERENCE)
-------------------
                  2

--  IF  t0.* Is Selected 
        ID REFERENCE VERSION_NUM  ISSUER            VERSION_STATUS SOME_OTHER_COL
---------- --------- ------------ ----------------- -------------- --------------
         1 Ref 1.0   ver. 1.11.01 Some Issuer       A              Something else 
         2 Ref 1.1   ver. 1.11.01 Some Issuer       A              Something else

如果将查询（使用EXISTS）应用于相同的样本数据，结果将相同

SELECT    Count(t0.REFERENCE)   -- t0.*
FROM      foo t0 
WHERE     EXISTS  ( SELECT  t1.REFERENCE 
                    FROM    foo t1 
                    WHERE   t0.REFERENCE = t1.REFERENCE AND 
                            t0.VERSION_NUM = t1.VERSION_NUM AND 
                            t0.ISSUER = t1.ISSUER AND 
                            t1.REFERENCE LIKE '%Ref 1%' AND
                            t1.VERSION_STATUS = 'A' )
--  IF Count(t0.REFERENCE) Is Selected  
COUNT(T0.REFERENCE)
-------------------
                  2
                  
--  IF  t0.* Is Selected 
        ID REFERENCE VERSION_NUM  ISSUER            VERSION_STATUS SOME_OTHER_COL
---------- --------- ------------ ----------------- -------------- --------------
         1 Ref 1.0   ver. 1.11.01 Some Issuer       A              Something else 
         2 Ref 1.1   ver. 1.11.01 Some Issuer       A              Something else

赞(0）回复(0）举报 2023-01-20

我来回答

oracle 优化JPA动态计数查询

2条答案

相关问题

热门标签

最新问答