Is your SQL super fast on ‘your machine,’ but worried it might be less than awesome in production?
We have a new feature in Oracle SQLcl you can use to detect and prevent poor coding habits from making it into production.
This feature is optional, and it’s not on by default.
set codescan on
Once this is enabled, each time you run a SQL statement, your SQL will be parsed and flagged if it matches patterns as defined by the 12 rules listed in the Docs.
I figured we’d take a quick swing through some of the rules…
…but wait, who’s rules are these?
BI Publisher and Oracle Fusion
Business users can use things like BI Publisher and OBIEE to run some reports against their data from various Oracle FUSION apps. Those reports get turned into SQL, and there are patterns of bad behavior over the years that our support teams have identified.
They’ve broken down these patterns into rules that our Java parser can recognize. We’ve then taken these rules back from the Fusion folks and said, hey you’re using our parser, let’s use your rules.
You can read the nitty-gritty behind this if you have access to MOS and Doc ID 2800118.1.
And in THAT note is a link to a PDF Whitepaper that’s an easier read.
Now a few of the rules with examples. Note that my SQL to demonstrate these rules may or may not make sense. I’m generally trying to ‘trip the breaker’ so to speak.
1. Scalar Sub-Queries with DISTINCT or ROWNUM…
select first_name,
last_name,
(
select distinct department_id
from departments
where department_name = 'Purchasing'
) dept_id,
hire_Date
from employees
fetch first 10 rows only;
2. Function Calls in WHERE Clause Predicates
select *
from employees
where upper(last_name) = 'SMITH';
I’m personally guilty of this, A LOT, but I’m also dealing with very small (relatively) data sets.
3. Long in IN-LISTs in SQL Clauses
select *
from departments
where department_id not in
(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
5 Comments
Good article. Impressive profile.
White paper must be interesting.
Suppose codescan is another step towards automation .
Curious how many is too many in IN
10, I think.
Things are changing in 23c though where this isn’t the perf hit it used to be.
Wow. I’m guilty of some of these. Is there a guide on how to avoid them? where upper(last_name) = ‘SMITH’; is probably my favorite thing.
I tried the SQLcl v22.1 and I got a pop-up to say I needed to install Java 8
Well that’s not right. So you didn’t have any java on your machine, you’ve never used SQLcl before. You grabbed 22.1, and the EXE prompted you to get Java 8?
Can you show me?