Consider the following query:
SELECT * FROM ( SELECT ARRM.*, ROWNUM FROM CRS_ARRANGEMENTS ARRM WHERE CONCAT(ARRM.NBR_ARRANGEMENT, ARRM.TYP_PRODUCT_ARRANGEMENT) > CONCAT('0000000000000000', '0000') ORDER BY ARRM.NBR_ARRANGEMENT, ARRM.TYP_PRODUCT_ARRANGEMENT, ARRM.COD_CURRENCY) WHERE ROWNUM < 1000;
This query runs on a table that has 10 000 000 entries. When running the query from Oracle SQL Developer or my application it takes 4 minutes to run! Unfortunately that is also the behaviour inside the application that I'm writing. Changing the value from 1000 to 10 has no impact at all, suggesting that it is doing a full table scan.
However when running from SQuirreL the query returns within a few milliseconds. How is that possible? Explain plan generated in SQuirreL gives:
But a different explain plan is generated in Oracle SQL Developer, for the same query:
Any idea how this difference in behaviour is possible? I can't get to understand it. I tried with JPA and raw JDBC. In the application I need to parse through all 10 000 000 records and this query is used for the paging, so waiting 4 minutes is not an option (that would take 27 days).
Note: I'm using the same Oracle jdbc driver in SQuirreL and my application so that is not the source of the problem.
I have also posted this to other web sites, e.g.
http://stackoverflow.com/questions/28896564/oracle-inconsistent-performance-behaviour-of-query