Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: dba
Statistics on Object tables
Way back in Oracle 8.0 we introduced the “Object-Relational” database, which was “the next big thing” in the database community back then. Every vendor was scrambling to show just how cool their database technology was with the object-oriented programming paradigm. Don’t get me wrong…
DBMS_JOB is an asynchronous mechanism
One of the very cool things about DBMS_JOB is that a job does not “exist” as such until the session that submitted the job is commits the transaction. (This in my opinion is a critical feature that is missing from the DBMS_SCHEDULER package which,…
Patch conflicts
My last post was about patching my home databases from 18.3 to 18.5 on Windows, and how I encountered a patch conflict when I tried to patch the JVM. I thought I’d give a little bit of info for anyone who runs into patch…
From Database 18.3 to 18.5 (on Windows)
Contrary to wild rumours on the internet, it was not a fear of the number 13 that led to a numbering jump from version 12c to version 18c. The jump was part of our new, more flexible release mechanism so that we can get…
EXPORT not GATHER with DBMS_STATS
Just a short post today on something that came in as a question for the upcoming Office Hours session which I thought could be covered quickly in a blog post without needing a lot of additional discussion for which Office Hours is more suited…
Your New Years Resolution
Aligning roughly with the calendar year, based on the Chinese zodiak we’re about to go from the year of the dog to the year of the pig. But for me, in the “Information Technology Zodiak” , 2018 was the year of the hack, just…
Automatic sequences not being dropped
One of the nice new things in 12c was the concept of identity columns. In terms of the functionality they provide (an automatic number default) it is really no different from anything we’ve had for years in the database via sequences, but native support…
The phantom tablespace
(Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL? OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick I’ve created a…
18c and the ignoring of hints
One of the new features in 18c is the ability to ignore any optimizer hints in a session or across the entire database. A motivation for this feature is obviously our own Autonomous Data Warehouse, where we want to optimize queries without the…
Add ORDER BY to make ANY query faster
Yes it’s SCBT day here in Perth! SCBT = Silly Click Bait Title This post is just a cautionary tale that it is easy to get caught up judging SQL performance solely on a few metrics rather than taking a more common sense approach…
Interval versus Range partitions
One of the nice things about partitioning in the database is that partition pruning can quickly eliminate the requirement to read large amounts of data when the partitioning key(s) are appropriately provided in your queries (and DML). This also extends to queries where the…
18c XE–running locally
There’s already a few blog posts out there, showing people (easily) getting their 18c XE instance downloaded, installed and running, so I won’t rehash that here. For the majority of people, I’d imagine they’ll have their XE instance running on a small box and…
