Select Page

Consider Table Expressions to Improve Performance

Author: Craig Mullins | 2 min read | December 23, 2015

Table expressions are frequently overlooked as a potential solution for resolving problems using only SQL. Table expressions can be used to force the optimizer to choose a specific processing order.

For example, consider the following query:

SELECT D.DEPTNO,
MIN(D.DEPTNAME) AS DEPT_NAME,
MIN(D.LOCATION) AS DEPT_LOCATION,
SUM(E.SALARY) AS TOTAL_SALARY
FROM   DEPT D,
EMP E
WHERE D.DEPTNO = E.WORKDEPT
AND   E.BONUS BETWEEN 0.00 AND 1000.00
GROUP BY D.DEPTNO;

In this query, the detail rows that qualify from each table are joined prior to the GROUP BY processing. In general, there will be more EMP rows than DEPT rows because a department comprises multiple employees. Suppose there were 200 DEPT rows joined to 75,000 EMP rows. The join is done and then the GROUP BY is processed.

Instead, you can use table expressions to force the optimizer to process the aggregations on a table-by-table basis:

SELECT D.DEPTNO,
D.DEPTNAME,
D.LOCATION,
E.TOTAL_SALARY
FROM   DEPT D,
(SELECT WORKDEPT, SUM(SALARY) AS TOTAL_SALARY
FROM EMP E
WHERE E.BONUS BETWEEN 0.00 and 1000.00
GROUP BY E.WORKDEPT) AS E
WHERE D.DEPTNO = E.WORKDEPT;

This will produce the same results but it should perform better.

Instead of coding the table expression in the FROM clause, you can code a WITH clause and refer to the expression by a name. This allows you to reference the expression multiple times in the query without having to type the entire expression each time. Here is a re-working of the previous query using this method:

WITH CEMP (WORKDEPT, TOTAL_SALARY) AS
(
SELECT WORKDEPT, SUM(SALARY)
FROM EMP
WHERE E.BONUS BETWEEN 0.00 and 1000.00
GROUP BY E.WORKDEPT
)
SELECT D.DEPTNO,
D.DEPTNAME,
D.LOCATION,
E.TOTAL_SALARY
FROM   DEPT D,
CEMP E
WHERE D.DEPTNO = E.WORKDEPT;

This is referred to as a common table expression, or CTE.

In general, consider using table expressions to pre-filter FULL JOIN tables, to pre-filter null supplying tables of LEFT/RIGHT joins, to separate GROUP BY work, and to generate or derive data.

This blog was originally published on Craig Mullins’ blog at: http://db2portal.blogspot.com/2008/04/consider-table-expressions-to-improve.html

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

PostgreSQL vs. Oracle: Let’s Compare

Discover some key differences between PostgreSQL vs Oracle that can help you make an informed decision when deciding on a database management system.

Vijay Muthu | March 30, 2021

Oracle RMAN Backup and Recovery with Restore Points

Oracle restore points are useful for benchmark testing. Find out how you can use Oracle’s Recovery Manager (RMAN) tool to create and use restore points.

Cindy Putnam | May 3, 2019

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS