IMO,
using views is a great advantage for both developer and end-user, until
there are performance issues with the views. For performance tuning the
only thing in many cases is to open up the view and hand code the SQL into
PL/SQL cursors, or to create new more specific view.
On average the performance hit on using the views is minimal, until you
define views that has 300-500 columns and joins to 10-20 tables. When the
volumes get higher then using one view for all-purpose entity is sometimes
impossible.
Using views on Oracle Forms (why not any client, VB, Java etc.) has many
benefits on performance. The most important benefit is that you don't need
to fetch the data for foreign key references in post-query trigger.
For on-line reporting tools it is sometimes dangerous to let end-users use
the base tables for reporting. With on-line reporting tools I mean tools
where you can just drag-and-drop tables and join them as you like. With
these tools it is easy to saturate the DBMS engine with unefficient SQL
statements which in worst cases perform full table scans and effect the
other users performance. Using SQL optimized views in this context makes
sense. Other way would be to use, as Alan expained, summarized reporting
tables for the reporting.
"Alan" <alanshein_at_erols.com> wrote in
news:9rpnd1$viktj$1_at_ID-114862.news.dfncis.de:
> Tom,
>
> First, let me say that I consider you to be the planet's leading expert
> on almost everything Oracle. Your Wrox "Expert One-on-One" book is
> sitting within arm's reach. However, I am disappointed that your
> reasons for using views are all based on making life easier for the
> developer. My reasons for NOT using views are based on making life
> easier (faster response time) for the end-user. I am still open to
> persuasion if you can give me some reasons why views are better for the
> average corporate end-user (not power users who understand how to make
> joins and so on). I realize that in some examples, a view can be a more
> logical business construct for certain end-users, but reporting tools
> can hide the gritty details anyway. I'm not looking for justification
> of views, just to be convinced that they are beneficial to end users,
> not just developers.
>
> "Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
> news:9rn75p0crk_at_drn.newsguy.com...
>> In article <9rmtmf$ugueb$1_at_ID-114862.news.dfncis.de>, "Alan" says...
>> >
>> >Although I don't "outlaw" views, I haven't found a compelling reason
>> >to use them. I create search tables that would otherwise be views,
>> >and index them for optimum querying. Some tables are partially or
>> >totally denormalized, and some have every column indexed. I update
>> >the search tables nightly, weekly, or monthly, depending on business
>> >need. I don't have to worry about queries or indexing affecting OLTP,
>> >and query repsonse is excellent because there are fewer joins needed.
>> >Also, I don't have the expense of a creating a view instance for each
>> >user.
>> >
>> >Of course, it really depends on the situation. We are not 7 x 24, so
>> >I have the luxury of running the search table updates overnight, the
>> >ad hoc queries are fairly predictable, and our security needs are
>> >pretty static.
>> >
>> >Anyway, I'm not dogmatic about it, but I do need to be convinced that
>> >a view is the way to go in a particular situation.
>> >
>>
>> why? if the developer has a tool, wants to use the tool, sees value
>> in the tool, why wouldn't you let them use it?
>>
>> Sometimes, you just want to use it.
>>
>> I'll try to list some of the reasons:
>>
>> o layer of protection from changes. I can have 50 reports use a
>> single view, when you change my structures, I fix A view, not 50
>> reports.
>>
>> o security. I can build some cool security into them.
>>
>> o ease of reading code. Instead of having mega lines of SQL in my
>> procedure, I create a view. I can tune the view, I can change the
>> view, the procedure remains the same. I like my code to be readable,
>> views can be of IMMENSE help here.
>>
>> You know what though, this is something that just plain and simply
>> doesn't need to be justified. Its a tool, its a tool to be used.
>> developers should not have to justify to a DBA why they want to use
>> this tool.
>>
>>
>> >"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
>> >news:9rmeoj01bgk_at_drn.newsguy.com...
>> >> In article <eQoD7.43362$C7.13166440_at_news02.optonline.net>, "jane"
>> >> says...
>> >> >
>> >> >Is it true that in general corporate enviornment, the use of Views
>> >> >is discouraged ? even frowned up ?
>> >> >
>> >> >I was working with this "seasoned" developer on developing
>> >> >reports, I am new to the Oracle enviornment
>> >> >and she insisted that I should NOT use views if at all possible,
>> >> >preferablly not at all.
>> >> >
>> >> >"You should be able to get all the data you need with straight
>> >> >SQL...even if it have to go for pages !"
>> >> >"...you are creating yet another dependency...it's another object
>> >> >that has to be maintained !......"
>> >> >
>> >> >The thing was with tools like Crystal Reports, it does not handle
>> >> >manual SQL very well (requires
>> >> >a separate file to store the query)
>> >> >
>> >> >Is this true ? Was she full of bs ?
>> >> >
>> >> >thanks
>> >> >jane
>> >> >
>> >> >
>> >>
>> >> I love views -- for the same reason packages are the only thing you
>> >> should use in real code (never a standalong procedure).
>> >>
>> >> Packages protect you from changes -- the SPECIFICATION won't change
>> >> -- but the implementation might (eg: you find a bug in the
>> >> algorithm and fix it -- the interface didn't change -- same inputs
>> >> and outputs but the mechanics changed)
>> >>
>> >>
>> >> Views are the same way. Underlying data structure changes (eg:
>> >> someone adds a column, denormalizes a table, splits a single table
>> >> into two, whatever) don't affect your CODE -- just your view.
>> >> Consider the view a "specificiation", fix the view -- you've fixed
>> >> ALL pieces of code that use it.
>> >>
>> >> There are some people who say -- you should NEVER query a table.
>> >> You should always query a view. These people are never phased by a
>> >> request to change a column name or the order of columns in a table
>> >> definition as it is as trivial as dropping and recreating the view
>> >> now.
>> >>
>> >> I might not go that far (but when asked to change a column name --
>> >> i will rename the table, create a view and grant on the view, no
>> >> one ever knows)..... but it shows there is a difference of opinion
>> >> out there.
>> >>
>> >> Views are a tool, a programming construct. Anyone who "outlaws"
>> >> them is *wrong* and being very short sighted.
>> >>
>> >> --
>> >> Thomas Kyte (tkyte_at_us.oracle.com)
>> >> http://asktom.oracle.com/ Expert one on one Oracle, programming
>> >> techniques and solutions for Oracle.
>> >> http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are
>> >> mine and do not necessarily reflect those of Oracle Corp
>> >>
>> >
>> >
>>
>> --
>> Thomas Kyte (tkyte_at_us.oracle.com)
>> http://asktom.oracle.com/ Expert one on one Oracle, programming
>> techniques and solutions for Oracle.
>> http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine
>> and do not necessarily reflect those of Oracle Corp
>>
>
>
--
-----------------------------------------------------------------------
Anti-spam: To reply by e-mail please ERASE the "4" after kaukovuo4
Received on Wed Oct 31 2001 - 23:38:56 CST
