Select
Case when the grouping (t1) group_type_code)=1 then '999' else t1. Group_type_code end as group_type_code - establishment level
Case when the grouping (t1) regional_office_code)=1 then '999' else t1. Regional_office_code end as regional_office_code, branch -
Case when the grouping (t1) cent_branch_no)=1 then '999' else t1., cent_branch_no end as cent_branch_no - sticks
Count (distinct t1. Party_id) as cnt_cust - customer number
The from a01_app. Tmp_lm3_m_marketing_cust_07 t1
Group by a rollup (t1 group_type_code), a rollup (t1) regional_office_code, t1. Cent_branch_no)
Limit of 111
;
Which table a01_app. Tmp_lm3_m_marketing_cust_07 about 4000 more than the amount of data, at the time of execution of the statement, execution plan shows the
Adopts GroupAggregate polymerization method, data query execution is very slow,
I think HashAggregate polymerization method, could you tell me how can you achieve?
Thank you very much!
The execution plan:
"Limit (cost=7004242.25.. 7031135.83 rows=111 width=1608). "
"- & gt; Append (cost=7004242.25.. 29796672.15 rows=94073 width=1608). "
"- & gt; Append (cost=7004242.25.. 22730829.78 rows=78818 width=1608). "
"- & gt; GroupAggregate (cost=7004242.25.. 7576875.47 rows=22883 width=1608). "
"Group By: t1. Group_type_code, t1 cent_branch_no, t1. Regional_office_code"
"- & gt; Shared Scan (share slice: id's 0-0 draw) (cost=7004242.25.. 7066897.48 rows=40761624 width=57)
""- & gt; Materialize (cost=6596626.01.. 7004242.25 rows=40761624 width=57)
""- & gt; Gather Motion, 156:1 (slice1; Segments: 156) (cost=5638727.85.. 6555864.39 rows=40761624 width=57)
""Merge Key: t1 group_type_code, t1 cent_branch_no, t1. Regional_office_code"
"- & gt; Sort (cost=5638727.85.. 5740631.91 rows=261293 width=57)
""Sort Key: t1 group_type_code, t1 cent_branch_no, t1. Regional_office_code"
"- & gt; Append - only Columnar Scan on tmp_lm3_m_marketing_cust_07 t1 (cost=0.00.. 486314.24 rows=261293 width=57)
""- & gt; GroupAggregate (cost=7004242.25.. 7577028.01 rows=30510 width=1608). "
"Group By: t1. Group_type_code, t1 cent_branch_no"
"- & gt; Shared Scan (share slice: id's 0-0 draw) (cost=7004242.25.. 7066897.48 rows=40761624 width=57)
""- & gt; GroupAggregate (cost=7004242.25.. 7576926.31 rows=25425 width=1608). "
"Group By: t1. Group_type_code"
"- & gt; Shared Scan (share slice: id's 0-0 draw) (cost=7004242.25.. 7066897.48 rows=40761624 width=57)
""- & gt; GroupAggregate (cost=5638727.85.. 7065689.82 rows=15255 width=1608). "
"Group By: t1. Group_type_code, t1 regional_office_code"
"- & gt; Gather Motion, 156:1 (slice2; Segments: 156) (cost=5638727.85.. 6555864.39 rows=40761624 width=57)
""Merge Key: t1 group_type_code, t1 regional_office_code, t1. Cent_branch_no"
"- & gt; Sort (cost=5638727.85.. 5740631.91 rows=261293 width=57)
""Sort Key: t1 group_type_code, t1 regional_office_code, t1. Cent_branch_no"
"- & gt; Append - only Columnar Scan on tmp_lm3_m_marketing_cust_07 t1 (cost=0.00.. 486314.24 rows=261293 width=57)
""Optimizer status: the legacy of the query Optimizer"
CodePudding user response:
Set the work_mem increases,