Does the Oracle JDBC driver Make Any Changes to the SQL Syntax when It Is Issued with a Statement.setMaxRow Value? (Doc ID 2109696.1)

Last updated on AUGUST 30, 2023

Applies to:

Goal

An application is deployed on an application server. The application is not performing well and as part of the review of database AWRs , 2 quite expensive SQL statements are seen which are frequently called from the application. Presumably this application call should be fast because it is using JDBC stmt.setMaxRows(5000) to limit first 5000 rows (number is configurable and the 5000 is the application default value) .
The SQL statements are select statements with bind variables and ORDER BY clauses.

Around usage of stmt.setMaxRows(5000) where SQL statement does full table scan and sorting (has ORDER BY ), is it correct to understand that the JDBC will not start limiting rowset untill after all database work will be completed and scanning and sorting big tables would still be very expensive operation?

Does the Oracle JDBC driver make any changes to the SQL syntax when it is issued with a Statement.setMaxRow value? In other words, is there any attempt to pass a SQL hint or other mechanisms to optimize the SQL execution plan in view of the maximum number of rows?
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.