Re: Corporate Acceptance of Creating Views ?

From: Alan <alanshein_at_erols.com>
Date: Thu, 1 Nov 2001 16:04:59 -0500
Message-ID: <9rsd9t$uei4p$1@ID-114862.news.dfncis.de>

Hi. I'm glad to finally have someone agree with me! Okay, basically I have scripts that either drop or truncate, and then do a create table as select, or inserts. Sometimes updates are required after the initial records are created, though I try to avoid that whenever possible. The scripts take on the general format of:

spool
set environment

make backup copies of tables
drop tables
drop indexes (if tables aren't dropped)
truncate tables (I drop these indexes first)

create tables as select from
create tables as select from table_at_otherserver (see note below) inserts
create indexes for joins in next step
updates
create rest of indexes

drop any temporary tables I created

reset environment
spool off

The reason for bringing over tables from other servers, rather than just querying them and bringing over the rows needed, is that Oracle will not optimize join queries initiated from another server. It is faster to bring over the tables (or parts thereof without doing any joins), extract what you need, and then delete them when you're done. Also, the "create" needs to be initiated on the server you want the tables to go to. You can't do DDL using a dblink except on the originating server.

I have a couple of different kinds of search tables. In the first type, the idea is to eradicate the need for using "LIKE", as LIKE will not use an index. So, in a common example, a table where you have company names and associated info, I created search fields that contain only the first X number of letters of the name. I typically use the first 3 thru 12 characters. So, the table is something like:

search3 varchar2(3),
search4 varchar2(4),

.
.
.

search12 varchar2(12)
company_name varchar2(100)

I index every column. The user interface (you need help from those accursed developers) is written to parse out how many characters a user enters for search criteria. So, if a user enters 7 characters, the search7 column is queried. Because search7 is indexed, results are immediate. Of course, the more characters entered, the faster the results (fewer rows returned). Now, there are a few problems. You have to deal with embedded apostrophes (O'HARA), upper/lower case (I UPPER everything), and the inability to begin with a wildcard (like %ACME for THE ACME PIANO COMPANY).

This technique has saved untold number of hours of the 300+ employees who need to do these searches. Queries that used to take 2-10 minutes now take 1-17 seconds.

To handle the initial wildcard problem, the developers need to parse for the % in the first position, and then use a LIKE in the where clause.

For apostrophes, I change the apostrophes to graves ( lower case on the ~ key), and all the users are trained to use this instead of the apostrophe when searching. The original data is not changed. I can do this because I don't use views <g>.

Otehr search tables are each essentially a view created as one table. Instead of CREATE VIEW, it's CREATE TABLE. I then index whichever columns I need to index (sometimes it's all columns, sometimes just what is essentially a PK). These search tables and indexes are not placed on the same drives as the original tables, so this avoids contention with the OLTP system.

Again, this won't work for everyone. You need the time overnight to run the scripts, you need to not have need to access live data (so then use both techniques, views and search tables!), and your requirements need to be somewhat static, or at least glacial.

HTH "jane" <janeyiu_at_optonline.net> wrote in message news:y2iE7.1804$Ie.230881_at_news02.optonline.net...
> Alan, I have been thinking about proposing the same thing you're talking
> about here, for we are not 7 X 24 either and we now actually have views
> that are join of views. (the db is just darn bad design and reporting
> requirement
> quite complicated)
>
> Now, can you tell me in more detail how your "search table" method is
> carried out ?
> How are these tables created/updated ?....Drop x; Create x as select ?
> ....or by
> PL/SQL stored procedures or a package (issuing inserts?) ? do you schedule
> this
> via dbms_job or in scripts ?
>
> I am really curious.
> Thanks
> jane
>
> "Alan" <alanshein_at_erols.com> wrote in message
> news:9rmtmf$ugueb$1_at_ID-114862.news.dfncis.de...
> > 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.
> >
> > "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
> > >
> >
> >
>
>
Received on Thu Nov 01 2001 - 15:04:59 CST