Donald K. Burleson Oracle Tips

Oracle 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:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.