The first version of Oracle I ever touched in any way was, I think, an installation of Oracle 6. The first version I actually did development on was Version 7 and thus beings my tale…
7
I had already been writing queries for a few years, so I was fairly comfortable with SQL when 7 came around, but being able to have a Stored Procedure… Wow! An actual “program” stored and executed inside the database! I even got to use arrays, I means Index-By tables, I mean PL/SQL Tables. 7 is when I learned the database wasn’t just a fancy filesystem. It was a data platform. Thanks to version 7, PL/SQL was lighting the way into a new path in my IT career.
8.0
When Version 8 first came out, my favorite feature, easily, was External Procedures. I had been a c programmer and had just recently changed jobs where I’d be doing more c, especially working with Oracle’s Pro*C. External Procedures came along and let me combine all my skills.
Over time though, the amount of Pro*C I used, or any c for that matter, started to decline. I became a VB programmer and later dabbled in dotNet, all the while still working with Oracle. Next I thought Index-Organized Tables were the coolest thing from 8; but as more time passed, I’d have to say Partitioning, instead of trying to use “partitioned views” has been one of the most significant and commonly used enhancements from 8 for me.
8i (8.1.5)
I might be asked to turn in my OCE in SQL if I didn’t say Analytic Functions were the best thing to come out of 8i and I do REALLY love them. It’s kind of a rare work day when I don’t have a reason to use them in some way – but I was a late adopter when 8i was new. I first saw them as awkward and clunky syntax. I’m so grateful (and I’m sure my customers have been too) that I finally opened my eyes to their power and elegance.
What I remember most about 8i was that was the version where the Cost-Based Optimizer finally came into its own. The CBO has existed for a few versions and several years already but in our experience it was usually better to go with RULE and only switch to CBO when forced to by using a new feature that the RBO didn’t support yet.
Sometimes the littlest things end up just being great. 8i added the CASE Statement to SQL. That tiny little feature made it possible to reorganize logic not just in functionality but in visual structure. Complex conditionals were not only easier to write but easier to read as well.
Of course I think Java, XML, VPD/RLS, Invoker rights and the whole “i” set of functionality built around TCP, most notably UTL_SMTP is awesome, but the features above were more significant to directly transforming how I worked.
9
If you listen carefully you might still be able to hear the echo of the choir of angels that heralded the coming of Oracle 9i. There hasn’t been a release before or since that I’ve looked forward to as much as I enjoyed digging into 9i when it first came out. It’s so hard to pick a favorite, but if I must I’ll go with the introduction of subfactoring, CTE, or the WITH clause, whatever name you prefer. Like the CASE of 8i, this little bit of syntax allowed for easier structuring of complicated SQL. This was another instance where I wasn’t just writing new code, I was writing new code in a new way.
ANSI-style joins and the MERGE statement also changed SQL . Pipelined functions came along here adding new ways to encapsulate business logic for security, performance, maintenance, or a combination of these. External tables allowed a new, efficient way to load data. 9i also gave us User-Defined Aggregates which are probably best known for being the engine behind STRAGG that I, and thousands of others, copied from Tom Kyte; or reinvented the wheel with a similar variant.
9i also came with a variety of tools, not just code. The Advisor views, DBMS_XPLAN and DBMS_METADATA were added to our toolbox. The venerable TKPROF got an upgrade in 9i by summarizing wait information from a trace file.
I had been working with Oracle’s original clustering architecture Oracle Parallel Server (OPS) for a few years and 9i replaced all that with Real Application Clusters (RAC.) RAC has received a lot of upgrades since it’s initial release but even in the beginning it was still offering us greater stability than OPS ever did.
10g
9i has such an explosion of features – and many of them were accessible in short order. 10g had some great stuff in it, but many of the better features took some effort to adopt. The Web Gateway and HTML DB (later APEX) gave a new way of connecting to and interfacing with the database. XQUERY expanded on XML functionality. Collection operations, distributed LOBS, conditional compilation added new features to our SQL and PL/SQL code. None of these really jumped out at a lot of developers I worked with, nor me either. My favorite new feature of 10g was Regular Expressions, allowing for compact description of complicated data patterns. I was already familiar with them from school as a mathematical tool. Like many others, I had refined the theoretical with practical application in the UNIX tool “grep”. So, while regexp coding can be arcane for the newcomer I welcomed it with open arms and jumped right in with them.
Here I’ll admit to being a late of adopter of another great feature – DBMS_SCHEDULER. I was happy with DBMS_JOB. It did what I needed so DBMS_SCHEDULER just seemed like more typing for little gain in functionality. I might have been an initial skeptic but I have since adopted it for the bulk of my scheduling needs. The only time I ever use DBMS_JOB anymore is when I need to make my job creation transactional. If DBMS_SCHEDULER came with a no-auto-commit option, I’d be content to never touch DBMS_JOB again.
10g also gave us the Instant Client and ASM (Automatic Storage Management.) Enterprise wide ASM adoption was transformational for our DBA team in how we could manage our servers, not to mention clustering.
11g
11g introduced a lot of new features; but for me it was mostly extensions to things I was already using – such as sub-expressions in the REGEXP functions and an increase in types of operations that could be done online. In day-to-day coding, the PIVOT clause was one of the winners for me. Around the time of 11g I was moving around in departments and responsibilities. While I did continue doing some development my roles were in flux so I was not digging into as many new features. I was spending more time helping with older systems and exploring other development tools and architectures. I also spent a fair amount of time working with databases other than Oracle in this period. The RESULT_CACHE showed a lot of promise and I wish I had been an early adopter of Edition Based Redefinition; but alas, I was not.
12c
My favorite feature is probably the most notable feature of 12c – Multi-tenant/Pluggable databases. The In-Memory option is another great new feature in 12c. It was sort of advertised almost as a cure-all for performance – which it is not. But… when you do have a proper use case for IM, it really can live up to its hype yielding 100x or 1000x performance gains. Both of those features have extra licensing costs associated with them which does put a damper on their adoption. On a “no-cost” coding front, I’m also a fan of User-Defined Functions; both as inline SQL definitions via the WITH clause as well as stored functions with the UDF pragma; but the former are my favorite. Of course, for reuse I’m all in favor of compiling a named object; but like CASE and WITH subfactoring before it, WITH-UDFs allows a new way to describe and present functionality within a query. There are many good reasons to encapsulate and hide rules within functions; but sometimes the most useful thing you can do for illustration and maintenance is co-locate and expose the logic you’re using.
Another really neat feature of 12c is pattern matching/MATCH_RECOGNIZE. Combining a little bit of analytic functions windowing syntax with a smidge of the MODEL clause’s structure and topped off with some regular expressions, pattern matching can be a little daunting to get started but you just can’t match it for functionality when you need to find and process inter-row patterns.
And last, another one of those little things that still means a lot- 12c (12.2 specifically) added support for Long Identifiers, that is, identifiers can be 128 characters now instead of limited to 30. 30 is more than enough most of the time but when it’s not, being forced to use awkward abbreviations or arbitrary numberings can be a painful exercise.
18c
When 18c new features first started to leak, I really wanted to believe polymorphic table functions were going to change the way I wrote code in immediate and grand ways but reality did not pan out that way. I have used them a couple times and I’m glad I have the option if I need to again; but they just haven’t been necessary frequently enough or impactful enough when I did try them to be a came changer for me.
The syntax of these next two features can grow to be cumbersome; but the fact that the functionality exists at all makes them two of my favorite features from 18c. ALTER TABLE MODIFY PARTITION enhancements are good enough on their own, but being able to do the changes ONLINE is simply phenomenal. Changing a partitioning strategy is not something that is done often; but when it is needed being able to do so as a single, atomic command – ONLINE – is great. Inline External Tables are a much enhanced next step from the 12c feature of inline modification of an external table. Having data in a database usually helps to make it clean and consistent in structure; but data outside of the db can be quite messy and unreliable. Being able to adjust import structure as part of a query helps smooth the bumps other applications put in the road.
I’m also glad Qualified Expressions for records and associative arrays are finally supported. They probably would have been a favorite feature if they came about in 9i or 10g, but at this point it feels more like relief from a painfully obvious functionality hole than satisfaction of something new and useful.
19c
19c being the terminal release of the 12.2 support family is mostly desirable for bug fixes but does introduce some nice changes. The LISTAGG function now supports the DISTINCT option. This is another one of those “why was this not here before” type of things; but we can rejoice now that its finally made it.
Another small thing but I think important – The oracle supplied accounts are now created as schema-only. Meaning it’s not possible to log in with them without first taking some other steps (like granting proxy access or changing them to have passwords.)
In the realm of autonomous actions – the SQL Quarantine is particularly intriguing. You might not have an immediate fix for some large, complicated query, but you can at least quarantine the plans that you do know are undesirable.
My favorite feature though isn’t really a 19c specific feature, but it is a change introduced during the 19c lifespan. I’ve already blogged about it previously: That is, two options that formerly required additional licensing now have no additional costs associated with them: “Oracle Spatial and Graph” and “Oracle Machine Learning/Advanced Analytics”.
There are, of course, many, many other features in each of these versions that I’ve tried and found useful, either as a developer or an administrator – or both. I’m sure there are features I haven’t used or did use but didn’t mention that have profoundly changed the work of others. My goal was not to be exhaustive or try to convince anyone that these were necessarily the most important or best features for everyone.
Rather, this was a bit of nostalgia, thinking back to where my career has been as well as where it’s going. If a reader has not explored one or more of these features, perhaps my mentioning it might inspire them to try something new.