Sleeping oracle problem

Question

SELECT * FROM ( select * from tableA where ColumnA = 'randomText' ORDER BY columnL ASC ) WHERE ROWNUM <= 25 

When executing this query, due to some optimization by Oracle, the query takes about 14 minutes. If I delete the where clause, the query will execute in seconds. most columns of the table have indexes on them, including those mentioned above. I do not have much flexibility in the structure of the request, since I use sleep mode.

This query also returns results with the correct result:

 SELECT * FROM ( select * from tableA, dual where ColumnA = 'randomText' ORDER BY columnL ASC ) WHERE ROWNUM <= 25 

is there something i can do using hibernate?

UPDATE: I am using EntityManager.createQuery () and I am using setMaxResults (25) and setFirstResult (). the request above looks like the request for hibernation when watching logs

Answer 1

I don’t understand what kind of explanation plans match your needs, but it seems the oracle uses a different index for the two requests.

Can you create an index containing columnA and columnL ?

If you have an index containing only columnA , you MAY discard this without having a big impact on the execution of other queries.

An alternative would be to add a hint to use the index used in a faster query. But for this you need to use your own sql.

Answer 2

Does that mean you are using hibernate / jpa? If so, I think you are using EntityManager.createNativeQuery() to create a query? Try removing the usage restriction and use .setMaxResults(25) instead of .setMaxResults(25) .

In any case, why do you need an external choice? Will not be

 select * from tableA where ColumnA = 'randomText' AND ROWNUM <= 25 ORDER BY columnL ASC 

give the desired results?