ODC Appreciation Day: SQL

October 10, 2017

oracle, SQL

Oracle Developer Community

Most of my work in the Oracle world has been DBA-oriented. But for the last years, I have rediscovered the joy of development and data analysis. When I haven’t been writing the code myself, I have often helped other developers when they connect to the Oracle database, and their program does not perform as expected.

Developers coding in Java, Python, or most other programming languages spend time on figuring out how to get the work done. They learn algorithms to discover the fastest way to do it, or the lazy coders just punch out code without wondering too much about efficiency. If their test data is small, performance problems usually get detected after release to production with ensuing frustrations.

If you have an idea but don’t want to worry too much about how to do it, then use SQL. You see, SQL is a declarative language. If you are capable of expressing what you want, then write it in SQL, and the algorithms behind will take care of the hard work.

SQL runs in the database with code much closer to your data then all the alternatives (which are different solutions running somewhere else). It is challenging to beat the performance of well-written SQL running in the Oracle database.

When you tell Oracle what you want, Oracle executes code that implements complex algorithms. What Oracle runs for you also runs a million of times around the world every day. Bugs do happen in software made by humans, but the probability that someone discovers them soon increases tremendously when millions of people use the same software instead of a comparatively small group.

Besides not losing data , getting the answer correct is the #1 priority for the Oracle database. (Getting wrong answers from a databank is just as useful as getting counterfeit bills from an ATM.) Oracle has put down a lot of effort to make sure this works as expected.

Writing good SQL can be an art. When I struggle to express a query in SQL, I ask myself if I have understood the problem. Once I have a grasp on the logic, the job is at least half done. Once the statement is written and sent for execution, the result comes back amazingly fast. Typically one answer brings another question, but the ad-hoc nature of this makes it easy to continue with many new enquires. No need to compile or rewrite half the code, just change a bit and learn from new results.

I use the same language when I help people with optimisation (often called tuning by them because they think there is a knob we forgot to move.) You see, the Oracle database has a lot of information about the data and what is going on in the database. The same SQL I use for reporting I use to investigate performance. I spend most of the time analysing what is going on, and little on how to implement code to get the answer I want.

So for this year’s appreciation day, I thank Oracle for SQL, and for keeping life interesting for data professionals around the world.