Why performance degradation with query hint FIRST_ROWS(1000)? I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input.

In case the user supplies very unselective arguments, the query takes a lot of time to complete. In case the query would return more than 1000 rows, the user should be supplied with an error message.

The query does not have an order by clause, but just joins three (large) tables.

The idea now was to use the query hint FIRST_ROWS(1000) to speed up. Tests in SQL developer showed the cost metric is considerably reduced (factor 10).

The query is executed through the Entitymanager using a native query.

However, the behavior within the application is as follows: duration of OraclePreparedStatement.executeQuery is reduced by 60%. However, now a lot more of time is spent in EJBQueryImpl.getResultList which before took less than 100ms creating a high performance degradation compared with leaving out the query hint.

Code is essentially:

Query query = em.createNativeQuery(sqlQuery); // sqlQuery has query hint "FIRST_ROWS(" + (maxResults + 1) + ")"

query.setMaxResults(maxResults + 1);

List&lt;Object[]&gt; resultList = query.getResultList();

if (maxResults != 0 &amp;&amp; resultList.size() &gt; maxResults) {
    resultSetTruncated = true;
}


Query is:

SELECT ... FROM
    T1 
LEFT OUTER JOIN T2 on T1.pk1 = T2.pk1
        AND T1.pk2 = T2.pk2 
        AND T1.colx = T2.pk3
LEFT OUTER JOIN
    T3 
        ON T2.pk1 = T3.pk1
        AND T2.coly = T3.pk1
WHERE
    T1.pk1 IN ('081111') 
    AND EXISTS (
        SELECT 1 FROM T4 WHERE
            T1.PK1 = T4.PK1
            AND T2.PL2 = T4.PK2 
            AND T4.KIND = 'N' 
            AND T4.NORMALIZED LIKE 'DA%'
    )


Table T3 is very large and the specified conditions in the case of interest are not very selective.
Query optimization isn't an exact science. Oracle cannot always find the best execution plan and it certainly can't do any magic. So here are some points that might or might not help:


With the FIRST_ROWS hint, Oracle favors execution plans with low cost for the first few rows over execution plans with minimal cost for retrieving the whole result set. It can also mean that retrieving the whole set takes longer with the hint than without. So what you are observing might be the intended behavior.
I don't how whether maxResults is a constant. If it's not, then you create a lot of queries that are different from Oracle's view even though you just slightly vary the hint. As a consequence, Oracle will spend a lot of time parsing the query and finding an execution plan. The cached execution plans won't help because they are for a slightly differnt query. So you better use a fixed value for your hint, like FIRST_ROWS(200). That should be good enough.
Oracle execution plan is derived from cost estimations. The cost estimations are based on statistical data about your tables. Please check whether your table statistics are up-to-date. Otherwise Oracle is likely to construct bad execution plans.


Please provide more information about your query so that we can give you more specific help. What WHEREconditions does it contain? Does it contain a clause like WHERE ROWNUM &lt;= 1000? How many numbers of rows do you normally expect?