oracle-inline view

Question

Why inline views are used..??

Answer 1

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.

Inline views provide

1. Bind variables can be introduced inside the statement to limit the data
2. Better control over the tuning
3. Visibility into the code

Answer 2

There are many different reasons for using inline views. Some things can't be done without inline views, for example:

1) Filtering on the results of an analytic function:

select ename from
( select ename, rank() over (order by sal desc) rnk
  from emp
)
where rnk < 4;

2) Using ROWNUM on ordered results:

select ename, ROWNUM from
( select ename
  from emp
  order by ename
);

Other times they just make it easier to write the SQL you want to write.

Answer 3

To get top N ordered rows.

SELECT name, salary, 
FROM (SELECT name, salary
      FROM emp
      ORDER BY salary DESC)
WHERE rownum <= 10;

Answer 4

An inline view can be regarded as an intermediate result set that contributes to the required data set in some way. Sometimes it is entirely a matter of improving maintainability of the code, and sometimes it is logically neccessary.

Answer 5

You will often use inline views to break your query up into logical parts which helps both readability and makes writing more complex queries a bit easier.

Jva and Tony Andrews provided some good examples of simple cases where this is useful such as Top-N or Pagination queries where you may want to perform a query and order its results before using that as a part of a larger query which in turn might feed a query doing some other processing, where the logic for these individual queries would be difficult to achieve in a single query.

Another case they can be very useful is if you are writing a query that joins various tables together and want to perform aggregation on some of the tables, separating group functions and the processing into different inline views before performing the joins makes managing cardinality a lot easier. If you want some examples, I would be happy to provide them to make it more clear.

Factored subqueries (where you list your queries in the WITH clause at the start of the query) and inline views also often bring performance benefits. If you need to access the results of the subquery multiple times, you only need to run it once and it can be materialized as a Global Temporary Table (how the optimizer acts isn't totally black and white so I won't go into it here but you can do your own research - for example, see http://jonathanlewis.wordpress.com/2007/07/26/subquery-factoring-2/)

Answer 6

From the Oracle Database Concepts document there are the inline view concept definition:

An inline view is not a schema object. It is a subquery with an alias (correlation name) that you can use like a view within a SQL statement.

About the subqueries look in Using Subqueries from the Oracle SQL Reference manual. It have a very nice pedagogic information.

Anyway, today is preferred to use the Subquery Factoring Clause that is a more powerfull way of use inline views.

As an example of all together:

WITH 
   dept_costs AS (
      SELECT department_name, SUM(salary) dept_total
         FROM employees e, departments d
         WHERE e.department_id = d.department_id
      GROUP BY department_name),
   avg_cost AS 
SELECT * FROM dept_costs
   WHERE dept_total >
      (SELECT avg FROM (SELECT SUM(dept_total)/COUNT(*) avg
                          FROM dept_costs)
      )
      ORDER BY department_name;

There you can see one of all:

• An inline view query: SELECT SUM...
• A correlated subqueri: SELECT avg FROM...
• A subquery factoring: dept_costs AS (...

What are they used for?:

• To avoid creating an intermediate view object: CREATE VIEW ...
• To simplify some queries that a view cannot be helpfull. For instance, when the view need to filter from the main query.