| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Adhoc queries and limiting the amount of records queried...
I'll add my $.02 about rownum...
Rownum is highly variable. It all depends upon access paths, sorting,
physical structures, etc. Even careful use can result in non-repeatable
reads, bad data, etc. Many experienced and talented techies and power users
will not completely understand the oracle internal storage and transaction
architecture and therefore don't completely understand rownum. This is not a
dig against developers or users. Most of them don't have the need to get to
know the under-the-covers Oracle.
When properly understood and very carefully used, it can be a great help.
-----Original Message-----
Sent: Tuesday, October 29, 2002 2:44 PM
To: Multiple recipients of list ORACLE-L
Chris,
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,
Mark.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
INET: Dan.Fink_at_mdx.com
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). Received on Wed Oct 30 2002 - 10:54:42 CST
 |
 |