NetNews Usenet Archive 1992 #20/NN_1992_20.iso/spool/comp/database/oracle/1574

Internet Message Format | 1992-09-13 | 5.5 KB

Path: sparky!uunet!mdcbbs!bbs.mdcbbs.com!suskind
Newsgroups: comp.databases.oracle
Subject: Re: Summary: Controlling "ad hoc" queries
Message-ID: <[e-mail]>
From: [e-mail]
Date: 13 Sep 92 19:30:40 GMT
References: <[e-mail]> <[e-mail]> <[e-mail]>
Organization: E-SYSTEMS
Nntp-Posting-Host: bbs
Nntp-Posting-User: suskind
Lines: 101

In article <[e-mail]>, [e-mail] writes:
> In article <[e-mail]>, [e-mail] (Nat Brooks) writes:
>> In article <[e-mail]> Nat Brooks, [e-mail] writes:
>>>Has anyone out there successfully installed and supported an end user
>>>query tool that was actually used by end users?
>

>
> We don't allow adhoc querries on live production data.  Production data
> structures are geared (denormalised and indexed) for production activity and
> throughput.  We full normalise the production structures and place a full copy
> into the reporting database.  The reporting database has many many indexes
> which would slow down updates (which don't happen here) but are fine for
> reporting.

This is an interesting thought, but what about large databases (greater
than 100MBytes)? Is it an good use of diskspace? I guess this depends on
the amount users that have need for "ad-hoc" queries. Fortunately as of now
we only have about 5. A couple for each different database.

>
> Regular copies are a real pain but Oracle7 has some nice features which should
> make this a lot easier.
>
>> Are "user query tools" a dead end, due to inadequate protection against
>> "incorrect" queries?
>
> At the moment YES.  The standard reply from the vendor is to run it on a bigger
> machine which of course requires more license spending and maintenance costs.
> Keep screaming at the vendor long enough and they will eventually come up with
> something useful.
>
>> If so, what are the alternatives (rapid development of query applications,
> improved report writing tools...)?.
>
> When an application is built we try to identify the major reports that would be
> required.  Those that satisfy 90-99% of all inquiries.  The user is given the
> ability to modify the columns that are extracted and change range settings on
> the WHERE clauses but cannot add to the complexity of the query.  The base
> skeleton query is optimised by the application developers.
>
> We have found this to work quite well.  If the user's job changes to start
> requiring additional reports regularly then they are factored into the
> maintenance/enhancement resources for that project.
>
>> If not, how are they best used (only with heavy training for users, small
>> databases only, on extracted "reporting" databases...)?
>
> If you have an end user adhoc reporting requirement then you MUST TRAIN them in
> using the tools efficiently.  You MUST MONITOR who is using the adhoc enquiry
> facilities and clobber anyone who is doing inefficent reporting.
>
>> My intention here is to stimulate discussion.  Speculate away!
>
> Is the above a good start?
>
> --
>
> Bruce...        [e-mail]
>                  ^^
> *******************************************************************
> * Bruce Pihlamae  --  Database Administration                     *
> * Commonwealth Department of Health, Housing & Community Services *
> * Canberra, Australia                             (W) 06-289-7056 *
> *******************************************************************
> * These are my own thoughts and opinions, few that I have.        *
> *******************************************************************

The client query products like Oracle*Card or Oracle*browse etc would be
nice for the user wanted a particular grouping of columns out of a table.
However, these tools all require a good knowledge of how the database is
setup. How the relationships between tables are used, etc. Without this
knowledge it is really shooting in the dark.
Most of the users I know can only refer to data elements by what screens
they see the data in and have no idea about how the data is in the
database.
In order to give a user a "useful" tool with one of these packages, lots of
work would have to be done to either provide templates or lots of training.
Either approach could spell lots of bad queries clogging the network with
data that will be discarded and also loading down the server's CPU with
processing all this stuff.
As was stated Oracle V7 will provide quotas for CPU and I/O which would limit
this problem.
But what about a good legitimate query from a user that would
hit a large database. Would he be stopped in the middle of getting data.
True the trustworthy users would have higher quotas, but even those users
can pump out bad queries.
This would mean a DBA police force, sort of like points system. Do good
queries and I'll up your quota. If you screw up (which would require lots
of monitoring) I lower you quotas.

These just does not seem to be any happy medium. Either you piss off the
good users by inhibiting them or you spend you life policing the bad users
that constantly drag down the system and the network asking for meaningless
information.
+---------------------------------------------------------------------+
Barry A. Suskind                  Internet:   [e-mail]
MaBell: 703-560-5000x2348             UUCP: uunet!mdcbbs!edoras!suskind
E-Systems / Melpar Division 7700 Arlington Blvd, Falls Church, VA 22046
JSNM                                             Just Stark Naked Magic