Starting with 21c (and continued in 23ai) I've noticed that Oracle database does not "automatically sort" data when working with set operators such as UNION, MINUS, INTERSECT etc, while it used to sort data till 19c.
Is this behavioral change documented somewhere?
Example Code: WITH
sample_data_1(c_id, c_val) AS(
SELECT 1002,'V2' FROM dual
UNION
SELECT 1001,'V1' FROM dual
),
sample_data_2(c_id, c_val) AS(
SELECT 1004,'V4' FROM dual
UNION
SELECT 1003,'V3' FROM dual
)
SELECT * FROM sample_data_1
UNION
SELECT * FROM sample_data_2
;
OUTPUT in 21c and 23ai:C_ID | C_VAL
---- + --
1002 | V2
1001 | V1
1004 | V4
1003 | V3
OUTPUT in 19c and below:C_ID | C_VAL
---- + --
1001 | V1
1002 | V2
1003 | V3
1004 | V4
It's never been the case that these guaranteed a sort to my knowledge. This line has been in the documentation under the set operators since at least 12.2:
Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
(emphasis mine)
It appears that from 21c it's more likely the database will choose hash-unique for union than a sort-unique.
That said, whichever version you're using the advice is always:
If you want sorted results, you must include an order by!