Create Oracle Views tips

Oracle Tips by Burleson Consulting

Oracle Views

As a DBA one of the types of objects you will manage
will be views. In this section we will introduce you to Oracle view constructs.
Following your introduction to views, we will discuss the benefits and downsides
of views in Oracle.

Inside Oracle Views

A view is simply the representation of a SQL statement
that is stored in memory so that it can easily be re-used. For example, if we
frequently issue the following query

SELECT empid FROM emp;

I might well want to make this a view (the reality is
that we would probably never create a view for a statement this simple but we
wanted to use an easy example).

To create a view use the create view command as seen in
this example

CREATE VIEW view_emp AS SELECT empid FROM
emp;

This command creates a new view called VIEW_EMP. Note
that this command does not result in anything being actually stored in the
database at all except for a data dictionary entry that defines this view. This
means that every time you query this view, Oracle has to go out and execute the
view and query the database data. We can query the view like this:

SELECT * FROM view_emp WHERE
empid BETWEEN 500 AND 1000;

And Oracle will transform the query into this:

SELECT * FROM (select empid
from emp) WHERE empid BETWEEN 500 AND 1000;

Benefits of Oracle Views

Oracle views offer some compelling benefits. These
include:

* Commonality of code being used. Since a view is based
on one common set of SQL, this means that when it is called it's less likely to
require parsing. This is because the basic underlying SQL that is called is
always the same. However, since you can add additional where clauses when
calling a view, you still need to use bind variables. Additional where clauses
without a bind variable can still cause a hard parse!

* Security. Views have long been used to hide the tables
that actually contain the data you are querying. Also, views can be used to
restrict the columns that a given user has access to. Using views for security
on less complex databases is probably not a bad thing. As databases become more
complex, this solution becomes harder to scale and other solutions will be
needed.

* Predicate pushing. Oracle supports pushing of
predicates into a given view. Assume we had a set of layered views, like this:

The predicate in this statement is the where empid=100
statement. You may have one of tens or even hundreds of predicates (if you have
hundreds, we don't want to be supporting your SQL code!). Oracle will, in many
cases, push those predicates down into the views being called. Thus, Oracle will
transform the VW_LAYER_ONE view into a SQL statement that looks like this:

Note that both the predicate from view two (where deptno=100)
and the predicate from the SQL statement being executed (where empid=100) are
pushed down into the final view that is executed. This can have significant
performance benefits because now the bottom view can possibly use an index if
one exists on deptno and/or empid.

Predicate pushing has a number of restrictions that are
beyond the scope of this book, but you can find them in the Oracle
documentation. Also, any predicate pushing may result in a hard parse of the
underlying SQL that is executed. Hence, it is important to make sure you use
bind variables instead of literals in SQL code calling views. Thus, our SQL
should look something like this instead for best performance:

SELECT *
FROM vw_layer_two_dept_100WHERE
empid=:b100;

The downside to using Views

Views are very handy but they get badly abused, which is
a shame. I've seen views that return 50 columns, and have 40 predicates used to
return just two or three columns that could easily have been retrieved from a
simple SQL query. This is clearly a case of view abuse, and can lead to badly
performing views. Also see:
Guard against performance issues when using Oracle hints and views.

Stacked views can also mask performance problems. Again,
they can result in innumerable columns being returned when all you really need
are a few of those columns. Also, predicate pushing tends to break down as you
stack more views on top of more views. If you are going to start stacking views,
carefully review the rules for predicate pushing in the Oracle documentation.
They are rather long and involved!

This is an excerpt from the bestselling "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle
Certified Master). It's only $19.95 when you buy it directly from the
publisher
here.

You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.

��

Burleson is the American Team

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail:
and include the URL for the page.