Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: sql
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…
Take care with regular expressions
In an Office Hours session a couple of months back, I covered an important change that comes to regular expressions once you upgrade to 12c Release 2. You can see the video covering the issue here: but for the TL;DR brigade reading this post:…
Gooey GUIDs
Do a quick Google search and you’ll find plenty of blog posts about why GUIDs are superior to integers for a unique identifier, and of course, an equal number of posts about why integers are superior to GUIDs. In the Oracle world, most people…
From file names to directory hierarchy
I had a fun request come in from a colleague the other day. They had a simple list of fully qualified file names and they needed to present that data in the familiar hierarchical tree layout. To demonstrate, I took a little trip down…
Concurrency … the path to success and the path the failure
Let’s face it. Concurrency is a good thing when it comes to database applications. After all, if there is only a single user of your application, then chances are, it is not a successful application . Of course there are exceptions to this rule,…
Choose your names wisely
It all seems simple enough. You pick a name for an object…and that is the only object that have than name right ? For example: SQL> create table MY_UNIQUE_NAME ( x int ); Table created. SQL> SQL> create synonym MY_UNIQUE_NAME for MY_UNIQUE_NAME; create synonym…
NVL vs COALESCE
Jonathan Lewis just published a blog post about NVL and COALESCE and the optimizer costings for each. There is also perhaps a significant difference between NVL and COALESCE in that the former seems to have an in-built optimization for handling bind variables and nulls. …
Buzzword Bingo
Looking for that catchy title for your next presentation ? I took the first word from the title of 1000 Oracle OpenWorld presentations, and looked for patterns . I omitted some obvious terms that are either products or definite/indefinite articles: Oracle Peoplesoft The How …
AskTOM TV–episode 11
Just a quick note to give the supporting collateral to the latest episode of AskTOM TV. The question I tackled is this one: https://asktom.oracle.com/pls/apex/asktom.search?tag=want-to-retrive-numbers-in-words which was a fun one to answer because it showcases several useful SQL techniques: Using CONNECT to synthesize rows, Using…
Partition-wise join
So just what is a “partition-wise” join ? We will use a metaphor to hopefully explain the benefit. Let’s say two people, Logan and Shannon, decide to move in together. If each of them already have an existing residence, they will both have a…
Oracle Code … Not for database people ?
Jump over to the Oracle Code home page and you will see the “mission statement” of the Oracle Code conference series: “Learn from technical experts in sessions for developing software in Java, Node.js, and other languages and frameworks.” You might hence be thinking that…
