||Oracle Concepts by Burleson
Re-writing Subqueries for Faster Performance
As mentioned in Chapter 19, subqueries can often
be re-written to use a standard outer join, resulting in faster
performance. AS we may know, an outer join uses the plus sign (+)
operator to tell the Oracle database to return all non-matching rows
with NULL values. Hence we combine the outer join with a NULL test in
the WHERE clause to reproduce the result set without using a subquery.
b.book_key = s.book_key(+)
s.book_key IS NULL
We will compare the execution plans for these
types of queries in more detail in a later chapter, but for now, be
aware that we can often re-write subqueries to improve the speed of
the query and the resource demands on the Oracle database.
Next, let’s take a look at another class of
subqueries where we place a subquery inside the FROM clause of the
2 – In-line views (subqueries in the FROM clause)
With an in-line view, an SQL statements is
embedded inside the FROM clause of the SQL statement, just as if it
were a table name.
"Tablespace" for a13
MB" for 99,999,999
MB" for 99,999,999
MB" for 99,999,999
- fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
round(sum(bytes) / 1048576) TotalSpace
group by tablespace_name) df,
round(sum(bytes) / 1048576) FreeSpace
group by tablespace_name) fs
df.tablespace_name = fs.tablespace_name(+)
This is an SQL script that queries the data
dictionary to see the amount of free space within the database
tablespace in Oracle. We will be using this script in as a tool for
Oracle database administration.
Note that there are no table names in the FROM
clause. Instead of table names, there are two subqueries, one against
the dba_data_files view and another against the dba_free_space view.
These subqueries are given the aliases df and fs, and these aliases
are used in the SELECT clause to get the desired columns.
In-line views are a powerful component of SQL
because they allow us to combine several SQL queries into a single
To see how this works, copy the above code and
run it against your Oracle database. You should see output similar to
Tablespace Used MB Free MB Total MB Pct.
------------- ----------- ----------- -----------
6 14 20 70
8 12 20 60
EXAMPLE 153 0 153
0 25 25 100
SYSTEM 240 85 325
TOOLS 7 3 10
1 199 200 100 USERS 1 24 25
Next, let’s examine a new type of query that was
introduced in Oracle 10g called the scalar subquery. With a scalar
subquery, subqueries can be placed inside the SELECT statement.
Here is another example from the sample database.
This query returns each employee and their contribution to the total
company salary as a percent. The subquery returns the total company
While this approach is handy, this type query is
extremely inefficient because a Cartesian product is formed since the
subquery result must be joined with each row in emp.
sum(emp_salary) totsal from emp) t
Note that we can
get the same result much more efficiently by replacing the in-line
view with a temporary table:
sum(emp_salary) totsal from emp;
At this pint in the course it is not necessary to
fully appreciate the performance improvements of this re-write and
this topic will be covered extensively in a later tutorial. For now,
just remember that multiple queries with temporary tables will often
run faster than a single query with an in-line view.
3 - Scalar Subqueries (subqueries in the SELECT clause)
Scalar subqueries allow us to place individual
queries inside the SELECT clause, thereby combining many queries into
a single query.
In the example below, we compute the total sales
for all stores, the number of stores, the highest number of books sold
for any store, the least number of books sold for any store, and the
average number of books sold for all stores.
sum(quantity) from sales) tot_sales,
count(*) from store) nbr_stores,
max(quantity) from sales) most_sales,
min(quantity) from sales) least_sales,
avg(quantity) from sales) average_sales
NBR_STORES MOST_SALES LEAST_SALES AVERAGE_SALES
---------- ---------- ----------- -------------
110550 10 9900 10 1105.5
Using the dual table
Note that the
outer query references the dummy table called “dual” the dual table
is used in Oracle when you need to run SQL that does not logically
have a table name. For example, we can select our current user ID
from dual and the current date from dual.
user from dual;
sysdate from dual;
We can also use
the dual table for date arithmetic. This last query displays the day
of the week for any date in the past 1,000 years. To see the day of
the week that you were born, copy this query into your c”\Burleson
directory, add your birth date, and run the query see what day of the
week you were born on.
The SQL sum function is used whenever we want to
sum a series of values within the Oracle database. The basic format
of a summary SQL statement is as follows:
Note that a sum is most often used when we join
multiple tables together and we want to sum a numeric data column.
There is also a rule in SQL that all columns preceding the sum must be
included in the GROUP BY clause of the SQL statement.
Here is a simple example from your sample
database. Suppose that we want the names of all of the job
descriptions and the sum of all salaries for each job:
Here is the output from the sample database:
Just remember the simple rule when you perform a
sum: You must GROUP BY all columns except the column that you are
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA