Oracle23c: Simplifying Query Development with Improved GROUP BY and HAVING Clauses

vijay balebail
Oracle Developers
Published in
3 min readJul 24, 2023
Flamboyance of Flamingoes

Introduction

Oracle23c brings exciting new enhancements that significantly streamline query development and improve the readability of SQL statements. In previous database releases, developers were required to repetitively reference column definitions in the GROUP BY and HAVING clauses, which could be cumbersome and error-prone. However, with Oracle23c, developers can now utilize column aliases in these clauses, making queries more concise and easier to interpret. Additionally, Oracle has introduced support for column position in the GROUP BY and ORDER BY clauses, enhancing the overall flexibility of query writing.

Simplified GROUP BY and HAVING Clauses

Let’s take a look at a sample query in previous releases:

SELECT object_name,object_type, 
to_char(created,'YYYY-MM-DD HH24') as CREATEDi_ALIAS, count(1) as COUNT
FROM all_objects
GROUP BY object_name, object_type, to_char(created,'YYYY-MM-DD HH24')
HAVING count(1) > 10
ORDER BY COUNT
FETCH FIRST 20 ROWS ONLY;

In this example, we group by to_char(created,’YYYY-MM-DD HH24') and use count(1) in the HAVING clause. However, in Oracle23c, we can now utilize the column aliases directly in the GROUP BY and HAVING clauses:

SELECT object_name,object_type, 
to_char(created,'YYYY-MM-DD HH24') as CREATED_ALIAS, count(1) as COUNT
FROM all_objects
GROUP BY object_name, object_type, CREATED_ALIAS
HAVING COUNT > 10
ORDER BY COUNT
FETCH FIRST 20 ROWS ONLY;

This improvement simplifies the query and makes it more intuitive for developers to understand.

GROUP BY with Column Position Clause

Oracle Database always was able to use alias and column positions to sort data using ORDER BY. In Oracle23c, the GROUP BY clause now supports column position as well. As the previous example shows, we can GROUP the results by the time1 column, referenced by its alias. However, Oracle has gone a step further by introducing support for column position in the GROUP BY clause as well. To enable this feature, you need to set the group_by_position_enabled parameter to true. ( Default is FALSE)

Set the parameter at the session level or system level.

ALTER SESSION SET group_by_position_enabled = true;
ALTER SYSTEM SET group_by_position_enabled = true;

With this parameter enabled, you can now use column positions in the

GROUP BY clauses. For instance:

SELECT object_name,object_type, 
to_char(created,'YYYY-MM-DD HH24') as CREATED_ALIAS, count(1) as COUNT
FROM all_objects
GROUP BY 1,2,3
HAVING COUNT > 10
ORDER BY COUNT
FETCH FIRST 20 ROWS ONLY;

Conclusion

The enhancements introduced in Oracle23c pave the way for more intuitive and efficient query development. Developers can write more apparent and concise SQL statements by allowing column aliases in the GROUP BY and HAVING clauses. Additionally, the support for column positions in the GROUP BY and ORDER BY clauses further enhances the flexibility of query writing.

These improvements make Oracle23c a developer-friendly platform and facilitate the migration process from other databases that support alias functionality. With Oracle23c, developers can focus more on query development and less on adapting queries for different platforms, ultimately leading to improved application migration and development experiences.

--

--