The following is the fifth part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization. Each tip is excerpted from the not-yet-released Rampant TechPress book, "Oracle SQL and index internals," by Kimberly Floss. Check back to the main series page for upcoming installments.
Methods for creating high-quality database code
"Jeff" was a really nice guy but an absolute bare-boned novice at writing SQL code. So what does a company with a small IT staff do with someone like Jeff? Put him in a group of SQL developers who are charged with the task of writing decision support queries against a mammoth Teradata warehouse, of course! One day Jeff asked me to look at a query he had written against the warehouse, which really was an absolute monster. If you have never used Teradata, let me say that it is a true database junkie's dream come true. Infinitely scalable architecture, massive parallel processors, tons of RAM, and fast hashing algorithms used to distribute the tons of data among the many storage devices. Teradata also has a unique EXPLAIN plan that not only communicates the paths used to obtain a SQL result set, but also gives a time estimate of how long it believes the query will take to run. I asked Jeff if he had run his query through the EXPLAIN utility, and he said that he had not. So without reviewing his query, I sat down with him and put it through an EXPLAIN. Teradata went through its computations and issued its information back. My eyes widened as I focused on the time estimate. The year all this took place was 1993. Teradata estimated that Jeff's query would finish in the year 2049.
The story above is completely true and underscores how damaging and dangerous SQL can be in untrained hands. And, unfortunately, there are not only a lot of SQL novices out in IT shops right now, but their number is growing. At the same time corporations are increasing the number of projects that require database support, the number of seasoned database professionals available to do the work is shrinking. Faced with a terrible shortage of qualified database personnel, companies are throwing employees like Jeff into the meat grinder and are expecting them to produce systems that meet or exceed the difficult expectations of end users.
Writing high-quality SQL code isn't easy for a seasoned professional to do, let alone a rookie. But if you follow a judicious set of guidelines and work within a solid code framework that enforces certain standards, you stand a much higher chance of producing the types of response times needed for today's systems. This chapter won't teach you how to write optimized SQL code — books numbering into the hundreds of pages have attempted to do that (with some succeeding and many others failing). But what this chapter will do is offer a set of guidelines or checklist to follow when writing and testing SQL to ensure the best possible outcome. By adhering to what follows, you might just avoid a query like Jeff's that would still be running right now if we hadn't put the kibosh on it.
Start with the basics
I am astounded at the number of developers who just paste SQL ad hoc into an application without first seeing if it is actually syntactically correct. Web folks are the worst at this because most of the tools they use do not provide a parser to check SQL code, so they just code or paste some SQL into their controls and hope for the best. Obviously, this is not the best way to work because syntax problems will come back to bite you continuously and your productivity will begin a downward slide.
Ideally, you want to do your work in an integrated development environment that offers the ability to quickly check the validity of — in other words, parse — your SQL code. Parsing validates security access to the underlying objects, ensures that object definition names or references are correct and in order, and confirms that the underlying syntax is valid and free of errors. This may seem terribly basic, but SQL validation is the first checkbox to mark in your SQL perfection checklist.
Go to the main series page.
About the author
Kimberly Floss is one of the most-respected Oracle database administrators in the U.S., and is president of the International Oracle Users Group (IOUG). With more than a decade of experience, Kimberly specializes in Oracle performance tuning and is a respected expert in SQL tuning techniques. She is an active member of the Chicago Oracle Users Group, and the Midwest Oracle Users Group, in addition to the IOUG. Kimberly Floss has over 15 years of experience in the information technology industry, with specific focus on relational database technology, including Oracle, DB2, Microsoft SQL Server and Sybase. She holds a bachelor's of science degree in computer information systems from Purdue University, specializing in systems analysis and design, and has an MBA with emphasis in management information systems from Loyola University.
