In the previous post we saw that the Oracle Optimizer has a special operation – SORT GROUP BY ROLLUP – for performing several aggregations of the same population in a single scan, as long as the grouping keys are in a “rollup form”; for example:
(a) (a,b,c) (a,b,c,d)
If there is an index that its leading part is the same as the rollup grouping key, and that at least one of its columns is defined as NOT NULL, and if the optimizer thinks that cost-wise it is worth it, then the sorting part of the operation is omitted and the operation becomes SORT GROUP BY NOSORT ROLLUP.
Here is an example (executed in Oracle 18.3).
SQL> create table t ( 2 x number not null, 3 y number, 4 z number, 5 w char(1000) 6 ); Table created. SQL> insert into t (x,y,z,w) 2 select mod(rownum, 3), 3 mod(rownum, 3)+1, 4 mod(rownum, 3)+2, 5 rownum 6 from dual 7 connect by level <= 1000 8 order by 1,2,3; 1000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(user,'T') PL/SQL procedure successfully completed. SQL> create index i on t (x,y,z); Index created. SQL> set timing on SQL> set autotrace on SQL> select grouping_id(x,y) grp_id, 2 x, 3 y, 4 count(z) 5 from t 6 group by grouping sets ((x),(x,y)); GRP_ID X Y COUNT(Z) ---------- ---------- ---------- ---------- 0 0 1 333 1 0 333 0 1 2 334 1 1 334 0 2 3 333 1 2 333 6 rows selected. Elapsed: 00:00:00.05 Execution Plan ---------------------------------------------------------- Plan hash value: 3362344319 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 3 | 27 | 4 (0)| 00:00:01 | | 1 | SORT GROUP BY NOSORT ROLLUP| | 3 | 27 | 4 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | I | 1000 | 9000 | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 4 physical reads 0 redo size 889 bytes sent via SQL*Net to client 624 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed