Re: Corporate Acceptance of Creating Views ?

[Quoted] 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.

[Quoted] 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 [Quoted] 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 [Quoted] here is saying) -- ONE place. Why did I use a view? Cause its a hacking big [Quoted] 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 [Quoted] have to recompile the code -- true the code went invalid, is it far easy to tune [Quoted] a VIEW then do tune the SQL inside the code, I can just use queries against the [Quoted] view to see how it'll perform -- if the query is buried in code, I have to exact [Quoted] 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).

[Quoted] we are not talking about the average corporate end user here. We are talking [Quoted] about a DBA who has said to a DEVELOPER -- the guy responsible for doing reports [Quoted] 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.

[Quoted] Ok, here is how it is beneficial to the end users -- the developer is happier [Quoted] and more productive and can churn out those reports faster. Consider this -- [Quoted] 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 [Quoted] track down that trace file so you can tkprof it and see the results of your [Quoted] 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 [Quoted] tune that view to death. The report -- that I never touch, I don't have to, its [Quoted] 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, [Quoted] go find every report and pull it up in my reporting tool, find the affected [Quoted] queries (IF I CAN -- there is yet another cool reason for the view -- I see the [Quoted] 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 [Quoted] are back up and running.

I don't see why things have to be for the "end user". If that were the case, [Quoted] 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 [Quoted] 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_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
>>
>
>

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