impala和大表关联时，大表限制语句写在on里，或者写在子查询里，哪个sql查询的速度快?

最新推荐文章于 2024-04-24 18:27:59 发布

五块兰州拉面

最新推荐文章于 2024-04-24 18:27:59 发布

阅读量307

点赞数

分类专栏：工作文章标签： sql 哈希算法数据库

本文链接：https://blog.csdn.net/qq_39331255/article/details/125391401

版权

工作专栏收录该内容

15 篇文章 1 订阅

订阅专栏

相同，执行计划完全一样，都是扫描大表时加上限制条件，然后去做关联
数据量：b:55 a:19321755

explain select count(1)
 from stg_data.ODS_NKG_SIMPLEAGENTINFO b
 left join [SHUFFLE] stg_data.ODS_NKG_FUNDINFO a
    on a.FUNDID=b.FUNDID and a.custid=1100020376

+------------------------------------------------------------------------------------+
| Explain String                                                                     |
+------------------------------------------------------------------------------------+
| Max Per-Host Resource Reservation: Memory=34.00MB                                  |
| Per-Host Resource Estimates: Memory=2.08GB                                         |
| WARNING: The following tables are missing relevant table and/or column statistics. |
| stg_data.ods_nkg_fundinfo, stg_data.ods_nkg_simpleagentinfo                        |
|                                                                                    |
| PLAN-ROOT SINK                                                                     |
| |                                                                                  |
| 07:AGGREGATE [FINALIZE]                                                            |
| |  output: count:merge(*)                                                          |
| |                                                                                  |
| 06:EXCHANGE [UNPARTITIONED]                                                        |
| |                                                                                  |
| 03:AGGREGATE                                                                       |
| |  output: count(*)                                                                |
| |                                                                                  |
| 02:HASH JOIN [LEFT OUTER JOIN, PARTITIONED]                                        |
| |  hash predicates: b.FUNDID = a.FUNDID                                            |
| |                                                                                  |
| |--05:EXCHANGE [HASH(a.FUNDID)]                                                    |
| |  |                                                                               |
| |  01:SCAN HDFS [stg_data.ods_nkg_fundinfo a]                                      |
| |     partitions=54/54 files=54 size=758.66MB                                      |
| |     predicates: a.custid = 1100020376                                            |
| |                                                                                  |
| 04:EXCHANGE [HASH(b.FUNDID)]                                                       |
| |                                                                                  |
| 00:SCAN HDFS [stg_data.ods_nkg_simpleagentinfo b]                                  |
|    partitions=55/56 files=55 size=297.93KB                                         |
+------------------------------------------------------------------------------------+

explain select count(1)
 from stg_data.ODS_NKG_SIMPLEAGENTINFO b
 left join [SHUFFLE] (select * from stg_data.ODS_NKG_FUNDINFO where custid=1100020376) a
    on a.FUNDID=b.FUNDID

+------------------------------------------------------------------------------------+
| Explain String                                                                     |
+------------------------------------------------------------------------------------+
| Max Per-Host Resource Reservation: Memory=34.00MB                                  |
| Per-Host Resource Estimates: Memory=2.08GB                                         |
| WARNING: The following tables are missing relevant table and/or column statistics. |
| stg_data.ods_nkg_fundinfo, stg_data.ods_nkg_simpleagentinfo                        |
|                                                                                    |
| PLAN-ROOT SINK                                                                     |
| |                                                                                  |
| 07:AGGREGATE [FINALIZE]                                                            |
| |  output: count:merge(*)                                                          |
| |                                                                                  |
| 06:EXCHANGE [UNPARTITIONED]                                                        |
| |                                                                                  |
| 03:AGGREGATE                                                                       |
| |  output: count(*)                                                                |
| |                                                                                  |
| 02:HASH JOIN [LEFT OUTER JOIN, PARTITIONED]                                        |
| |  hash predicates: b.FUNDID = stg_data.ods_nkg_fundinfo.fundid                    |
| |                                                                                  |
| |--05:EXCHANGE [HASH(stg_data.ods_nkg_fundinfo.fundid)]                            |
| |  |                                                                               |
| |  01:SCAN HDFS [stg_data.ods_nkg_fundinfo]                                        |
| |     partitions=54/54 files=54 size=758.66MB                                      |
| |     predicates: custid = 1100020376                                              |
| |                                                                                  |
| 04:EXCHANGE [HASH(b.FUNDID)]                                                       |
| |                                                                                  |
| 00:SCAN HDFS [stg_data.ods_nkg_simpleagentinfo b]                                  |
|    partitions=55/56 files=55 size=297.93KB                                         |
+------------------------------------------------------------------------------------+