Re: Summary: Controlling "ad hoc" queries

In article <1992Sep11.104127.364_at_hhcs.gov.au>, pihlab_at_hhcs.gov.au writes:
> In article <laujakINNp5_at_news.bbn.com>, NBROOKS_at_BBN.COM (Nat Brooks) writes:

>> In article <lapscgINN8jg_at_news.bbn.com> Nat Brooks, NBROOKS_at_BBN.COM 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?

>> If so, what are the alternatives (rapid development of query applications, 

>> If not, how are they best used (only with heavy training for users, small
>> databases only, on extracted "reporting" databases...)?

>> My intention here is to stimulate discussion.  Speculate away!

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:   suskind%edoras_at_mdcbbs.com
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