Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: using views in select statements

Re: using views in select statements

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Fri, 22 Oct 1999 09:47:08 +0200
Message-ID: <7up4q1$qp0$1@ctb-nnrp2.saix.net>


kal121_at_yahoo.com wrote in message <7unfj2$9ar$1_at_nnrp1.deja.com>...
>
>What is the general opinion of using views instead of tables in a query?

Yes. No. Maybe. Dunno. :-)

>For example, is it better to run a query against four separate tables or
>against a single view that already contains the relevant columns from
>those four tables? I know it is hard to answer this without seeing the
>query(s), but I'd just like a general opinion on this.

A view is simply a predefined SQL statement. Consider this:

CREATE VIEW person AS SELECT name, surname FROM employees

SELECT * FROM person WHERE surname LIKE 'SMIT%'

If you are comparing the above SELECT from a VIEW statement with the following "normal" SQL query run by a user.. : SELECT name, surname FROM employees WHERE surname LIKE 'SMIT%'

..then there are no difference at all. It basically one and the same thing. Execution speed for all intents and purposes will be the same. VIEWS are not created to make database performance faster - whether the SQL comes from a user application or from a VIEW inside the database, it's the same SQL and needs to go thru the exact same execution process.

The primary reason for a view is to make the data easier accessible for the user - create an abstraction layer that removes some of the more complexities of the database. A perfect example is the DBA views. They are based on the more complex V$ views which in turn are based on the very complex (even obscure) internal X$ tables of Oracle.

Another function of VIEWS is security. You can create a view for one department on a table that is different from a view by another department on the same table. This means for example that the HSR department can view the employees' salaries while the marketing department can not. Each is limited to the data what the view provides them (assuming of course they can not access the underlying table and each others views).

The last thing I can think of what a VIEW provide is faster network thruput when dealing with large SQL statements. Let's say you have a SELECT statement joining 5 tables and accessing 40 columns by name. The resulting SQL statement can be well over 1KB in size. You can implement that as a view and instead of having to send a 1KB across the network everytime, the client apps only need to do a "SELECT * FROM view". Properly implemented, VIEWS and STORED PROCS can cut down your SQL "send" network traffic down up to 80% if not more.

regards,
Billy Received on Fri Oct 22 1999 - 02:47:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US