How to limit globalet results (or session) in Oracle?

Question

I am trying to limit the result set to size without having to change every SQL statement from the application (at least for example). Therefore, adding a ROWNUM for each query is not an option.

I am looking more or less for some global parameter, or at least a session parameter (like SET ROWCOUNT in SQLServer).

The application uses the JDBC source to connect to Oracle, so I can set the parameter for the entire session in the JDBC SQL init pool.

Answer 1

Have you tried using the Statement.setMaxRows method? This is a standard JDBC approach that will automatically discard rows after a specified maximum number.

However, in terms of performance, you are most likely to be best served by actually modifying the queries that are sent to the database. If you do as @MK suggests, you tell the optimizer that you are going to collect only N rows. This potentially allows the optimizer to choose a more effective plan.

Answer 2

I suspect there is no way to do this. You can create an AOP tip that will modify every call to executeQuery (), completing the query with

 "select * from ( " + origQuery + " ) where ROWNUM <= 5" 

but I hope this is a temporary or test thing we're talking about.