CODEWITHSUNDEEP
c#oracle-database
Thomas Carlton
Thomas Carlton

Reputation: 5968

Oracle : How to immediately get the first results from slow queries?

I'm writing a C# application that should run an Oracle-Select query and perform some calculations for each line.

The select query is very big and takes a long time.

In the current application design, I should wait until the query finishes retrieving all the data from the database in order to start the required computations on each row.

I was wondering if there is a way to get the first query results as the database engine find them.

Means that : Instead of waiting for the database engine to find all the rows that correspond to my query and return them, get the result since the first row found by the database engine.

At the end the computation required for each line will start as long as the first line found in the database and hence the total run time will be less.

The idea here is not about how to speed up an Oracle query or adding any index. It's more about getting overlapping computations to optimize more the computations.

Sorry if it's a dump question and thank you in advance.

I'm using Oracle 11g and the Query may just be as simple as (but returns hundreds of thousands of rows)

Select * from Table Where Condition1;

I run the explain plan for my query :

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   251 |   122K|   656K  (1)| 00:07:40 |
|*  1 |  TABLE ACCESS FULL| TABLE1  |   251 |   122K|   656K  (1)| 00:07:40 |
-----------------------------------------------------------------------------

Upvotes: 0

Views: 821

Answers (1)

Mark J. Bobak
Mark J. Bobak

Reputation: 14385

Oracle has an all rows strategy and a first rows strategy.

Usually, Oracle will, when possible, do a first rows strategy when possible. The simplest example of that would be something like:

select * from emp;

Here, there is no join, there's no sorting, etc, so, Oracle will begin to return rows immediately, as it reads through the EMP table.

On the other hand, this is a simple example of an all rows strategy:

select * from emp order by surname;

Here, we're asking for sort on SURNAME, so, we cannot begin to immediately return results. The table must be read in its entirety, and then sorted, before we can return the first row.

There are other factors as well. If you're joining tables, a NESTED LOOPS join will execute with a first rows strategy, whereas a HASH JOIN will (necessarily) employ an all rows strategy.

Ultimately, which is better, which you will want, is going to be dependent on your application. If you're doing stuff that the user directly interacts with, you'll probably want first rows, to not keep the user waiting. For batch jobs, all rows is (probably) better.

Finally, the optimizer can be influenced with the ALL_ROWS and FIRST_ROWS_n hints.

Upvotes: 1

Related Questions