A question came to me from a DBA friend asking about SQL differences between releases of the database. IE: behavioral differences in SQL processing.
The question posed by this person was that they felt that some older SQL was not forward compatible with the newer Oracle databases. They were not able to produce such an example, though.
What I know is that there have been subtle and important changes in how the data is returned, mostly using a GROUP BY clause. This goes back a ways, so bear with me…but…it might be pertinent to your current applications; who knows?
Prior to Oracle10.2, Oracle RDBMS always did a sort on the GROUP BY clause. It was redundant to include the ORDER BY clause in your code. Maybe we got complacent. Maybe we didn’t think about it at all. Oracle10.2 changed the internal sort mechanism and the GROUP BY clause no longer did a sort unless the ORDER BY clause was specified. I would say that this issue should have shown up in migration testing though.
It is good practice and now very important to have an ORDER BY clause associated with the same fields in the GROUP BY clause.
Another subtle change, implemented in Oracle9i, was that the sort now took into account the national language assigned to the database. So, the same data from different countries might not appear in the same order on a report/screen/form even when using an ORDER BY clause.
The only other change over the years and releases has been the cost-based optimizer (CBO) taking over the rule-based optimizer (RBO). The old rule based optimizer may have performed better than some SQL in older releases of the database (prior to Oracle10.2). I say this because release 10.2 was a HUGE change for the CBO, and for the good! Oracle11 removed references to the rule based optimizer but both Oracle11 and Oracle12 still support the RBO when a /*+ RULE */ hint is applied to the SQL.
The RBO had a list of 19 rules that mostly revolved around an index on one or more WHERE clause items. The RBO also drove off the last table in the FROM clause as both optimizers still read backwards through the SQL text, so, the last table in the FROM clause was always the first one it encountered. This would be the leading table on a nested-loop or a sort-merge operation. Hash joins are only CBO operations. The first thing I used to do to tune RBO SQL was to shuffle the tables on the FROM clause to find the best-performing SQL. I still recommend this technique in my tuning class (see my video offerings with discount codes or instructor led offerings at www.DanHotka.com) using the LEADING hint. I have all kinds of SQL performance tips I share in this class. The CBO, of course, has lots of ways it interprets the submitted SQL and my point here is the behavior and possible performance implications are great between the two optimizers.
I see no good reason to be using the RBO anymore. None. Convert your code, fix the poor-performing SQL
So, SQL between releases performed differently but still produced the same results, but perhaps not in the same amount of time.
I know of no SQL code that used to work in prior releases of the Oracle database that doesn’t work in Oracle12.1.0.2.
Dan Hotka
Author/Instructor/Oracle Expert
The reason GROUP BY no longer resulted in a default sort in Oracle 10.2 is that Oracle started using a hash value for the grouping rather than the columns. Many people relied on that default sort and were upset with the change. Oracle documentation has always stated that no order is guaranteed unless the SQL contains an ORDER BY clause. Even their documentation prior to 10 stated this.