Scalar subqueries tips for Oracle 9i

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.

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.

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.

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.

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

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)

;

insert into

max_credit

(

name,

max_credit

)

values

(

‘Bill’,

select max(credit) from credit_table where name = ‘BILL’

);

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’);

Tablespace Block Size Used MB Free MB Total MB Pct. Free 100

------------- ---------- ----------- ----------- ----------- ----------

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

Tags: database design, Oracle SQL Server, query performance, sql programming, sql tips, tutorials

Comment on "Scalar subqueries tips for Oracle 9i"

Related News: