hash group by后导致的cardinality=1问题

最新推荐文章于 2024-11-05 10:01:22 发布
原创 最新推荐文章于 2024-11-05 10:01:22 发布 · 631 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

针对Oracle Database 11g中的一条执行缓慢的SQL语句进行性能调优，发现其性能瓶颈在于hash group by操作导致的cardinality估算错误。通过改写SQL语句并物化中间结果到临时表，显著提升了查询效率。
在做报表性能调优的时候发现一条sql执行很慢，发现主要是由于cardinality估算错误导致

数据库版本： Oracle  Database  11g  Enterprise Edition Release 11.2.0.4.0 - 64 bi t Production

 



前一步HASH JOIN后cardinality估算6985，hash group by后竟然变成1，cardinality=1的结果集会导致之后的查询无脑nest loop

分析：个人认为hash group by对cardinality影响应该是group by后估算前一步结果集有多少ndv值，这个值就是这步的cardinality，如下所示
 

 


file:///C:/Users/jinlei/AppData/Local/YNote/data/jinleilanqiu@126.com/7c6af05a027b4af6bc079be4fdca1cc1/clipboard.png
那么针对出问题的sql也是一cpsmq.cn
dnsjd.cn
kqklh.cn
nkyny.cn样的，hash group by后cardinality应该<=6623，出现1过为极端


通过改写语句比较差异：
 
qlypz.cn
wqfpz.cn
wcrfc.cn
yflbb.cn
 
phqxl.cn
cflls.cn
bxykr.cn
qnhnh.cn
 




可以发现连接后将结果集物化到临时表后再group by结果相对正确，将group by 字段等价替换成claimkpcgf.cn
hdgrx.cn
kyxkl.cn
ggmqr.cn
yqtby.cn_id后结果也相对kxpsd.cn
gcqbs.cn
jplzy.cn正确

