| |
 |
|
Scalar subqueries tips
Oracle Tips by Burleson Consulting
May 31, 2010
|
Scalar subqueries are a powerful
enhancement to Oracle9i SQL. They allow
for quick formulation of extremely complex
SQL statements. Oracle’s introduction of
scalar subquery support is another example
of the company’s commitment to keeping
pace with the evolution of the SQL
language.
The in-line View
(select inside the FROM clause)
Oracle has long supported the notion of an
“in-line view,” whereby a subquery can be placed in the FROM clause,
just as if it were a table name. There’s an Oracle query displaying
tablespace sizes:
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
col "Block Size" for 9,999,999
select
df.tablespace_name "Tablespace",
block_size "Block Size",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
dba_tablespaces ts,
(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
ts.tablespace_name = fs.tablespace_name
and
df.tablespace_name = fs.tablespace_name(+)
;
Here is the output from this
in-line view query against the data
dictionary.
Tablespace Block Size Used MB Free MB Total MB Pct. Free
------------- ---------- ----------- ----------- ----------- ----------
CWMLITE 4,096 6 14 20 70
DRSYS 4,096 8 12 20 60
EXAMPLE 4,096 153 0 153 0
INDX 4,096 0 25 25 100
SYSTEM 4,096 241 84 325 26
TOOLS 4,096 7 3 10 30
TS_16K 16,384 3 7 10 70
UNDOTBS 4,096 1 199 200 100
In the simple example, the
SQL subqueries are placed inside the FROM
clause and assigned the aliases of df and fs. The df and fs subquery values are then
referenced inside the SELECT clause. If
you examine this query, you’ll see that it
sums and compares two ranges of values
from two tables, all in a single query.
For some readers, seeing SQL inside the
FROM clause is probably quite strange, and
the scalar subquery is even stranger! The
scalar subquery is a take-off of the
in-line view whereby SQL subqueries can be
placed inside the SELECT clause. Let’s
take a look at a few examples.
Scalar subquery examples
Once you become acquainted with the
syntax, you’ll find scalar subqueries to
be very powerful. Scalar subqueries are
especially useful for combining multiple
queries into a single query. In Listing C,
we use scalar subqueries to compute
several different types of aggregations
(max and avg) all in the same SQL
statement. Note that this query uses both
scalar subqueries and in-line views.
select
(select max(salary) from emp) highest_salary,
emp_name employee_name,
(select avg(bonus) from commission) avg_comission,
dept_name
from
emp,
(select dept_name from dept where dept = ‘finance’);
Scalar
subqueries are also handy for inserting
into tables, based on values from other
tables. In Listing D, we use a scalar
subquery to compute the maximum credit for
BILL and insert this value into a
max_credit table.
insert into
max_credit
(
name,
max_credit
)
values
(
‘Bill’,
select max(credit) from credit_table where name = ‘BILL’
);
The scalar subquery in Listing D is quite
useful for Oracle data warehouse
applications. In an Oracle data warehouse,
it’s common for the DBA to pre-aggregate
values to speed up query execution, and
scalar subqueries are a powerful helper in
aggregation. In Listing E, we populate an
emp_salary_summary table with many types
of aggregate values from the base tables.
insert into
emp_salary_summary
(
sum_salaries
max_salary,
min_salary,
avg_salary,
values
(
(select sum(salary) from emp),
(select max(salary) from emp),
(select min(salary) from emp),
(select avg(salary) from emp)
;
Restrictions on
scalar subqueries
Scalar subqueries are restricted to
returning a single value because they
select a finite value. Scalar subqueries
could be used in previous versions of
Oracle in some parts of a SQL statement,
but Oracle9i extends their use to almost
any place where an expression can be used,
including:
- CASE expressions
- SELECT statements
- VALUES clauses of INSERT statements
- WHERE clauses
- ORDER BY clauses
- Parameters of a function
There are also important
restrictions on scalar
subqueries. Scalar subqueries
can’t be used for:
- Default values for
columns
- RETURNING clauses
- Hash expressions for
clusters
- Functional index
expressions
- CHECK constraints on
columns
- WHEN condition of
triggers
- GROUP BY and HAVING
clauses
- START WITH and CONNECT
BY clauses
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2011 by Burleson Enterprises
All rights reserved.
Oracle ©
is the registered trademark of Oracle Corporation.
|
|
|
|
|
