What simple guidelines would you give your developers for writing good SQL against Oracle?

Question

I work in a group of about 25 developers. I'm responsible for coming up with the database design (tables, views, etc) and am called apon for performance tuning when necessary.

There are a couple of different applications that connect. Database access is via JDBC, hibernate, and iBatis SQL maps. Developers with various levels of experience write SQL statements.

What guidelines would you give to developers to write good SQL?

By good I mean: correct, performs well, easy to understand and maintain.

These are just meant to be easy to follow guidelines - I want to get people onto the right track for the majority of situations. We will break these guidelines when it makes sense.

EDIT: We have in place code reviews for all source commits (SQL, java, etc) enforced through a jira workflow.

Answer 1

• Don't write SQL if you can help it, use HQL (or JPQL is on JEE) whenever possible
• Don't use SELECT *
• Pick your internet sources wisely (e.g. asktom.oracle.com)
• Don't use cursors
• Don't do string concatenation in SQL
• Write queries such that they use indexes (fundamentally this means base WHERE predicates on the indexes that exist)
• use MERGE instead of other awkward 'upsert' type logic
• When working with dates, make sure you understand how they're stored in Oracle vs. how they are stored in Java, especially when it relates to TimeZone. Depending on the Calendar/Date types, this information can be stripped out, remapped to the TZ of the default locale, etc.
• Most importantly: Don't use the excuse of being a developer for not knowing how to write good SQL, and how the database works. You don't have to be a DBA, but you need to invest in your own training to make yourself suitable for the task. By the same token, your company needs to invest in that as well.

I don't mean to say that these "Don'ts" always apply. It's just that, if you're talking about a developer who is not comfortable with Oracle, they need to know what they're doing before they start deciding whether those types of things are necessary and appropriate.

Answer 2

If you have 25 developers writing SQL queries against your database you are in quite a bit of trouble. Guidelines are not worth much when your junior developers are learning SQL and checking in a mess.

I would like to offer 4 recommendations

1. Use an ORM of sorts so your all your devs write less SQL.
2. Invest in training, buy books, send people to courses.
3. Have all the SQL reviewed by the senior SQL developers, by all, I mean every SQL statement, no exceptions. This way your senior guys will be able to teach the juniors over time.
4. Have a single person, who lives and breaths Oracle, responsible for the database. By responsible I mean knows every query, understands all the structure and is able to give expert advice.

Here are some additional things you may add to your existing guidelines/checklist.

• Have you tested your queries on a large data set? How was performance?
• Have you performed a quick index review on the tables that are being accessed? Are all the right indexes in place? Do you recommend and new indexes?
• For high volume queries, are any covering indexes required?
• Are you using "NOT IN" in cases where a "LEFT JOIN" should be used?
• Is your work transactionally sound? Are you missing a transaction somewhere?

Answer 3

Introduce basic style guide that covers:

• naming (of everything - tables, columns, procedures, aliases, ...) .
• formatting style
  • line width
  • what reserved words require new line (e.g where)
  • are reserved word capitalized or small caps
  • indenting
  • ...

Here are some examples:

• Oracle PL/SQL Programming, Fourth Edition. There is older, 2nd edition - available online
• SQL and PL/SQL Coding Standards

Be very strict about naming, it will be easier for you to read other people's code.
As formatting is concerned, there are tools available that can format automatically, so maybe you don't need very detailed description here.

Answer 4

An hour long presentation on some Oracle fundamentals (eg parsing, SGA vs PGA). "Do this" rules may or may not apply to your situation. Give them an understanding of what the DB side does, and they at least have a basis on which to make a decision. Plus Code reviews.

Answer 5

Here's what I already have in my guidelines.

• Work in sets, not row by row
• The best way to make something go quicker is to avoid doing work you do not have to do
• Databases love to join
• Fully qualify and specify column names (so SQL does not break when additional columns are added)
• Select only the data you need (never select *, never more rows than you require, never every column just becaues it's there)
• How to use rownum to limit resultsets
• Bind Variables vs Literals (use bind variables in all but a few special cases related to skewed data)
• Avoid functions or calculations on columns in the WHERE clause (except for a special case of function based index)
• Use ORDER BY for all queries returning more than one row (this is mostly for testability)

Each of these points is expanded a bit in the actual guidelines I've written out with an example relevant to our database schema.

Answer 6

Along with the recommendation to have queries reviewed by senior programmers, if you can get the buy-in, have code reviews which involve as many team members as possible.

Answer 7

Read Tom Kyte's books. He explains how you can write fast code and how you can measure performance and scalability. If you have a problem you can probably find the answer on the "ask tom"-site.

Answer 8

• If you are a database developer, you need to know what an EXECUTION PLAN is. If you don't then go mine coal or something.
• Before developing:
  • first, you think what best EXECUTION PLAN will be,
  • second you create tables and indexes, and
  • third you use hints to persuade the optimizer to come out with the plan you made.
• You do use hints. Forget automatic optimization, it's a marketing myth. No optimizer knows your data better than you and never will.
• There are no "programmers who create queries" and "system administrators who create indexes". Programmers program, system administrators make backups (or whatever they make).
• Triggers are evil.
• Prefix you columns, tables and views (SELECT prs_name FROM t_person)
• Make lines and indent

Answer 9

Pair-program. Any advantange it provides for agile development in general, at least doubles for SQL development.

Second choice, code reviews for all SQL.

Answer 10

I'm by no means a guru but here are my tips:

• Don't use ORDER BY unless you really need an ordered list as it incurs a performance hit.
• Understand the explain plan and also recognise that the plan on your development environment is often different from your production environment. Don't expect it to accurately reflect real life performance
• The pros of using hints is that you get to choose your explain plan, the cons of using hints is that the optimal plan may change over time and you might be choosing a plan that is suboptimal in the long term
• Make sure the developers know when to use INNER JOIN, OUTER JOIN, [NOT] IN, [NOT] EXISTS - you can put in place a lot of processes but one or two Cartesian products will bring production performance to its knees
• Ensure your developers understand indexes - what they are, when they should be used, when they should be avoided
• Have a DBA monitor the most executed queries and the most expensive queries and highlight these as candidates for optimisation
• Peer review
• Coding standards (especially code comments on particularly long/complex queries)
• Unit testing

Answer 11

1. Send as many of the developers out for as much Oracle training as you can get away with (or have a trainer come in house, it may be cheaper).
2. Hire a Data Architect.
3. Find something to numb the pain until 1. and 2. kick in. Share it in large quantities with the DBAs.

Seriously, don't underestimate the return on investment from investing in training. If you do nothing else, do that. You'll better off if the developers understand the database enough to write their own guidelines. I don't mean to disparage the value of advice you'll get from here, but we haven't seen your code, your schema or used your application, so it will be hard to give advice that is relevant to your situation. On top of that, most of it is likely to be anecdotal.