It's here! Oracle Database 12c Release 2 (12.2) is available on Oracle Cloud and on-premises! With it comes a whole host of new features to help you write...
It's here! Oracle Database 12c Release 2 (12.2) is available on Oracle Cloud and on-premises! With it comes a whole host of new features to help you write better, faster applications. Here's my rundown of the top 12 new features to help you when developing against Oracle Database. Easier, Better, Faster, Stronger JSON JSON from SQL JSON in PL/SQL Looooooooooong Names Robust Code using Constants for Data Type Lengths Listagg Improved On Overflow Lightning Fast SQL with Real...
It's here! Oracle Database 12c Release 2 (12.2) is available on Oracle Cloud and on-premises! With it comes a whole host of new features to help you write better, faster applications. Here's...
When you’re trying to get SQL to run faster there’s one thing that’s vital: an execution plan. In this post we’ll investigate four ways to create these:...
When you’re trying to get SQL to run faster there’s one thing that’s vital: an execution plan. In this post we’ll investigate four ways to create these: Autotrace SQL Monitor TKPROF DBMS_XPlan But before we begin let's answer the question: What is an Execution Plan? The plan for a SQL statement is a set of instructions. This tells the database how to access the data and join it together. Plans come in two varieties: Explain Execution These sound similar. But they’re...
When you’re trying to get SQL to run faster there’s one thing that’s vital: an execution plan. In this post we’ll investigate four ways to create these: Autotrace SQL Monitor TKPROF DBMS_XPlan But...
There are some things in life it's great to have spare copy of. Your keys, your kidneys and your backups to name a few. Image by Ryan McGuire But there are some...
There are some things in life it's great to have spare copy of. Your keys, your kidneys and your backups to name a few. Image by Ryan McGuire But there are some things you don't want duplicated. Rows in your database tables are a perfect example. So it's unsurprising a common question people working with relational databases have is: "How do I find duplicate rows using SQL?" This is often closely followed with: "How do I delete all but one of the copies?"
There are some things in life it's great to have spare copy of. Your keys, your kidneys and your backups to name a few. Image by Ryan McGuire But there are some things you don't want duplicated. Rows...
Imagine you have a table with a series of transactions. These are classified into credits or debits: TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT --------...
Imagine you have a table with a series of transactions. These are classified into credits or debits: TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT -------- ----------------- ---------- -------- 6 01-JAN-2015 00:00 CREDIT 40 7 01-JAN-2015 00:00 CREDIT 90 8 01-JAN-2015 00:00 DEBIT 80 5 02-JAN-2015 00:00 CREDIT 200 4 03-JAN-2015 00:00 DEBIT 99 3 04-JAN-2015 00:00 CREDIT 50 2 05-JAN-2015 00:00 DEBIT 200 1 06-JAN-2015 00:00 DEBIT 100 Your boss has asked you to do the following: "I...
Imagine you have a table with a series of transactions. These are classified into credits or debits: TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT -------- ----------------- ---------- --------...
To wrap up this series, let's take a look at Stelios' winning entry in the Obfuscated SQL Contest! Vastly simplifying, this solution generates the latitude and...
To wrap up this series, let's take a look at Stelios' winning entry in the Obfuscated SQL Contest! Vastly simplifying, this solution generates the latitude and longitude co-ordinates of each location. These are then passed as parameters in a HTTP request to http://ws.geonames.org/, in the process using more hex conversions in one statement than I've seen in my entire career! This call returns an XML document including the time zone name for each place. Stelios then uses...
To wrap up this series, let's take a look at Stelios' winning entry in the Obfuscated SQL Contest! Vastly simplifying, this solution generates the latitude and longitude co-ordinates of each...
Thanks to everyone who entered the Obfuscated SQL Contest! This ran on the PL/SQL Challenge from March - April. It asked players to produce ingenious,...
Thanks to everyone who entered the Obfuscated SQL Contest! This ran on the PL/SQL Challenge from March - April. It asked players to produce ingenious, creative, quirky and downright weird SQL to return the current time in the timezones Bangalore, London, New York, Chicago and Denver. There were 53 entries, many of which provoked serious head scratching amongst the judges to determine how these queries actually work! After much deliberating in an exceptionally close run...
Thanks to everyone who entered the Obfuscated SQL Contest! This ran on the PL/SQL Challenge from March - April. It asked players to produce ingenious, creative, quirky and downright weird SQL to...
