Re: Corporate Acceptance of Creating Views ?

In article <9rpnd1$viktj$1_at_ID-114862.news.dfncis.de>, "Alan" says...
>
>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 as a developer am tasked with making things go faster for the end user.

I as a developer gotta develop stuff.

I as a developer need something to make my life easier and faster as well.

The impact on the end user should in fact be so neglible as to the point where they do NOT even know a view is coming.

I have a view I use in exactly ONE place (goes contrary to what everyone else here is saying) -- ONE place. Why did I use a view? Cause its a hacking big view (over 100 lines long) and looked totally out of place in my code. Additionally -- I was able to more easily TUNE this view and re-tune it (without have to recompile the code -- true the code went invalid, is it far easy to tune a VIEW then do tune the SQL inside the code, I can just use queries against the view to see how it'll perform -- if the query is buried in code, I have to exact the code which involves setting up simulations and everything else).

This view runs (gasp) exactly as fast as if I put the query into the code itself!

> 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).

we are not talking about the average corporate end user here. We are talking about a DBA who has said to a DEVELOPER -- the guy responsible for doing reports and such for heavens sake -- that they are not allow to use views at all (better not query that data dictionary then)

> 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.

Ok, here is how it is beneficial to the end users -- the developer is happier and more productive and can churn out those reports faster. Consider this -- you have a monster query to write. It takes what a couple of seconds to recompile the report in the report environment, couple more to run it, then track down that trace file so you can tkprof it and see the results of your tuning. Say it takes a minute (very conservative indeed).

Me, with my view, why I can sit in sqlplus with "edit" at my finger tips and tune that view to death. The report -- that I never touch, I don't have to, its only got "select * from v" in it.

Ok, so we are maybe more productive. anything else?

Well, its six months later and someone decides to change something. Oh bother, go find every report and pull it up in my reporting tool, find the affected queries (IF I CAN -- there is yet another cool reason for the view -- I see the DEPENDENCY between the view and the base tables) fix them and redeploy the report.

Oh wait, I was using a view, I'll just fix that view and -- there we go. reports are back up and running.

I don't see why things have to be for the "end user". If that were the case, we'd all be programming punch cards with assembler. The developer doesn't matter here -- don't give them any tools ( you know, debuggers don't do much for end users either do they ).

I don't get it.

>
>"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@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@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@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