Database Programming

Learning Database Programming the easy way

The importance of efficient SQL statements in an Oracle-based application cannot be downplayed; poorly written ones can cause havoc in the database. Because in many organizations power users access the production databases via reporting tools and direct queries, efficiently written SQL not only results in better application performance but also reduces traffic on the network. Therefore, power users as well as developers must have a good understanding of Oracle's optimizer and of tuning concepts that can make their statements more efficient and less risky. In this article, I'll summarize 25 of the most useful SQL tuning tips for making SQL statements run faster. Although some of these techniques have been previously described in Oracle manuals and various journals, many others have never been published and are appearing here for the first time.

Know your data and business application well

Identical information can often be retrieved from different business data sources. Familiarize yourself with these sources; you must be aware of the data volume and distribution in your database. Your should also have a thorough understanding of your data model--such as the relationships among business entities--before writing your SQL. This understanding will help you write much better queries for retrieving information from multiple tables. CASE tools such as Designer/2000 do a very good job of documenting relations among different business and database objects.

Test your queries with realistic data

Most organizations have three database environments: development, test, and production. Programmers use the development database environment to create and test applications, which are then more rigorously examined in the test environment by programmers and users before they are migrated to the production environment. When a SQL is being tested in the test environment, make sure the test database contains data that reflects the production database. A SQL statement tested with unrealistic data may behave differently when used in production. To ensure rigorous testing, the data distribution in the test environment must also closely resemble that in the production environment. Write identical SQL statements in your applications. Take full advantage of bind variables, stored procedures, and packages wherever possible. The benefits of identical SQL statements include reduced memory use on the database server and faster execution, as parsing is unnecessary.

For example, these statements are not identical

select * from employee where empid = 10;

SELECT * FROM EMPLOYEE WHERE EMPID = 10;

select * from employee where empid = 20;

but when using a bind variable named i_empid, the statement would be:

select * from employee where empid = :i_empid;