 |
 |
 |
 |
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
|
|
 |
|
|
|
 |
|
Oracle9i Scalar Subqueries
Oracle has long supported the notion of an “in-line view,” whereby a subquery SELECT can be placed in the FROM clause, just as if it were a table name.
Here is an Oracle query
commonly used by The DBA to display tablespace sizes.
Note that it uses SELECT statement in the FROM clause. This is
called an in-line view.
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(+)
;
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
USERS
4,096 1
24 25
96
Now in Oracle9i, you can add sub-queries directly into the SELECT clause. In this example, we select the MAX and AVG values for a table, right along with the detail rows:
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’)
;
You can also place sub-queries directly into the VALUES clause of an insert statement:
insert into
max_credit
(
name,
max_credit
)
values
(
‘Bill’,
select max(credit) from credit_table where name = ‘BILL’
);
Be aware the scalar subqueries have limitations. Scalar subqueries cannot 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
Scalar subqueries provide a powerful new tool within Oracle SQL. Their syntax is obtuse and sometimes hard to follow, but scalar subqueries can combine multiple queries into a single SQL unit, where they can be executed as a single unit. This greatly simplifies complex SQL computations. Scalar subqueries are especially useful for data warehouse applications and those types of databases requiring complex SQL queries.
If you like Oracle tuning, you might enjoy my
latest book “Oracle Tuning: The Definitive Reference” by Rampant
TechPress. It’s only $41.95 (I don’t think it is right to charge a
fortune for books!) and you can buy it right now at this link:
|
|
