hibernate oracle rownum issue

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

on execution of this query, due to some Oracle optimization, the query takes about 14 minutes to execute . If I remove the where clause , the query executes in seconds. most of the columns of the table have indexes on them, including the ones mentioned above. I do not have much flexibility on the structure of the query as I use hibernate.

This query returns results instantly too, 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 use EntityManager.createQuery(), and I use setMaxResults(25) and setFirstResult() too. the query above is what hibernate's query looks like, upon observation of logs

2
oraclehibernate
Could you please post HQL itself? Does your original query contain joins on collections? Also the mapping itself would be useful as well. – Stanislav Bashkyrtsev
Can you provide the explain plans for the two queries? – Jens Schauder
@ctapobep pastebin.com/qjzu5HKc is the HQL . as you can see, there are no joins. – vritantjain
@JensSchauder for query with no join and where clause, explain plan :static.inky.ws/image/2857/image.jpg for query with join on DUAL, explain plan : static.inky.ws/image/2858/image.jpg for query with no join but where clause, explain plan : static.inky.ws/image/2859/image.jpg – vritantjain
the last one is no join and no where clause, sorry. – vritantjain

2 Answers

0
votes

I don't get the explain plans exactly matched to your queries, but it seems oracle using a different index for the two queries.

Can you create an index containing columnA and columnL?

If you have an index only containing columnA, you MIGHT be able to drop that without a large effect on performance of other queries.

An alternative would be to add a hint to use the index used in the faster query. But this would require you to use native sql.

-2
votes

this means you are using hibernate/jpa? If so, I guess you are using the EntityManager.createNativeQuery() to create the query? Try removing your where-restriction and use the .setMaxResults(25) on the Query instead.

Anyways, why do you need the outer-select? Wouldn't 

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

produce the desired results?