Optimize the speed on performing "select" query in a big loop

Question

I'm performing a simple 'select' query in a Java loop as what is shown below. The size of the List can grow up to 10000+. How can I improve the query speed? Any example or advice is appreciated. Thanks.

Do note that I need to retrieve all data in every column of that table, so that's why the asterisk (*) is used.

List<String> valueList = ....
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    conn = DriverManager.getConnection(dbURL, dbUsername, dbPassword);
    for (int m = 0; m < valueList.size() ; m++) {    
         String sql = "SELECT * FROM WORKSHEET WHERE " + sheetId + " = '" +    
                      valueList.get(m) + "'";
         ps = conn.prepareStatement(sql);
         rs = ps.executeQuery();
         // retreive data....           
    }
}

Edit: At the end, there are a few ways to speed this query up. I'm using the second way as it prevent ORA-04031 error in future.

1. Use parameterized 'SELECT' query with 'IN' clause.
2. Create a Nested table and cast array/list of items that comes from JDBC to the created Nested table.
3. Create a temporary table and insert the list of items. Then perform a JOIN to the main table (1 query) and get the results.

Answer 1

There are two things to consider when trying to speed this up:

1. Execute this query only once for all sheetid's
2. Make sure you are executing the same query each time, by not hardcoding the values in. Since these values possibly change, each query will look like the previous query, but with only a few different values. This won't allow Oracle to re-use the previous query and leads to non sharable SQL in the shared pool. This will fill up the shared pool. Do this long enough and you'll receive ORA-04031 error messages.

The way to go is to use SQL types. Here is an example in PL/SQL. You can use the same principle in Java.

First create a table with ten thousand sheetId's:

SQL> create table worksheet (sheetid)
  2  as
  3   select level
  4     from dual
  5  connect by level <= 10000
  6  /

Table created.

Create a SQL type:

SQL> create type mynumbers is table of number;
  2  /

Type created.

In your code, fill an instance of the SQL type with the values in your "valuelist" and use the TABLE operator to transform the type to table values:

SQL> declare
  2    valuelist mynumbers := mynumbers(23,124,987,6123,8923,1,7139);
  3  begin
  4    for r in
  5    ( select ws.sheetid
  6        from worksheet ws
  7           , table(valuelist) vl
  8       where ws.sheetid = vl.column_value
  9    )
 10    loop
 11      dbms_output.put_line(r.sheetid);
 12    end loop;
 13  end;
 14  /
1
23
124
987
6123
7139
8923

PL/SQL procedure successfully completed.

Now you have just one SQL in your shared pool and just one execution of this query, instead of thousands.