Oracle Database Discussions

Hi,

As first_rows and all_rows are explained:

first_rows and all_rows are CBO only modes, this is true.

In choose mode, the optimizer will be closer to an all_rows optimization.

First_rows attempts to optimize the query to get the very first row back to the client as
fast as possible. This is good for an interactive client server environment where the
client runs a query and shows the user the first 10 rows or so and waits for them to page
down to get more.

All_rows attempts to optimize the query to get the very last row as fast as possible.
This makes sense in a stored procedure for example where the client does not regain
control until the stored procedure completes. You don't care if you have to wait to get
the first row if the last row gets back to you twice as fast. In a client
server/interactive application you may well care about that.

For example I code with Java.
If I code like this:
List guys = new ArrayList();
PreparedStatement ps = conn.prepareStatement("select name, age from guy where ....");
...
ResultSet rs = ps.executeQuery();
while (rs.hasNext()) {
Guy g = new Guy();
g.setName(rs.getString(1));
g.setAge(rs.getInt(2));
guys.add(g);
}

I think that all_rows is better than first_rows, because it needs to get all records with this query.

If I set first_rows to get records as fast as possible, my question is whether I need to change the code to not open the cursor and return ResultSet to client who is responsible to render the information in UI.

PreparedStatement ps = conn.prepareStatement("select name, age from guy where ....");
...
return ps.executeQuery();

Regards,
Nesta