Closing ResultSet early invalidates result cache

When using the result cache (/*+ result_cache */) in a SQL during the query execution phase all the dependent objects will be identified for the query and will have the status PUBLISHED in result cache object view. But it is during the result fetch phase that the actual result will be cached. Status of the result set in the aforementioned view will be NEW when the first result is fetched and then on will be ByPass for subsequent row fetches for the same result set in the same session, once all the rows are fetched without an error status will be PUBLISHED.

If for some reason result set was closed half way through (or an error happens, which is not considered in this post) the result will have a status of invalid. Subsequent running of the sql will try to create a new result set to cache but will also end of invalid if result set is closed without being traversed the full length.

However if one session traverse the entire result set and subsequent sessions only traverse few rows of the result set, these latter sessions will be using the result cache.

Test case is give below.

1. Create the tables and functions needed

SQL> create table x (a number, b varchar2(100), c as (mod(a,4)));

Table created.

SQL> begin
  2  for i in 1 .. 1000
  3  loop
  4  insert into x (a,b) values (i,i||'abcdefg');
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

create or replace package rescachetest as
type ret_type is ref cursor;
function getvalues return ret_type;
end;
/

create or replace Package Body Rescachetest As

Function Getvalues Return Ret_Type Is

Ret_Val Ret_Type;
  Begin
    Open Ret_Val For
      Select /*+ result_cache */ * From X Where C In (3,4);
    
      return ret_val;
  end;
End;
/

This uses the result cache hint inside the pl/sql function which returns a ref cursor type. But it could be verified for pure sql as well. The java code used to test use be used to run both these cases.

2. Run the java code which will take only 90 rows from the result set and close the result set.

public class ResCacheTest {

    public static void main(String[] args) {
        try {
            OracleDataSource dataSource = new OracleDataSource();
            dataSource.setURL("jdbc:oracle:thin:@url");
            dataSource.setUser("username");
            dataSource.setPassword("password");

            Connection con = dataSource.getConnection();


            // PL/SQL
            CallableStatement clm = con.prepareCall("begin ? := rescachetest.getvalues; end;");
            clm.registerOutParameter(1, OracleTypes.CURSOR);
            clm.execute();


            // SQL
//            PreparedStatement pr = con.prepareStatement("select /*+ result_cache */ * from X Where C In (3,4)");

//            System.out.println("executed");
//            Thread.sleep(10000);

            ResultSet rs = ((OracleCallableStatement)clm).getCursor(1);

//            ResultSet rs = pr.executeQuery();
            int i = 0;
            while(rs.next()){

                i++;
                System.out.println(rs.getInt(1)+" "+rs.getString(2));

//                System.out.println("get result");
//                Thread.sleep(10000);
                if(i == 90){
                    break;
                }
            }

//            System.out.println("closing");
            rs.close();
            clm.close();
//            pr.close();
            con.close();
            dataSource.close();


        } catch (Exception ex) {
            Logger.getLogger(ResCacheTest.class.getName()).log(Level.SEVERE, null, ex);
        }
   }

}

3. ADMon has been used to query the v$result_cache_objects view.

When the clm.execute(); has been called observed the following
When the first row was taken from the result set
Subsequent row fetches
Once the result set was closed after taking 90 rows
4. If the java code was to run without closing the result set after taking 90 rows results will be published in the result cache.
Subsequent execution of java code where result set is closed after taking 90 rows will not result in creating additional invalid result caches as before, and these execution will use the already publish result cache.