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.

These rules came from SQL performance experts, more on that later.

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;
DEPT_ID from our subquery HAS to be a scalar, so adding DISTINCT isn’t necessary.

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);

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

5 Comments

  1. Saurabh Relia Reply

    Good article. Impressive profile.

    White paper must be interesting.

    Suppose codescan is another step towards automation .

    Curious how many is too many in IN

  2. 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.

  3. Amin Adatia Reply

    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?

Write A Comment