From: pihlab@hhcs.gov.au
Newsgroups: comp.databases.oracle
Subject: Re: Summary: Controlling "ad hoc" queries
Date: 11 Sep 92 10:41:27 +1000
Organization: Aust. Dept. Health, Housing and Community Services

In article <laujakINNp5@news.bbn.com>, NBROOKS@BBN.COM (Nat Brooks) writes:
> In article <lapscgINN8jg@news.bbn.com> Nat Brooks, NBROOKS@BBN.COM writes:
>>Has anyone out there successfully installed and supported an end user
>>query tool that was actually used by end users?

We have been using Easy*SQL, SQL*Plus, and FOCUS as end user tools for adhoc
reporting.  We tried SQL*QMX but the users never liked it so its just
stagnating.  Some users found Easy*SQL too simplistic and now have access to
SQL*Plus.  The Dept. went through a review process some time ago and FOCUS was
selected as THE end user reporting tool but the end users haven't picked it up
fully yet.  I've seen demos of Oracle's new end user type tools (SQL*Browser
etc) and they look nice but we haven't moved on them yet.

>> How did you prevent "monster" queries?

With Oracle6, you can't.  The best we could do was isolate them to their own
disk drive(s) away from transaction based production applications (same machine
though).

Oracle7 is supposed to allow you to set CPU and I/O limits per session/user so
this should help enormously.  Oracle7 also has the cost based optimiser which
should help the performance of end user querries.
 
>> How did you handle users formulating queries that returned misleading
>> results?

We provide crystal balls so users can see into the future and determine if the
results look ok.

Actually, there is no way of guaranteeing query correctness.  The users are
warned about this and are effectively on their own.  If they want confirmation
that a particular query is correct then they can approach the application
developers for review but this hasn't happened to my knowledge.

>> How did you /will you handle changes in the database schema?

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.

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...        pihlab@hhcs.gov.au
                 ^^
*******************************************************************
* 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.        *
*******************************************************************