| |
 |
|
Oracle SQL: Re-writing Oracle subqueries for faster performance
Oracle Tips by Burleson Consulting
|
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
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.
select
b.book_key
from
book b,
sales s
where
b.book_key = s.book_key(+)
and
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 database.
Next, let’s take a look at another class of subqueries where we
place a subquery inside the FROM clause of the outer query.
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.
col "Tablespace" for a13
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select
df.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from dba_free_space
group by tablespace_name) fs
where
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 IT482
as a tool for 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 statement.
To see how this works, copy the above code and run it against your
database. You should see output similar to this:
SQL> @tsfree
Tablespace Used MB Free MB Total MB Pct. Free
------------- ----------- ----------- ----------- ----------
CWMLITE 6 14 20 70
DRSYS 8 12 20 60
EXAMPLE 153 0 153 0
INDX 0 25 25 100
SYSTEM 240 85 325 26
TOOLS 7 3 10 30
UNDOTBS 1 199 200 100
USERS 1 24 25 96
Next, let’s examine a new type of
query that was introduced in Oracle9i called the scalar subquery.
With a scalar subquery, subqueries can be placed inside the SELECT
statement.
Here is another example from the pubs database. This query returns
each employee and their contribution to the total company salary as
a percent. The subquery returns the total company salary.
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.
select
e.emp_last_name,
to_char(round((emp_salary/t.totsal)*100,1),'99.9')||'%'
from
emp e,
(select sum(emp_salary) totsal from emp) t
;
EMP_LAST_NAME TO_CHA
------------------------------ ------
king 18.9%
jackson 7.0%
korn 5.6%
linus 9.0%
tokheim 12.6%
levender 2.8%
johnson 6.2%
baker 10.2%
coleman 14.6%
brannigan 13.3%
Note that we can get the same result much more efficiently by
replacing the in-line view with a temporary table:
create table t1 as
select sum(emp_salary) totsal from emp;
select
e.emp_last_name,
to_char(round((emp_salary/t.totsal)*100,1),'99.9')||'%'
from
emp e,
t1 t
;
EMP_LAST_NAME TO_CHA
------------------------------ ------
king 18.9%
jackson 7.0%
korn 5.6%
linus 9.0%
tokheim 12.6%
levender 2.8%
johnson 6.2%
baker 10.2%
coleman 14.6%
brannigan 13.3%
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.
select
(select sum(quantity) from sales) tot_sales,
(select count(*) from store) nbr_stores,
(select max(quantity) from sales) most_sales,
(select min(quantity) from sales) least_sales,
(select avg(quantity) from sales) average_sales
from
dual;
TOT_SALES NBR_STORES MOST_SALES LEAST_SALES AVERAGE_SALES
---------- ---------- ---------- ----------- -------------
110550 10 9900 10 1105.5
Note: These exercises may use the
pubsdb.sql script that can be
downloaded at this link.
 |
|
Need Oracle training?
- Get Oracle training from a practicing Oracle
expert
- Get custom training designed to
fit your needs
- Conveniently offered at your
workplace, anywhere in the USA
BC Oracle training offers some of the
USA's most respected Oracle experts and authors. Why spend
thousands on cookie cutter Oracle classes when you can have the
personalized attention of a real Oracle
guru? Just call now: |
|
|
