[Solved] Simple Oracle query started running slow when returning more than 251 results
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Simple Oracle query started running slow when returning more than 251 results

Database type:

As the title implies, I have a very simple Oracle query that is returning in 5 seconds when I go beyond returning 251 results. I am using SQL Developer, and attaching using the built in connection utility (there is no facility for an ODBC connection in this application).

The query found here is fast (fast enough) (pa_stu holds roughly 40k rows):

Select * From pa_stu Where rownum < 252;

Oracle returns the data to me in .521 second, according to SQL Developer.

The following query, and ones that pull larger sets of data, are the culprit:

Select * From pa_stu Where rownum < 253;

Oracle returns the data to me for that last one in 5.327 second, according to SQL Developer.

All queries being used for testing have the same explain plan. That is, the filter predicate of ROWNUM<251 (change the 251 to whatever number is being used) and a TABLE ACCESS of FULL.

The results above are consistent, plus, bumping up the evaluated number to about 1000 doubles the result time to roughly 10 seconds (consistently). It is as if some buffering is going on somewhere, and that buffer is too small. Additionally, this is happening on only one of our Oracle servers. The other, more highly used one (holds different data as well) has no problem returning 100's of thousands of records using similar statements.

The databases are controlled by a DBA, and, I have run all of this by her. She does not have a solution. This actually started happening a month or so back, and was not the case many months ago, if that is meaningful. It was just not as noticeable as it is now.

Thank you for any help.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX pa_stu_idx_rownum ON pa_stu (rownum);
The optimized query:
        pa_stu.rownum < 252

Related Articles

* original question posted on StackOverflow here.