I don't really have a nice solution, however I would have to recommend
against using rownum type solutions...
There is a really good chance that the users won't understand the
implications of such a clause and therefore won't understand it's affect
upon the results. A user might ask for the sum(sales) of a given
salesperson, for example, and would then have no idea if the result
obtained was correct or an invalid result caused by the rownum clause.
As someoe suggested - are these queries being executed against an OLTP or
OLAP system? If they are connecting to the OLTP then you may need to
create a replica of some sort for them to run their queries against.
You talk about "insane queries" - Are the queries "insane" because they are
complex (but necessary to answer a question) or because they are written
poorly? If they are written poorly then user education is required. If
the queries are complex then determine if they are executed often and, if
so, look at tuning those queries and turning them into a standard report.
I always take the approach of "if that's what they need to know then I'll
help them find out".
Oracle does also provide some features to limit resources to certain users
once the machine hits 100% utilisation. You could look into this so that
the adhoc queries get choked when the system is busy, leaving everything
else to run ok. This way the adhoc users will get valid results (most
important - if they don't get complete results then they might as well not
run the query at all) but they will have to wait a while (might
subconsciously teach them not to write bad queries).
Help I've helped a little,
Chris" To: Multiple recipients of list ORACLE-L
Subject: Adhoc queries and limiting the amount of records queried...
I just wanted to ping the list to see what other people have done to
control or constrain adhoc query users???
We have a group that is struggling with the adhoc query piece that's in
production. Some of the users end up firing off insane queries. The group
is trying to find a way to limit the amount of records queried for, so that
a wild query doesn't hose the database.
Appending a ROWNUM to the WHERE clause is one idea. Using USER PROFILEs is
another. Any other thoughts??
Dare I ask?this custom app also runs on SQL Server, so SQL Server ideas
would also be appreciated.
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
Please see the official ORACLE-L FAQ: http://www.orafaq.com
Author: Mark Richard
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).