oracle query performance in java

Question

My Scenario is i have big query with lot of joins and lot of decode/case calls in select and i am passing one param to where condition from java and i see for 150000 rows java fetch is very slow but query is running faster in SQL developer client interface.

i thought of creating or replacing a view which takes one parameter and call that view from java.

Did not find resource to know how to pass prams to create or replace view statement from java ?

Any one suggest other approach that fetches rows quickly ?

Using oracle 12c and driver is jdbc7 and jdk8

Answer 1

First (and easiest):

Set the JDBC fetch size to a high number in your statement. There is a setFetchSize(int) method on Statement, PreparedStatement, CallableStatement, and ResultSet objects. This defaults to something small like 10 rows. Set that to a reasonably high number, such as 500 or more.

This is a setting that will definitely slow down a query that pulls back hundreds of thousands of records.

Second:

Verify that the query is indeed running fast in SQL Developer, to the last row. You can export to a file or try wrapping the query in a PL/SQL statement that will loop through all records.

If you wish, you can use AUTOTRACE in SQL*Plus to your advantage:

SET TIMING ON
SET AUTOTRACE TRACEONLY
<your query>

This will run the query to the end, pulling all records over the network but not displaying them. The goal here is to prove that your SQL statement is indeed returning all records as quickly as needed.

If not, then you have a standard tuning exercise. Get it running to completion quickly in SQL Developer first.