Problem solve Get help with specific problems with your technologies, process and projects.

Three checks to increase your SELECT statement execution productivity

Developers who use these three checks before executing SQL SELECT statements will increase their productivity.

There are three simple checks that all developers should do before executing any SQL SELECT statement. Performing these checks before submitting the statement will gain you a marked increase in your productivity. They will also allow you to avoid the most common errors that occur when writing a SELECT statement. Avoiding these errors will cause you to get your results sooner by eliminating the need to re-run statements. Finally, the checks will also help you avoid wasted query time caused by retrieving incorrect records.

The three checks are:

  1. Always count the number of right facing parentheses and the number of left facing parentheses within the SELECT statement.
  2. Always make sure that all regular expressions (non-group functions) in the SELECT clause are also listed in the GROUP BY clause.
  3. Always check to make sure that you have one less join condition in the WHERE clause than you have items listed in the FROM clause.

It is very common to use parentheses in a SELECT statement. They are used for a multitude of reasons. Some of these are:

  • To designate the proper order of operator execution in a calculation
  • To control the logic in a WHERE clause
  • To designate a sub-query
  • To hold the parameters for a function

It is very common to omit one of the parentheses when writing a SELECT statement. If you do not have the same number of left and right facing parentheses, your statement will not parse. It will save you some time if you count the parentheses before executing the query.

The second most common error for a developer is not to synchronize the expressions contained in the SELECT clause with the expressions in the GROUP BY clause. All expressions listed in the SELECT clause must also be listed in the GROUP BY clause.

Listing 1 (below) illustrates a SELECT statement with this problem. Notice that the Select clause contains the non-group columns fk_department, last_name, and first_name. After reviewing the clause, it appears that the developer intends to compute the total cost of tools for each employee.

However, the GROUP BY clause only lists fk_department. This clause is telling the database manager to compute the total cost of tools for each department. This query has two different groups and confuses the database manager. That is the reason the database manager issued the error message and stopped the parsing of the statement.

Mismatched groups are a very common error. Checking the two groups will help you execute your statements quicker by eliminating the re-runs caused by this error.

The last of the three errors is the most time consuming. The reason is that this error will not stop the parsing and execution of the statement. The result of the error is incorrect data, an excessive wait for statement results, and an overuse of the CPU. This error often results in the "Query From Hell" that uses up all of the CPU. It is called a Cartesian Join.

SELECT statements have the ability to take records from multiple tables and match them using common values. The matching logic consists of a JOIN statement in the WHERE clause. For example, in Listing 2 (below) "department = fk_department" is a JOIN condition. It matches records from the Department and Employee tables. If you forget the JOIN condition, each record in the first table will be joined to each record in the second table. This is a Cartesian join.

In Listing 2, the first SELECT statement produced a Cartesian join. Notice that 2018 virtual records were processed and the result was invalid information. This resulted in a waste of the developer's time because the SELECT statement needed to be re-run. A symptom of this type of query is the overlong processing time. A simple check using the rule "A query should have one less JOIN condition than the number of tables to be joined." The second query used this rule with proper results.

Drilling yourself on the three checks discussed in this tip will increase your productivity. Remember, a developer is similar to a baseball player. Practice the mechanics and the play will come naturally. Learn and practice these techniques and you will become a better developer.

Listing 1. A SELECT statement with SELECT and GROUP BY
clauses mismatched:

SQL> select fk_department, last_name, first_name, sum(tool_cost)
  2  from employee, emp_tools
  3  where payroll_number = fk_payroll_number
  4  group by fk_department;
select fk_department, last_name, first_name, sum(tool_cost)
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Listing 2. Cartesian and Non-Cartesian Select Statements:

SQL> select department_name, count(*)
  2  from department, employee, emp_tools
  3  group by department_name;

--------------- ---------
CENSUS DEPT           416
TRESURY DEPAR         416

SQL> select department_name, count(*)
  2  from department, employee, emp_tools
  3  where department = fk_department
  4  and payroll_number = fk_payroll_number
  5  group by department_name;

--------------- ---------

About the Author

John Palinski has been an Oracle developer and project manager for the last fourteen years. He developed one of the first relational database work management systems in the country. Mr. Palinski also teaches his own Oracle courses at Iowa Western Community College and the University of Nebraska at Omaha. Mr. Palinski is the author of the Oracle Database Construction Kit published by QUE and provides custom Oracle training worldwide through his consulting business, Realistic Software Training.

For More Information

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.