It should have same results by using [shuffle] or not, no need to worry about it.
-- 此致！Best Regards 陈明雨 Mingyu Chen Email: chenmin...@apache.org At 2021-09-01 18:02:09, "Thomas Cai" <thomas...@126.com> wrote: >Dear MingYu, > > > > >Great, it works. I did two changes as following: >At first, I changed a big table as left table then found an error (unknown >field a.producttype). I assumed it was related with partition. >Then, I added [Shuffle] join type on the table with the error message. The >result had the same number of records result as the initial SQL. > > > > >There is an additional question: Can I believe that the records are totally >the same with [Shuffle] join, or I have to verify the records of the new SQL? >Thanks. > > >Here is the modified SQL as following: >==================================== >SELECT > a.projectid, > a.ContractGUID, > c.CalID, > c.calperiod, > c.teamtype, > c.HKRatio, > d.MeasureMemberID, > e.producttype, > e.salestage, > e.commisionstage, > f.sequence >FROM yj_roompayment_doris c > JOIN yj_contractinfo_doris a ON > a.projectid = c.projectid > AND a.contractguid = c.contractguid > AND a.ContractType = c.ContractType > JOIN yj_salestage_doris b ON b.CalID = c.CalID > AND b.projectid = c.projectid > AND b.contractguid = c.contractguid > JOIN yj_measure_limit d ON d.CalID = c.CalID > AND d.projectid = c.projectid > AND d.SaleStage = b.SaleStage > AND d.producttype = a.producttype > AND c.ContractType = d.ContractType > AND c.HKRatio BETWEEN d.min AND d.max > JOIN [Shuffle] yj_paramrules e ON c.projectid = e.projectid > AND d.MeasureMemberID = e.MeasureMember > AND d.producttype = e.producttype > AND d.salestage = e.salestage > AND a.ContractType = e.ContractType > AND a.qsdate BETWEEN e.begindate AND e.enddate > JOIN yj_dimensionmember f ON e.commisionstage = f.memberid >WHERE > c.CalID = '898' > AND c.ProjectID = '26d614143fa73c16c4c8149455311e34' > AND c.CalPeriod = 'c709bdf3ff7da83e23e1a1fc1289b68e' > AND c.teamtype = 'ffab7099a45eb01583ea3b43faefad66' >====================================================== > > > > > >At 2021-09-01 13:06:57, "陈明雨" <morning...@163.com> wrote: >>You can refer to >>https://mp.weixin.qq.com/s/YNGdTbnG6iBjT1qtsK912w >> >> >>to see if it can help >> >> >> >>-- >> >>此致！Best Regards >>陈明雨 Mingyu Chen >> >>Email: >>chenmin...@apache.org >> >> >> >> >> >>At 2021-09-01 12:39:11, "Thomas Cai" <thomas...@126.com> wrote: >>>Dear All, >>> >>> >>>I am having the SQL execution performance issue. The followings are the SQL >>>and the result of SQL explain. But I have no ideas on how to tune the SQL to >>>improve. >>> >>> >>>Expect to receive your suggestions. Thanks. >>> >>> >>>Best Regards, >>>Thomas >>> >>> >>>=========SQL START================ >>>SELECT >>>a.projectid, >>>a.ContractGUID, >>>c.CalID, >>>c.calperiod, >>>c.teamtype, >>>c.HKRatio, >>>d.MeasureMemberID >>>FROM >>>yj_contractinfo_doris a >>>JOIN yj_salestage_doris b ON b.CalID = '898' and a.ProjectID = >>>'26d614143fa73c16c4c8149455311e34' >>>-- AND a.projectid = b.projectid >>>AND b.contractguid = a.contractguid >>>JOIN yj_roompayment_doris c ON c.calid = '898' >>>-- AND a.projectid = c.projectid >>>AND c.contractguid = a.contractguid >>>AND c.ContractType = a.ContractType >>>-- AND b.CalID = c.CalID >>>AND c.CalPeriod = 'c709bdf3ff7da83e23e1a1fc1289b68e' >>>AND c.teamtype = 'ffab7099a45eb01583ea3b43faefad66' >>>AND c.CalID = '898' >>>JOIN yj_measure_limit d ON d.CalID = '898' >>>AND d.projectid = a.projectid >>>AND d.producttype = a.producttype >>>AND d.SaleStage = b.SaleStage >>>AND d.ContractType = c.ContractType >>>AND c.HKRatio >=d.min and >>>c.HKRatio<= d.max >>>===========SQL END===================================== >>>Explain SQL Result as following: >>>===========EXPLAIN START================== >>>PLAN FRAGMENT 0 >>> OUTPUT EXPRS:`a`.`projectid` | `a`.`ContractGUID` | `c`.`CalID` | >>> `c`.`calperiod` | `c`.`teamtype` | `c`.`HKRatio` | `d`.`MeasureMemberID` >>> PARTITION: UNPARTITIONED >>> >>> >>> RESULT SINK >>> >>> >>> 10:EXCHANGE >>> tuple ids: 1 2 3 0 >>> >>> >>>PLAN FRAGMENT 1 >>> OUTPUT EXPRS: >>> PARTITION: RANDOM >>> >>> >>> STREAM DATA SINK >>> EXCHANGE ID: 10 >>> UNPARTITIONED >>> >>> >>> 6:HASH JOIN >>> | join op: INNER JOIN (BROADCAST) >>> | hash predicates: >>> | colocate: false, reason: left hash join node can not do colocate >>> | equal join conjunct: `b`.`contractguid` = `a`.`contractguid` >>> | equal join conjunct: `c`.`contractguid` = `a`.`contractguid` >>> | equal join conjunct: `c`.`ContractType` = `a`.`ContractType` >>> | equal join conjunct: `d`.`projectid` = `a`.`projectid` >>> | equal join conjunct: `d`.`producttype` = `a`.`producttype` >>> | tuple ids: 1 2 3 0 >>> | >>> |----9:EXCHANGE >>> | tuple ids: 0 >>> | >>> 4:HASH JOIN >>> | join op: INNER JOIN (BROADCAST) >>> | hash predicates: >>> | colocate: false, reason: Node type not match >>> | equal join conjunct: `b`.`SaleStage` = `d`.`SaleStage` >>> | equal join conjunct: `c`.`ContractType` = `d`.`ContractType` >>> | other predicates: `c`.`HKRatio` >= `d`.`min`, `c`.`HKRatio` <= `d`.`max` >>> | tuple ids: 1 2 3 >>> | >>> |----8:EXCHANGE >>> | tuple ids: 3 >>> | >>> 2:CROSS JOIN >>> | cross join: >>> | predicates is NULL. | tuple ids: 1 2 >>> | >>> |----7:EXCHANGE >>> | tuple ids: 2 >>> | >>> 0:OlapScanNode >>> TABLE: yj_salestage_doris >>> PREAGGREGATION: OFF. Reason: No AggregateInfo >>> PREDICATES: `b`.`CalID` = 898.0 >>> partitions=2/3 >>> rollup: yj_salestage_doris >>> tabletRatio=24/24 >>> tabletList=11523,11527,11531,11535,11539,11543,11547,11551,11555,11559 >>> ... >>> cardinality=1199149 >>> avgRowSize=21.937193 >>> numNodes=3 >>> tuple ids: 1 >>> >>> >>>PLAN FRAGMENT 2 >>> OUTPUT EXPRS: >>> PARTITION: RANDOM >>> >>> >>> STREAM DATA SINK >>> EXCHANGE ID: 09 >>> UNPARTITIONED >>> >>> >>> 5:OlapScanNode >>> TABLE: yj_contractinfo_doris >>> PREAGGREGATION: OFF. Reason: null >>> PREDICATES: `a`.`ProjectID` = '26d614143fa73c16c4c8149455311e34' >>> partitions=1/1 >>> rollup: yj_contractinfo_doris >>> tabletRatio=1/12 >>> tabletList=17970 >>> cardinality=6092 >>> avgRowSize=72.79629 >>> numNodes=3 >>> tuple ids: 0 >>> >>> >>>PLAN FRAGMENT 3 >>> OUTPUT EXPRS: >>> PARTITION: RANDOM >>> >>> >>> STREAM DATA SINK >>> EXCHANGE ID: 08 >>> UNPARTITIONED >>> >>> >>> 3:OlapScanNode >>> TABLE: yj_measure_limit >>> PREAGGREGATION: OFF. Reason: null >>> PREDICATES: `d`.`CalID` = 898.0 >>> partitions=1/3 >>> rollup: yj_measure_limit >>> tabletRatio=12/12 >>> tabletList=20658,20662,20666,20670,20674,20678,20682,20686,20690,20694 >>> ... >>> cardinality=90938 >>> avgRowSize=5.368526 >>> numNodes=3 >>> tuple ids: 3 >>> >>> >>>PLAN FRAGMENT 4 >>> OUTPUT EXPRS: >>> PARTITION: RANDOM >>> >>> >>> STREAM DATA SINK >>> EXCHANGE ID: 07 >>> UNPARTITIONED >>> >>> >>> 1:OlapScanNode >>> TABLE: yj_roompayment_doris >>> PREAGGREGATION: OFF. Reason: null >>> PREDICATES: `c`.`calid` = 898.0, `c`.`CalPeriod` = >>> 'c709bdf3ff7da83e23e1a1fc1289b68e', `c`.`teamtype` = >>> 'ffab7099a45eb01583ea3b43faefad66', `c`.`CalID` = 898.0 >>> partitions=2/3 >>> rollup: yj_roompayment_doris >>> tabletRatio=24/24 >>> tabletList=13675,13679,13683,13687,13691,13695,13699,13703,13707,13711 >>> ... >>> cardinality=503798 >>> avgRowSize=52.880646 >>> numNodes=3 >>> tuple ids: 2 >>>===========EXPLAIN END============================
