5.4 Inline Views

Most texts
covering SQL define the FROM clause of a
SELECT statement as containing a list of tables and/or views. Please
abandon this definition and replace it with the following:

The FROM clause contains a list of data sets.

In this light, it is easy to see how the FROM clause can contain
tables (permanent data sets), views (virtual data sets), and SELECT
statements (temporary data sets). SELECT statements, or inline views
as mentioned earlier, are one of the most powerful, yet underutilized
features of Oracle SQL.

In our opinion, the name "inline
view" is confusing and tends to intimidate people.
Since it is a subquery that executes prior to the containing query, a
more palatable name might have been
"pre-query."

In this example, the FROM clause references the
department table and an inline view called
emp_cnt, which calculates the number of employees
in each department. The two sets are joined using
dept_id and the ID, name, and employee count are
returned for each department. While this example is fairly simple,
inline views allow you to do things in a single query that might
otherwise require multiple select statements or a procedural language
to accomplish.

5.4.1 Inline View Basics

Because the
result set from an inline view is
referenced by other elements of the containing query, you must give
your inline view a name and provide
aliases for all ambiguous columns. In
the previous example, the inline view was given the name
"emp_cnt", and the alias
"tot" was assigned to the
COUNT(*) column. Similar to other types of
subqueries, inline views may join multiple tables, call built-in and
user-defined functions, specify optimizer hints, and include GROUP
BY, HAVING, and CONNECT BY clauses. Unlike other types of subqueries,
an inline view may also contain an ORDER BY clause, which opens
several interesting possibilities (see Section 5.5 later in the chapter for an
example using ORDER BY in a subquery).

Inline views are particularly useful when you need to combine data at
different levels of aggregation. In the previous example, we needed
to retrieve all rows from the department table and
include aggregated data from the employee table,
so we chose to do the aggregation within an inline view and join the
results to the department table. Anyone involved
in report generation or data warehouse extraction, transformation,
and load (ETL) applications has doubtless encountered situations
where data from various levels of aggregation needs to be combined;
with inline views, you should be able to produce the desired results
in a single SQL statement rather than having to break the logic into
multiple pieces or write code in a procedural language.

When considering using an inline view, ask yourself the following
questions:

What value does the inline view add to the readability and, more
importantly, the performance of the containing query?

How large will the result set generated by the inline view be?

How often, if ever, will I have need of this particular data set?

Generally, using an inline view should enhance the readability and
performance of the query, and it should generate a manageable data
set that is of no value to other statements or sessions; otherwise,
you may want to consider building a permanent or temporary table so
that you can share the data between sessions and build additional
indexes as needed.

5.4.2 Query Execution

Inline views
are always
executed prior to the containing query and, thus, may not reference
columns from other tables or inline views from the same query. After
execution, the containing query interacts with an inline view as if
it were an unindexed, in-memory table. If inline views are nested,
the innermost inline view is executed first, followed by the
next-innermost inline view, and so on. Consider the following query:

If you're new to inline views, this query might be
intimidating. Start with the innermost query, understand the result
set generated by that query, and move outward to the next level.
Since inline views must be noncorrelated, you can run each inline
view's SELECT statement individually and look at the
results. (From the standpoint of the inline view, this would
constitute an "out-of-query
experience.") For the previous query, executing the
emp_orders inline view generates the following
result set:

The emp_orders set contains all salespeople who
booked orders since 2001, along with the total number of orders
booked. The next level up is the dept_orders
inline view, which joins the emp_orders data set
to the employee table and aggregates the number of
orders up to the department level. The resulting data set looks as
follows:

After query execution completes, the emp_orders
and dept_orders result sets are discarded.

5.4.3 Data Set Fabrication

Along
with
querying existing tables, inline views may be used to fabricate
special-purpose data sets that don't exist in the
database. For example, you might want to aggregate orders over the
past year by small, medium, and large orders, but the concept of
order sizes may not have been defined in your database. You could
build a table with three records to define the different sizes and
their boundaries, but you only need this information for a single
query, and you don't want to clutter the database
with dozens of small, special-purpose tables. One solution is to use
the UNION set operator to combine individual
sets of data into a single set. (Set operators will be covered in
detail in Chapter 7.) For example:

One word of caution: when constructing a set of
ranges, make sure there are no gaps
through which data may slip. For example, an order totaling $29.50
would not appear in either the small or medium categories, since
$29.50 is neither between $0 and $29 nor between $30 and $79. One
solution is to overlap the region boundaries so that there is no gap
through which data can slip. Note that you can no longer use BETWEEN
with this approach:

Now that you have neither an overlap nor a gap between the buckets,
you can be sure that no data will be left out of the aggregations.

Fabricated data sets can also be useful for
determining what data is not stored in a
database. For example, your manager might ask for a report listing
the aggregate sales for each day of the year 2001, including days
with no sales. Although the cust_order table
contains records for every day that had orders, there is no table in
the database containing a record for every day of the year. To
provide your manager with an answer, you will need to fabricate a
driving table containing a record for every day in 2001, and then
outer join it to the set of aggregated sales for the same period.

Since a year contains either 365 or 366 days, we will build the set
{0, 1, 2, ..., 399}, add each member of the set to the start date of
01-JAN-2001, and let Oracle throw away the rows that
don't belong in 2001. To build the set {0, 1, 2,
..., 399}, we will create the sets {0, 1, 2, ..., 10}, {0, 10, 20,
30, ..., 90}, and {0, 100, 200, 300} and add members of the three
sets across the Cartesian product:

