Re: Difference between execute and fetch with a select-statement

Christian Hartmann <cha_at_christian-hartmann.de> wrote:
> Hi there,
>
> I should mention that I use an Oracle 9.2.0.4 database with
> OPTIMIZER_MODE=FIRST_ROWS.
>
> as for my understanding if I let run an SQL-Select-Statement "execute"
> does tis:
>
> The actual execution of the statement - For insert, update, and delete
> statements it writes the Before Image, writes the After Image, sets
> Dirty-Flag in Database-Buffer, and writes RedoLog Information. For
> select statements this identifies the selected rows.

For the select statement, execute doesn't identify the selected rows (in general, there may be cases where it does identify them). It does whatever initialization is necessary. It's lazy.

If the last step is a sort, then the entire row set is computed and sorted upon the first fetch, not upon execute. (Maybe the sort isn't even done to completion upon the first fetch, it could just sort enough to know what the first result is). If the execution path is such that the results can be computed incrementally, then only enough results are computed to satisfy the current fetch.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB