Oracle PL/SQL group by grouping sets

The use of group by grouping sets is equivalent to Union all for the set requiring group.

When there are more grouping elements in grouping sets, the performance of using grouping sets is better than using Union all, which may be related to using grouping sets to access tables only once.

The results of the following two queries are equal:

 

Q1 (grouping sets ):

Select department_id, job_id, manager_id, AVG (salary) <br/> from employees <br/> group by grouping sets (department_id, job_id), (job_id, manager_id )); </P> <p> department_id job_id manager_id AVG (salary) <br/> ------------- ---------- ------------- <br/> sh_clerk 122 3200 101 <br/> ac_mgr 12000 100 7280 <br/> st_man 121 2675 <br/> st_clerk <br/> sa_rep 148 8650 120 <br/> sh_clerk 2900 124 2825 <br/> sh_clerk 100 13000 <br/> mk_man 24000 101 12000 <br/> ad_pres <br/> fi_mgr <br/> sa_rep 146 8500 123 <br/> sh_clerk 3475 101 4400 <br/> ad_asst 102 9000 103 <br/> it_prog 4950 108 <br/> it_prog 7920 <br/> fi_account <br/> pu_man 100 11000 122 <br/> st_clerk 2700 145 <br/> sa_rep 8500 205 8300 <br/> ac_account 100 17000 <br/> ad_vp </P> <p> department_id job_id manager_id AVG (salary) <br/> ------------- ---------- ------------- <br/> st_clerk 120 2625 124 <br/> st_clerk 2925 147 7766.66667 <br/> sa_rep 149 8333.33333 <br/> sa_rep <br/> hr_rep 101 6500 101 <br/> pr_rep 10000 123 3000 <br/> st_clerk 121 3675 <br/> sh_clerk 114 2780 100 <br/> pu_clerk 12200 <br/> sa_man <br /> mk_rep 201 6000 110 <br/> 8300 ac_account 17000 <br/> 90 ad_vp 2785 <br/> 50 st_clerk 8396.55172 <br/> 80 sa_rep 7280 <br/> 50 st_man <br/> 80 sa_man 12200 <br/> 110 ac_mgr 12000 <br/> 90 ad_pres 24000 <br/> 60 it_prog 5760 <br/> 100 fi_mgr 12000 </P> <p> department_id job_id manager_id AVG (salary) <br/> ------------- ---------- ------------- <br/> 30 pu_clerk 2780 <br/> 50 sh_clerk 3215 <br/> 20 mk_man 13000 <br/> 100 fi_account 7920 <br/> sa_rep 7000 <br/> 70 pr_rep 10000 <br/> 30 pu_man 11000 <br/> 10 ad_asst 4400 <br/> 20 mk_rep 6000 <br/> 40 hr_rep 6500 </P> <p> 52 rows selected.

 

Q2 (Union all ):

Select null department_id, job_id, manager_id, AVG (salary) <br/> from employees <br/> group by (job_id, manager_id) <br/> Union all <br/> select department_id, job_id, null manager_id, AVG (salary) <br/> from employees <br/> group by (department_id, job_id ); </P> <p> department_id job_id manager_id AVG (salary) <br/> ------------- ---------- ------------- <br/> sh_clerk 122 3200 101 <br/> ac_mgr 12000 100 7280 <br/> st_man 121 2675 <br/> st_clerk <br/> sa_rep 148 8650 120 <br/> sh_clerk 2900 124 2825 <br/> sh_clerk 100 13000 <br/> mk_man 24000 101 12000 <br/> ad_pres <br/> fi_mgr <br/> sa_rep 146 8500 123 <br/> sh_clerk 3475 101 4400 <br/> ad_asst 102 9000 103 <br/> it_prog 4950 108 <br/> it_prog 7920 <br/> fi_account <br/> pu_man 100 11000 122 <br/> st_clerk 2700 145 <br/> sa_rep 8500 205 8300 <br/> ac_account 100 17000 <br/> ad_vp </P> <p> department_id job_id manager_id AVG (salary) <br/> ------------- ---------- ------------- <br/> st_clerk 120 2625 124 <br/> st_clerk 2925 147 7766.66667 <br/> sa_rep 149 8333.33333 <br/> sa_rep <br/> hr_rep 101 6500 101 <br/> pr_rep 10000 123 3000 <br/> st_clerk 121 3675 <br/> sh_clerk 114 2780 100 <br/> pu_clerk 12200 <br/> sa_man <br /> mk_rep 201 6000 110 <br/> 8300 ac_account 17000 <br/> 90 ad_vp 2785 <br/> 50 st_clerk 8396.55172 <br/> 80 sa_rep 7280 <br/> 50 st_man <br/> 80 sa_man 12200 <br/> 110 ac_mgr 12000 <br/> 90 ad_pres 24000 <br/> 60 it_prog 5760 <br/> 100 fi_mgr 12000 </P> <p> department_id job_id manager_id AVG (salary) <br/> ------------- ---------- ------------- <br/> 30 pu_clerk 2780 <br/> 50 sh_clerk 3215 <br/> 20 mk_man 13000 <br/> 100 fi_account 7920 <br/> sa_rep 7000 <br/> 70 pr_rep 10000 <br/> 30 pu_man 11000 <br/> 10 ad_asst 4400 <br/> 20 mk_rep 6000 <br/> 40 hr_rep 6500 </P> <p> 52 rows selected.