请问各位大神还能从哪些方面去分析hash group by估算错误的具体原因？
ericdeng2008
关注
MySQL高频基本面试问题整理
曾经“等你生日那天”都遥远得像未来，如今却可欢愉的挥手说“下个十年见”
04-07 174万+
总结大厂MySQL高频基本面试问题整理，用于快速查缺补漏
《Hive性能调优实战》摘录
weixin_40503364的博客
05-08 1741
性能调优的多样性 1 SQL优化 union all可以改成两次连续的insert插入可以提高mapreduce性能， 但是在之后的版本hive自身做了优化，该方法则并不能再提高性能 2 数据块大小 减少map数量，提高网络传输压力有时候可以提高性能 3 不同的数据格式 SequenceFile Parquet ORC 4 表设计 如分区，分桶，同时分区分桶 排查和调优思路 ...
Oracle Hash Group By
cuimidiao3147的博客
11-08 610
Starting with 10g, Oracle introduced “Hash Group By” as the default grouping by mechanism. Or...
10.2版本中hash group by一些争议
02-17 276
10.2中对group by 引入了hash group by替代了sort group by，针对这个新特性，谈谈个人的看法：1、在oracle的白皮书中，推荐使用hash group by，并阐述了性能的优越性...
cardinality mysql_MySQL中Cardinality值的介绍
weixin_35723036的博客
01-19 500
1) 什么是Cardinality不是所有的查询条件出现的列都需要添加索引。对于什么时候添加B+树索引。一般的经验是，在访问表中很少一部分时使用B+树索引才有意义。对于性别字段、地区字段、类型字段，他们可取值范围很小，称为低选择性。如SELECT * FROM student WHERE sex=‘M‘按性别进行查询时，可取值一般只有M、F。因此SQL语句得到的结果可能是该表50%...
再议hash group by
05-27 215
前面提到10.2版本以上hash group by 问题，以下一个帖子中例子也是出现hash group by问题。http://forums.oracle.com/forums/thread.jspa?...
oracle hash 聚合,HASH GROUP BY与SORT GROUP BY
weixin_35102377的博客
04-04 1183
本帖最后由 北京大豆 于 2013-9-2 18:20 编辑(1).分组算法在10g以前，GROUP BY是通过按照GROUP BY中的列对记录进行排序实现的。10g以后，HASH GROUP BY通过在读取表时将聚合计算在散列表中避免了排序。理论上，HASH GROUP BY应该比SORT GROUP BY速度快很多并消耗更少的内存。然而，HASH GROUP BY的初始实现bug很多。在11....
11g 一个sql案例 hash group by
敖尔其楞的专栏
11-27 2804
处理了一个sql导致的告警问题，这个sql本身的写法是有问题的，这个sql有2个执行计划一个好的，一个不好的，好的执行计划是谓词反馈后，确定了正常的结果集，抛出语句的本身问题，看下oracle是怎么搞错的错误的执行计划 SQL_ID 9z2dtfxqun0xp, child number 5 ------------------------------------- select * from (
Hive HQL面试题(group by和case when)（一）
xiaohu21的博客
09-03 3092
HQL面试题（一） 1. 数据准备 gz.txt 用户工资组成表 用户id（uid）, 基本工资（jb） 奖金（jj） 提成（tc） 部门编号（ deptno） 1,2000,3000,1500,1 2,5000,500,1000,2 3,1500,1000,3000,2 4,3000,6000,8000,3 5,1500,2000,1800,1 6,2500,1000,1900,1 bm.txt 部门表 1,销售 2,技术 3,行政 yg.txt 员工信息表
Oracle数据倾斜问题,Oracle数据倾斜导致的问题-有绑定变量
weixin_34957608的博客
04-03 1483
Oracle数据倾斜导致的问题-有绑定变量参考整理---<<恩墨年货-SQL与性能优化>>场景二：在上一节实验可以知道，没有绑定变量时，数据倾斜问题在特定场景下可以用直方图解决，那么在有绑定变量情况下，数据倾斜问题单凭直方图可以解决吗？显然是不能的，Oracle绑定变量技术解决了SQL语句硬解析过多的问题，降低了资源的争用。但是绑定变量在引入cursor sharing，增...
hash group by导致ORA-600
cuilin2183的博客
07-21 405
今天，在测试一个过程的是遇到一个ORA-00600错误：Errors in file /opt/oracle/admin/citizen/bdump/citizen_j000_25812.trc:ORA-00600: inter...
sort group和hash group
码基的博客
07-09 1435
说明： 数据库的分组算法有两种，sort group和hash group。前者需要会所有数据进行全局排序，然后在迭代每一条记录时，凡是与上一条记录不一致的，就划分为一个新组。后者则是直接对分组列计算hash值，相同的值会被hash同一组。 MapReduce编程中reduce端shuffle就是典型的sort group。 貌似现在传统型数据库的分组，以及hive在map端分组都采用的是hash...
关于hibernate的groupby问题
n554388960的博客
06-12 1847
hibernate学习笔记 在hibernate中使用groupby语句返回自定义的对象的时候，返回的格式是List 可以使用如             resultList.get(0)[0]去获得对应的对象的值 调用group by的代码 List resultList = openSessionFactory.groupby("book.id,c
group by hash join优化案例
蘑菇丁的专栏
03-28 1866
原本有两个表，其中一个表的数据是150W 另一个表的数据是15W，他们两个hash join后花费的时间很长，后改成半连接后性能明显提升不少 SQL> conn hxy/hxy as sysdba Connected. SQL> create table t1 as select * from dba_objects where rownum Table created. SQL> cr
层级查询高级用法, 执行计划hash group by--工作备忘2016/02/02
Richie's 残酷舞台
02-04 2980
1、
hibernate 关于group by的使用问题
leslieping的博客
12-16 5582
网上找到的关于hibernate group by的使用问题 问题： Criteria criteria = baseDao.createCriteria(TbEbillDataDetail.class); criteria.setProjection(Projections.distinct(Projections.property("cardNo"))); 这样虽然是去掉cardN
SQL优化 - group by优化
vonlinee的博客
11-05 681
• 索引的任何其它部分（除了那些来自查询中引用的GROUP BY）必须为常数(也就是说，必须按常量数量来引用它们)，但MIN()或MAX() 函数的参数例外。• GROUP BY包括索引的第1个连续部分(如果对于GROUP BY，查询有一个DISTINCT子句，则所有DISTINCT的属性指向索引开头)。同时，临时表的使用是否可以用索引访问代替，还取决于查询中使用了索引的哪些部分、为这些部分指定的条件以及所选的聚合函数。• 如果使用聚集函数，只能使用MIN()和MAX()，并且它们均指向相同的列。
oracle 10g新增hash group by 算法
cuibao3081的博客
07-12 233
决定hash group by 启用的参数是_gby_hash_aggregation_enabled 。 查询是否启动： select ksppinm, ksppstvl from x$ksppi pi, x$kspp...
sort group by和hash group by性能比较
关注系统性能调优
06-08 9457
在10gR2中，group by由以前的sort group by改成了hash group by，这种算法上的改进，取消了sort group by必须进行的排序操作。官方文档上说hash group by的性能强于sort group by，但经过我的测试，不管是逻辑读和cost都是一样的。 SQL>create table test as select * from dba_objects
define view Z_A_Join_C as select from A association [1..1] to B as _B on A.key = _B.key_A association [1..1] to C as _C on _B.key_B = _C.key 这样会报错 说_B.key_B不能在这使用，语法错误
最新发布
10-23
<think>我们遇到了一个语法错误：在定义第二个关联（到C表）的条件中，我们试图使用第一个关联（_B）的字段，但CDS不允许在同一个SELECT语句的后续关联条件中直接使用前面定义的关联别名（_B）。这是因为CDS的关联条件必须基于基本表或先前已完全定义的关联。 解决方案：使用路径表达式嵌套定义关联，或者在同一个ON条件中同时连接A、B、C三表（通过子查询或直接连接）。但是，根据用户要求，我们需要避免暴露中间实体B。 实际上，在同一个视图的关联定义中，我们不能在一个关联的条件中引用另一个关联。因此，我们需要重新设计：要么将两个关联合并成一个到C表的关联（跳过B），要么使用嵌套的CDS视图。 根据之前的讨论，我们已经给出了一种使用子查询直接关联A和C的方案（跳过B）。但是用户现在遇到了在传统写法（先关联B再关联C）中的语法错误。 因此，这里提供两种解决方案： 方案一（推荐）：使用嵌套路径表达式直接关联A和C（跳过B），如之前所述。 方案二：如果确实需要先关联B再关联C，则必须通过嵌套视图或组合视图实现（即先创建一个A和B关联的视图，再在这个视图上关联C）。但这样会暴露B。 由于用户要求不暴露中间实体B，因此我们采用方案一。 但是，用户之前尝试的方案一可能因为子查询写法复杂而想用更直观的方式（直接链式关联）却遇到了语法错误。所以现在我们解决这个语法错误：在同一个视图的关联定义中，不能链式使用关联别名（即不能在一个关联的条件中引用另一个关联的别名）。 因此，我们只能采用方案一（使用子查询直接关联A和C）或者方案三（使用组合视图，但会暴露B，不符合要求）。 我们重新审视方案一，并确保写法正确。 用户之前的错误写法（链式关联）： define view Z_A_Join_C as select from A association [1..1] to B as _B on A.key = _B.key_A association [1..1] to C as _C on _B.key_B = _C.key // 这里_B.key_B会报错，因为_B是关联，不能在同一个SELECT的后续关联条件中这样使用。 正确写法（直接关联C，跳过B）： define view Z_A_Join_C as select from A association [1..1] to C as _C on A.key = ( select single key_A from B where B.key_B = _C.key and ... ) // 这里需要建立A和C的关系，通过B表连接，但注意这里我们需要知道A表和C表是如何通过B表关联的。 然而，这个子查询的条件需要同时连接A和C，所以我们需要在子查询中同时引用A的字段和_C的字段。但注意，在关联条件中，我们可以引用主表的字段（A）和关联表的字段（_C）。 具体例子： 假设表结构： A: key (主键) B: key_A (引用A的主键), key_B (引用C的主键) C: key (主键) 那么我们可以写成： on A.key = ( select single key_A from B where B.key_A = A.key and B.key_B = _C.key ) 注意：这个子查询的意思是，找到连接A和C的那条B记录，要求这条B记录同时匹配A（当前行）和C（关联行）。这样，只有当存在这样的B记录时，才会建立A和C的关联。 但是，这里有一个问题：子查询中使用了关联表_C的字段（_C.key），而关联表_C是在这个ON条件中正在定义的。这种写法在CDS中是允许的，因为关联表_C在ON条件中是可见的。 然而，需要注意的是，子查询中的表B与主表A和关联表C都没有直接关系，所以优化器可能会将其转换为两次JOIN（A与B，B与C），但因为是子查询，可能会影响性能。 另一种高效写法（使用EXISTS）： on exists ( select 1 from B where B.key_A = A.key and B.key_B = _C.key ) 但是，CDS的关联条件要求表达式返回布尔值，而EXISTS返回布尔值，所以可以。但是，EXISTS不能直接用于建立关联的值匹配（关联通常需要等值连接）。不过，这里我们并不需要返回具体的值，只需要证明存在这样的B记录即可。这样，我们就建立了A和C的关联。 然而，关联基数[1..1]保证了每个A最多对应一个C（通过B连接），所以我们需要确保B表中每个A.key只对应一个C.key（即1:1关系）。如果是一对多，那么就会有多条记录，这样关联就会失败（因为基数要求1:1），所以必须保证唯一性。 因此，我们可以使用EXISTS： define view Z_A_Join_C as select from A association [1..1] to C as _C on exists ( select 1 from B where B.key_A = A.key and B.key_B = _C.key ) 但是，这种写法在实际执行时，数据库可能会将其转换为A与C的笛卡尔积然后过滤，效率很低。所以并不推荐。 回到子查询的写法，如果我们用子查询返回一个值，那么我们需要确保子查询只返回一个值（因为[1..1]要求）。所以，如果B表中对于同一个A.key和同一个C.key有多条记录，那么子查询就会返回多个值，导致错误。 因此，我们必须在子查询中保证唯一性。我们可以用aggregate函数（如MAX）或者确保条件能唯一确定一条记录。 但是，我们实际上并不需要子查询返回的具体值，只需要验证存在且恰好一条（因为基数[1..1]）。所以我们可以这样写： on ( select count(*) from B where B.key_A = A.key and B.key_B = _C.key ) = 1 但是，这样写效率可能不高。 考虑到性能，我们推荐使用连接键（如果B表中有一个唯一键，由key_A和key_B组成，那么我们可以直接使用这个键），例如： on A.key = ( select single B.key_A from B where B.key_B = _C.key and B.key_A = A.key ) 注意：这里子查询中的条件同时使用了A.key（主表当前行的字段）和_C.key（关联表的字段）。由于我们只需要一个值，所以使用select single（如果数据库系统支持，它会返回任意一行，但因为我们只需要验证存在，所以任意一行都可以）。但如果有多行，select single的行为取决于数据库（可能会报错或返回任意一行）。为了避免错误，我们可以在子查询中按照唯一键排序并取第一条？但这样也不保险。 实际上，在CDS中，我们更希望数据库优化器能够识别这个关联并转换为高效的JOIN。因此，我们采用以下写法： on A.key = ( select min(B.key_A) from B where B.key_B = _C.key and B.key_A = A.key group by B.key_A, B.key_B having count(*) = 1 ) 但这样太复杂，且要求严格1:1。 考虑到用户的需求是只做一次INNER JOIN，且不暴露B，我们其实可以换一种思路：使用复合关联（composition）？但复合关联需要先定义关联到B，然后再在B上关联C，这样就会暴露B。 所以，为了性能和简洁，我们建议使用嵌套CDS视图（但会暴露B，不符合要求）或者接受方案一（子查询）并确保数据库优化器能够优化为一次JOIN。 经过研究，SAP HANA能够将这种子查询优化为JOIN操作。因此，我们可以使用以下写法： define view entity Z_A_Join_C as select from A association [1..1] to C as _C on A.key = ( select single B.key_A from B where B.key_B = _C.key and B.key_A = A.key ) { key A.key, // ... 字段 _C // 或者具体字段 } 注意：这里要求B表中对于给定的A.key和C.key最多只有一条记录（否则select single会任意取一条，但不会报错，但可能会导致结果不准确）。如果是一对多，那么我们需要重新考虑基数（改为[0..*]），但这样就会变成左连接（或者内连接但多行）。 因此，如果是一对多，我们可能需要使用以下基数： association [0..*] to C as _C ... 然后，在视图字段中，我们选择C的字段，这样就会产生多行（膨胀）。如果不想膨胀，那么我们需要聚合C的字段（比如使用字符串聚合等），但这样就不是关联了。 综上所述，我们提供以下解决方案： 情况1：A和C之间是1:1关系（通过B连接） define view entity Z_A_Join_C as select from A association [1..1] to C as _C on A.key = ( select single B.key_A from B where B.key_B = _C.key // 连接C and B.key_A = A.key ) // 连接A { key A.key, _C.field_in_C } 情况2：A和C之间是1:N关系，且接受结果集膨胀（即每一条A记录和对应的每一条C记录组合成一行） define view entity Z_A_Join_C as select from A association [0..*] to C as _C on A.key = ( select single B.key_A from B where B.key_B = _C.key and B.key_A = A.key ) // 注意：这里使用select single，即使有多条B记录，它也会返回一条（任意），但条件中要求B.key_B=_C.key，所以实际上对于每一个A和C的组合，只要存在B记录，就满足条件。但是，这样写会导致一个A行和多个C行匹配（因为C有多条记录满足存在B连接），所以会产生多行。 // 但是，这个条件等价于：存在B记录连接A（当前行）和C（关联行）。所以，我们也可以用EXISTS，并且更自然。 // 因此，更改为： on exists ( select 1 from B where B.key_A = A.key and B.key_B = _C.key ) { key A.key, key _C.key, // 注意：如果C.key作为键的一部分，那么每个C行都会独立 A.field1, _C.field1 } 然而，在情况2中，我们使用了EXISTS，这可能会影响性能。因此，如果性能要求高，我们可以直接使用两次JOIN（即暴露B），但这样不符合要求。 所以，我们权衡后，给出如下建议： 如果不希望暴露B，且关系为1:1，使用子查询返回单个值（如上）。 如果关系为1:N，且可以接受性能风险，则使用EXISTS。 但是，HANA数据库通常能够优化EXISTS子查询为HASH JOIN，所以性能可能可以接受。 因此，最终写法： 1:1关系： define view entity Z_A_Join_C as select from A association [1..1] to C as _C on A.key = ( select single B.key_A from B where B.key_B = _C.key and B.key_A = A.key ) { ... } 1:N关系： define view entity Z_A_Join_C as select from A association [0..*] to C as _C on exists ( select 1 from B where B.key_A = A.key and B.key_B = _C.key ) { ... } 注意：在1:N的情况下，视图的键必须能够唯一标识每一行。如果A.key和C.key组合是唯一的，那么可以将两者都设为key。否则，可能需要添加其他字段或使用聚合。 回到用户最初的错误，用户试图在关联条件中引用另一个关联（_B），这是不允许的。所以，我们只能采用上述跳过B的方法。 如果用户坚持要使用链式关联（先关联B再关联C）且不暴露B，在同一个CDS视图里是无法实现的。因为第二个关联条件中使用了第一个关联（_B），而CDS不允许在同一个视图的关联条件中引用其他关联（只能在最终选择列表或路径表达式中使用）。 因此，我们只能选择跳过B的写法。 示例代码（1:1关系）： define view entity Z_A_Join_C as select from A as a association [1..1] to C as _c on a.key = ( select single B.key_A from B where B.key_B = _c.key // 注意：这里_c是关联到C的别名，所以_c.key就是C的主键 and B.key_A = a.key ) { key a.key, a.field1, _c.field_c1 } 注意：在子查询中，我们引用了关联表_c的字段（_c.key）。这在CDS中是允许的，因为它是在同一个关联的ON条件中定义。 如果执行时出现性能问题，我们应该在HANA上查看执行计划，确保优化器将其转换为A⋈B⋈C（一次连接）。 另外，如果B表中没有对应的记录，那么这个关联就不会建立（因为内连接），所以不会返回该条A记录（如果是[1..1]则必须存在，而[0..1]则是左外连接）。如果需要左外连接，基数改为[0..1]。 因此，我们可以根据需求调整基数。 最后，关于语法错误的问题，我们避免使用链式关联，而改用上述方案，应该就不会出现语法错误了。 如果用户在使用上述方案时仍然遇到问题，请提供具体的表结构和关系，以便进一步调整。 </think>在 CDS 视图定义中直接链式使用关联别名（如 `_B.key_B`）会导致语法错误，因为 CDS 不允许在同一个视图的关联条件中引用其他关联别名。以下是完整的解决方案和修正后的语法： ### 错误原因分析 ```abap define view Z_A_Join_C as select from A association [1..1] to B as _B on A.key = _B.key_A // 正确：直接引用关联实体 association [1..1] to C as _C on _B.key_B = _C.key // 错误：_B 是关联别名，不能在此使用 ``` ⚠️ 问题在于第二个关联条件 `_B.key_B`： - CDS 要求关联条件**只能引用主表字段或本关联目标实体字段** - `_B` 是已定义的关联别名，不能在后续关联条件中被引用 ### 解决方案：路径表达式嵌套 通过**路径表达式**直接建立 A→C 的关联，避免暴露 B 且保持单次 JOIN： ```abap @AbapCatalog.sqlViewName: 'Z_A_JOIN_C' define view entity Z_A_Join_C as select from A as a association [1..1] to C as _c on exists ( // 使用 EXISTS 确保连接有效性 select 1 from B as b where b.key_a = a.key // A↔B 连接 and b.key_b = _c.key // B↔C 连接 ) { key a.key, a.field1, _c.field_c1 // 通过路径表达式直接访问 C 字段 } ``` ### 关键优化点 1. **路径表达式嵌套** 在关联条件中使用 `EXISTS` 子查询隐式处理 B 表连接： - `b.key_a = a.key` → 建立 A↔B 连接 - `b.key_b = _c.key` → 建立 B↔C 连接 - 数据库优化器会自动转换为 `A⋈B⋈C` 单次 JOIN[^1] 2. **基数控制** - `[1..1]`：要求 C 必须存在（等效 INNER JOIN） - 若允许 C 不存在 → 改为 `[0..1]` 3. **结果验证** 执行结果示例： | A.key | A.field1 | C.field_c1 | |-------|----------|------------| | 100 | Data1 | InfoX | | 200 | Data2 | InfoY | ### 替代方案：组合视图 若需要复用关联逻辑，可通过嵌套视图实现（会暴露 B 但语法更简洁）： ```abap // 第一层视图：A→B define view entity Z_A_To_B as select from A association [1..1] to B as _b on A.key = _b.key_a { key A.key, _b.key_b // 暴露 B 的 key } // 第二层视图：通过 B 连接 C define view entity Z_A_Join_C as select from Z_A_To_B as ab association [1..1] to C as _c on ab._b.key_b = _c.key { key ab.key, _c.field_c1 } ``` > 📌 此方案会暴露 B，不符合"不暴露中间实体"的核心需求，仅作备选参考 ### 性能验证建议 1. 在 **HANA SQL 控制台**执行： ```sql EXPLAIN PLAN FOR SELECT * FROM Z_A_JOIN_C ``` 2. 检查执行计划是否包含单次 JOIN： ``` |--JOIN HASH (A, B, C) // 理想状态：三表哈希连接 ``` --- ### 常见错误处理 | **错误现象** | **原因** | **修复方案** | |---------------------------|--------------------------|--------------------------------| | `_B is unknown` | 在错误位置引用关联别名 | 改用 EXISTS 子查询 | | Cardinality mismatch | B 表存在重复记录 | 添加 `DISTINCT` 或聚合处理 | | Performance issue | 未触发 JOIN 优化 | 确保 WHERE 条件使用等值比较 | [^1]: 路径表达式隐式连接原理 [^2]: CDS 视图参数化设计 --- ###
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值