Re: Corporate Acceptance of Creating Views ?

I hear you, and we're just never going to agree completely. BTW, punch cards are slower (especially if you drop them), but assembly language would run fast...<g>

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9s1m4k0o16_at_drn.newsguy.com...
> 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_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
>
Received on Mon Nov 05 2001 - 18:34:52 CET