DBA Hub

📋Steps in this guide1/3

GROUP BY ALL in Oracle Database 23ai/26ai

Oracle database 23ai/26ai (23.9) introduces the ALL keyword to simplify the GROUP BY clause.

oracle 23configurationintermediate
by OracleDba
13 views
1

Setup

We create a test user. We connect to the test user and create a table to run our queries against. All actions will be done using this test user.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant db_developer_role to testuser1;
grant alter session to testuser1;

conn testuser1/testuser1@//localhost:1521/freepdb1

create table t1 as select * from all_objects;
2

GROUP BY ALL

In previous releases we had to include all non-aggregated columns from the list into the clause. In the following example notice we have included , and in both the list and the clause. In this case the list is small and simple, but imagine if it were long and/or complex. This can make writing SQL statements with a clause rather clumsy. In Oracle database 23ai/26ai (23.9) we can use the keyword to easily reference all non-aggregated columns in the clause.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
column owner format a30

select owner, object_type, status, count(*)
from   t1
group by owner, object_type, status
order by 1, 2;

select owner, object_type, status, count(*)
from   t1
group by all
order by 1, 2;
3

Query Transformation

This is syntactic sugar , as the optimizer is replacing the keyword with references to all the non-aggregated columns, as shown below. We connect to a privileged user, flush the shared pool, then reconnect to our test user. We identify the trace file for our session. We initiate the 10053 trace, run a statement using the clause, and turn off the trace. We open the trace file and look for the phrase "Final query after transformations". Notice the clause includes all the non-aggregated columns after the query transformation. For more information see: - SELECT - SQL for Beginners (Part 7) : The GROUP BY Clause and HAVING Clause - GROUP BY and HAVING Clauses Using Column Aliases in Oracle Database 23ai/26ai Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

alter system flush shared_pool;

conn testuser1/testuser1@//localhost:1521/freepdb1

set linesize 100
column value format a60

select value
from   v$diag_info
where  name = 'Default Trace File';

VALUE
------------------------------------------------------------
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_18005.trc

SQL>

alter session set events '10053 trace name context forever';

select owner, object_type, status, count(*)
from   t1
group by all
order by 1, 2;

alter session set events '10053 trace name context off';

Final query after transformations: qb SEL$1 (#1):******* UNPARSED QUERY IS *******
SELECT "T1"."OWNER" "OWNER","T1"."OBJECT_TYPE" "OBJECT_TYPE","T1"."STATUS" "STATUS",COUNT(*) "COUNT(*)"
FROM "TESTUSER1"."T1" "T1"
GROUP BY "T1"."OWNER","T1"."OBJECT_TYPE","T1"."STATUS"
ORDER BY "T1"."OWNER","T1"."OBJECT_TYPE"

Comments (0)

Please to add comments

No comments yet. Be the first to comment!