Re: Corporate Acceptance of Creating Views ? (LONG)

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.

I definitely agree with Mr. Kyte on this subject - views are extremely valuable for providing a consistent view of the data. And, I'm one of those people that thinks a table should never be accessed directly.

My company has several hundred clients running our legacy application, which consists of nearly two million lines of code. Currently, we are undertaking the migration of the application to an N-tier architecture. One of my roles is to define the database standards we will use, and views play a very important part in them.

I'll define what I have come up with so far (at least what is relative to the post), but, for clarity, I need to define some of the terms I'll use. First, an Application Suite provides a turnkey solution for a large area of the clients business operations. For example, General Accounting (GL, AP, AR, etc) and Human Resources would be application suites. An application suite is a collection of modules related to that area. Each module can function stand-alone (e.g. Invoice Management), as long as all of the components (individual program units) are available (Invoice Management might consist of invoice entry, invoice reporting, etc.)

Okay, so I have come up with a specific naming convention as follows:

Modules are assigned an abbreviation as part of their name along the lines of SECURITY_SEC, where SEC is the abbreviation for the SECURITY module.

Tables are named starting with the abbreviation of the owning module, the entity name, and an abbreviation for the table. For example, SEC_USERS_USR or SEC_PRIVILEGES_PRVS. Initially, for each table a PRIVATE synonym is created using the abbreviation for the table (e.g. SEC_USRS). All data access from the programming standpoint occurs through the synonym. The table name NEVER appears in code. In addition, a read-only view is created for the table in the form of SEC_USERS. Grants are given to the view to allow &#8220;public&#8221; access to the data for report writer purposes. No public synonyms are allowed since this could result in naming conflicts with any other application the client wants to install in the database.

So, what do we gain from this? We get a lot of flexibility&#8230;

During development, each application can live in it&#8217;s own schema, but during deployment, it can be installed into any schema we own, without worrying about naming conflicts. Our clients can install multiple instances of the applications under one database. One instance for testing, one for production, one for revision controls. At $40,000 per CPU licensing from Oracle, multiple licenses are not feasible &#8211; especially with clients running 16-way NUMA servers (I working on moving the database to a different machine than the app server).

The same concept also provides us with a way to provide application hosting for our clients if we choose to do so. We simply install each client&#8217;s software into a single schema for that client.

Okay, so that was off-topic from the view question, I just wanted to shed some background information &#8211; now, on to the views:

When we need to change a table structure, we don&#8217;t want to have to modify application code if we can help it. Since the application never references the table (all access was via the synonyms), the table changes can be isolated from the application code by replacing the synonym with an appropriate view.

Each of our clients operates the software a little differently. Things that are important to one client are irrelevant to another. This guarantees us that application and database tuning needs to occur in the field. Again, we can replace a synonym with a view that contains hints specific to that clients needs &#8211; with no application changes.

Each application needs to have it&#8217;s own security module, but we don&#8217;t really want to maintain multiple copies of the security tables, so once one is installed, a view can be used to make it appear to be the applications own table, even though it isn&#8217;t.

Our current applications are single language, but the future demands a multi-lingual system. The original views for reporting and ad hoc query purposes are designed to return the data in the language appropriate for the user running the query.

Differences between large and small clients also make views useful. A small client can suffice with a single instance of Oracle for all of their needs, but a large client may require that OLTP be handled by one instance, while OLAP is handled by another. However, in our application there is a small amount of analytical data desired by the OLTP users, and a small amount of transactional data desired by the OLAP users. We just replace the views or synonyms (in one instance) with database links (it really is a small amount of data) or a snapshot/materialized view.

There is a great amount of flexibility to be had by using views. We can employ the above examples, without changing any application logic, we just relocate/modify/restrict the data. This weekend, I will be performing an upgrade to a client's production system. During this upgrade, roughly 160 tables will be created or modified or dropped altogether. With the exception of some new features being added, no application logic changes were necessary - the data structures are hidden by the views.

I hope that somewhere in this rather lengthy post I have convinced someone to think of views, as Mr. Kyte suggests, as a tool (and a powerful one) to be used to develop flexible quality software. Does anyone else have other suggestions for views or any comments on the standards I&#8217;m defining?

Tom Barnes
Software Architect
GERS Retail Systems Received on Wed Oct 31 2001 - 14:23:23 CST