CODEWITHSUNDEEP
oracle-databaseoracle11g
Daniel Hernández
Daniel Hernández

Reputation: 4256

Limit query size for specific users

Is there a way to limit the rows returned for a user in Oracle.

We have some users than can query some tables with millions of records decreasing the performance of the database, so I would like to know if there someway to set max size of records per user.

For example, If I have the table: APP.HISTORY with 10,000,000 records and the user 'dummy', I would like to set for dummy user that can only read 10,000 records from it.

For example if 'dummy' execute:

select * from APP.HISTORY

It will only return 10,000 records, instead try to fetch the 10,000,000 records

Upvotes: 0

Views: 239

Answers (1)

sstan
sstan

Reputation: 36483

There isn't any built-in functionality to limit the number of results per user.

However, even if you could, that wouldn't necessarily help you resolve your performance concern.

Consider for example a query like:

select *
  from (select *
          from app.history
         order by some_field desc)
 where rownum < 2

According to your requirements, user dummy would be able to run this and get back the single result he's interested in. However, assuming some_field is not indexed, then, even though this query will return a single record, it still has to order all 10,000,000 records to produce that single row.

As suggested by OldProgrammer in the comments, consider using resource groups, which is a very flexible and configurable way of throttling CPU and I/O usage.

Otherwise, if you don't trust user dummy to write smart and efficient queries, then don't give him direct access to the database.

Upvotes: 1

Related Questions