 |
|
Oracle Concepts by Burleson
Consulting |
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.
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 Oracle 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 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
statement.
To see how this works, copy the above code and
run it against your Oracle 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 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
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
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.
select
user from dual;
USER
------------------------------
PUBS
select
sysdate from dual;
SYSDATE
---------
27-MAY-02
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.
select
to_char(to_date('25-MAR-1956','dd-mon-yyyy'),'day')
from dual;
TO_CHAR(T
---------
sunday
SQL Aggregation
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:
select
column1,
sum(column2)
from
table1,
table2
where
table1.key =
table2.key
group by
column1;
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:
select
job_name,
sum(emp_salary)
from
emp,
job
where
emp.job_key =
job.job_key
group by
job_name
;
Here is the output from the sample database:
JOB_NAME
SUM(EMP_SALARY)
--------------------------------------------------
---------------
Editor
108000
Manager
190666
Marketer
73000
Salesperson
130000
Just remember the simple rule when you perform a
sum: You must GROUP BY all columns except the column that you are
summing.
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:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