Since this query has no join conditions, every combination of the
rows in the ones, tens, and
hundreds sets will be generated, and the sum of
the three numbers in each row will produce the set {0, 1, 2, ...,
399}. The next query generates the set of days in 2001 by adding each
number in the set to the base date and then discarding days that fall
in 2002:

Since 2001 is not a leap year, the result set will contain 365 rows,
one for each day of 2001. This query can then be wrapped in another
inline view and used as the driving table for generating the report.
Whether you would actually want to use such a strategy in your code
is up to you;
the main purpose of this example is
to help get the creative juices
flowing.

5.4.4 Overcoming SQL Restrictions

The use of certain features of Oracle SQL can
impose restrictions on our SQL statements. When these features are
isolated from the rest of the query inside an inline view, however,
these restrictions can be sidestepped. This section explores how
inline views can overcome limitations with hierarchical and
aggregation queries.

5.4.4.1 Hierarchical queries

Hierarchical
queries
allow recursive
relationships
to be traversed. As an
example of a recursive relationship, consider a table called
region that holds data about sales territories.
Regions are arranged in a hierarchy, and each record in the
region table references the region in which it is
contained, as illustrated by the following data:

Each record in the customer table references the
smallest of its applicable regions. Given a particular region, it is
possible to construct a query that traverses up or down the hierarchy
by utilizing the START WITH and
CONNECT BY
clauses:

The query just shown traverses the region hierarchy starting with the
North America region and working down the tree. Looking carefully at
the results, you can see that the Canada, United States, and Mexico
regions all point to the North America region via the
super_region_id field. The remainder of the rows
all point to the United States region. Thus, we have identified a
three-level hierarchy with one node at the top, three nodes in the
second level, and six nodes in the third level underneath the United
States node. For a detailed look at hierarchical queries, see Chapter 8.

Imagine that you have been asked to generate a report showing total
sales in 2001 for each subregion of North America. However,
hierarchical queries have the restriction that the table being
traversed cannot be joined to other tables within the same query, so
it might seem impossible to generate the report from a single query.
Using an inline view, however, you can isolate the hierarchical query
on the region table from the
customer and cust_order tables,
as in:

Even though the na_regions set includes the North
America and United States regions, customer records always point to
the smallest applicable region, which is why these particular regions
do not show up in the final result set.

By placing the hierarchical query within an inline view, you are able
to temporarily flatten the region hierarchy to suit the purposes of
the query, which allows you to bypass the restriction on hierarchical
queries without resorting to splitting the logic into multiple
pieces. The next section will demonstrate a similar strategy for
working with aggregate queries.

5.4.4.2 Aggregate queries

Queries
that
perform aggregations
have the following restriction: all nonaggregate columns in the
SELECT clause must be included in the GROUP BY clause. Consider the
following query, which aggregates sales data by customer and
salesperson, and then adds supporting data from the
customer, region,
employee, and department
tables:

Since every nonaggregate in the SELECT clause must be included in the
GROUP BY clause, you are forced to sort on five columns, since a sort
is needed to generate the groupings. Because every customer is in one
and only one region and every employee is in one and only one
department, you really only need to sort on the
customer and employee columns
to produce the desired results. So the Oracle engine is wasting its
time sorting on the region and department names.

However, by isolating the aggregation from the supporting tables, you
can create a more efficient and more understandable query:

