|
 |
|
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:
-- View
One
CREATE
VIEW vw_layer_one
AS SELECT
* FROM emp;
-- view
two
CREATE
VIEW vw_layer_two_dept_100
AS SELECT
* FROM vw_layer_one
WHERE
deptno=100;
Then assume we issued this query:
SELECT *
FROM vw_layer_two_dept_100
WHERE
empid=100;
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:
CREATE
VIEW vw_layer_one
AS SELECT
* FROM emp
WHERE
deptno=100
AND empid=100;
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_100
WHERE
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!
Oracle views notes:
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.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
and include the URL for the page.
