Oracle OCI - How to obtain the number of rows in the select set without fetching

Question

Noob here (with regards to Oracle and OCI).

I am writing a program to do some tasks for my company. Amongst the tasks, I have to update an oracle database to keep it in synch with other actions.

I want to pre-allocate memory efficiently to hold the results of fetching on the select set. In order to do that, I need to know how many rows are in the select set.

I could do it with 2 separate statements; first a SELECT COUNT statement, followed by a SELECT statement, but that is inefficient from a server point of view. I could also just execute my SELECT statement and dynamically allocate memory as rows are fetched from the select set, but that is inefficient from a client point of view.

I want to simply execute the SELECT statement and, before fetching any rows, retrieve the number of matching rows in the select set.

But I cannot locate an attribute that has this information.
There is OCI_ATTR_ROW_COUNT but this is the number of rows FETCHED, not the total number of rows in the select set. Then there is OCI_ATTR_PARAM_COUNT which tells you the number of COLUMNS (seems useless because you already know how many columns you asked for in your SQL).

Does anybody know how to obtain the select set row count prior to fetching?

Thanks Josh

Answer 1

You can't. Oracle the database generally doesn't know how many rows a query will return until after it has fetched the last row.

Normally, the client would be designed to fetch particularly sized batches of data from the client, process those results, and then fetch the next batch of data. Most PL/SQL GUI applications (TOAD, SQL Developer, etc.) for example, will execute a query, fetch the first batch of 50 or 100 rows, and then wait for the user. As the user pages through data, the client will fetch the next batch, process that, and iterate until the last batch is consumed. Whether you can discard the prior batches from memory or whether you need to hold on to them (to support a user's ability to scroll backwards, for example, without re-executing the query) is something that your application would need to determine.

What inefficiency are you worried about when you're concerned about dynamically allocating memory on the client? That's normally the most efficient way of doing things. Assuming you pick a reasonable batch size, you'll be requesting reasonably sized chunks of memory on the client (either once or one for each batch depending on whether you need to cache the data locally) which is pretty efficient.