Since the cust_order table includes the customer
number and salesperson ID, you can perform the aggregation against
these two columns without the need to include the other four tables.
Not only are you sorting on fewer columns, you are sorting on numeric
fields (customer number and employee ID) rather than potentially
lengthy strings (customer name, region name, employee name, and
department name). The containing query uses the
cust_nbr and sales_emp_id
columns from the inline view to join to the
customer and employee tables,
which in turn are used to join to the region and
department tables.

By performing the aggregation within an inline view, you have
sidestepped the restriction that all nonaggregates be included in the
GROUP BY clause. You have also shortened execution time by
eliminating unnecessary sorts and minimized the number of joins to
the customer, region,
employee, and department
tables. Depending on the amount of data in the tables,
these
improvements could yield significant performance gains.

5.4.5 Inline Views in DML Statements

Now that you are comfortable with inline views,
it's time to add another wrinkle: inline views may
also be used in INSERT, UPDATE, and DELETE statements. In most cases,
using an inline view in a DML statement improves readability but
otherwise adds little value to statement execution. To illustrate,
we'll begin with a fairly simple
UPDATE statement and then show the
equivalent statement using an inline view:

In the first statement, the WHERE clause of the UPDATE statement
determines the set of rows to be updated, whereas in the second
statement, the result set returned by the SELECT statement determines
the target rows. Otherwise, the two statements are identical. For the
inline view to add extra value to the statement, it must be able to
do something that the simple update statement cannot do: join
multiple tables. The following version attempts to do just that by
replacing the subquery with a three-table join:

ORA-01779: cannot modify a column which maps to a non key-preserved table

As is often the case in life, we can't get something
for nothing. To take advantage of the ability to join multiple tables
within a DML statement, we must abide by the following rules:

Only one of the joined tables in an inline view may be modified by
the containing DML statement.

To be modifiable, the target table's key must be
preserved in the result set of the inline view.

Although the previous UPDATE statement attempts to modify only one
table (cust_order), that table's
key (order_nbr) is not preserved in the result
set, since an order has multiple line items. In other words, rows in
the result set generated by the three-table join cannot be uniquely
identified using just the order_nbr field, so it
is not possible to update the cust_order table via
this particular three-table join. However, it is possible to update
or delete from the line_item table using the same
join, since the key of the line_item table matches
the key of the result set returned from the inline view
(order_nbr and part_nbr). The
next statement deletes rows from the line_item
table using an inline view nearly identical to the one that failed
for the previous UPDATE attempt:

The column(s) referenced in the SELECT clause of the inline view are
actually irrelevant. Since the line_item table is
the only key-preserved table of the three tables listed in the FROM
clause, this is the table on which the DELETE statement operates.
Although utilizing an inline view in a DELETE statement can be more
efficient, it's somewhat disturbing that it is not
immediately obvious which table is the focus of the DELETE statement.
A reasonable convention when writing such statements would be to
always select the key columns from
the target table.

5.4.6 Restricting Access Using WITH CHECK OPTION

Another way
in which
inline views can add value to DML statements
is by restricting both the rows and columns that may be modified. For
example, most companies only allow members of Human Resources to see
or modify salary information. By restricting the columns visible to a
DML statement, we can effectively hide the salary column:

Of course, the person writing the UPDATE statement has full access to
the table; the intent here is to protect against unauthorized
modifications by the users. This might prove useful in an n-tier
environment, where the interface layer interacts with a
business-logic layer.

Although this mechanism is useful for restricting access to
particular columns, it does not limit access to particular rows in
the target table. To restrict the rows that may be modified using a
DML statement, you can add a WHERE clause to the inline view and
specify WITH CHECK OPTION. For example, you may want to restrict the
users from modifying data for any employee in the Accounting
department:

The addition of WITH CHECK OPTION to the inline view protects against
any data modifications that would not be visible via the inline view.
For example, attempting to modify an employee's
department assignment from Sales to Accounting would generate an
error, since the data would no longer be visible via the inline view:

5.4.7 Global Inline Views

Earlier in the chapter, you saw how the WITH clause can be
used to allow the same subquery to be referenced multiple times
within the same query. Another way to utilize the WITH clause is as
an inline view with global scope. To illustrate, we will rework one
of the previous inline view examples to show how the subquery can be
moved from the FROM clause to the WITH clause.
Here's the original example, which comes from Section 5.4.4.1:

Earlier in this section, we stated that inline views
"are always executed prior to the containing query
and, thus, may not reference columns from other tables or inline
views from the same query." Using the WITH clause,
however, you are able to break this rule, since the
na_regions inline view is visible everywhere
within the query. This makes the na_regions inline
view act more like
a temporary table than a true
inline view.