|
Question: What is
an Oracle "in-line view? How do I
use an inline view?
Answer:
The
inline view is a construct in Oracle SQL
where you can place a query in the SQL
FROM, clause, just as if the query was a
table name.
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:
A
common use for in-line views in Oracle SQL
is to simplify complex queries by removing
join operations and condensing several
separate queries into a single query.
The
best example of the in-line view is the
common Oracle DBA script that is used to
show the amount of free space and used
space within all Oracle tablespaces. Let's
take a close look at this SQL to see how
it works. Carefully note that the FROM
clause in this SQL query specifies two
sub-queries that perform summations and
grouping from two views, dba_data_files,
and dba_free_space.
In
ANSI standard SQL, it is quite difficult
to compare two result sets that are summed
together in a single query, and this is a
common problem with Oracle SQL where
specific values must be compared to a
summary. Without the use of an
in-line view, several separate SQL queries
would need to be written, one to compute
the sums from each view and another to
compare the intermediate result sets.
This
is a great report for display the actual
amount of free space within an Oracle
tablespace.
column
"Tablespace" format a13
column "Used MB" format
99,999,999
column "Free MB" format
99,999,999
colimn "Total MB" format
99,999,999
select
fs.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
SQL quickly compares the sum of the total
space within each tablespace to the sum of
the free space within each tablespace.
Here is a sample of the output:
Basically, this query needs to compare the
sum of total space within each tablespace
with the sum of the free space within each
tablespace.
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.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
|
