Re: Can a query reuse data???

From: <fitzjarrell_at_cox.net>
Date: 1 Aug 2006 13:29:37 -0700
Message-ID: <1154464177.814672.204600@p79g2000cwp.googlegroups.com>

epokopac_at_excite.com wrote:

> It goes back and fetchs the data.
>
> The result set MUST be a "true" snapshot of the data that has been
> committed when a query BEGINS.
>
> A previous result set would be a "false" snapshot of the data that has
> been committed when this current (new) query began.
>
> Hope this helps clear things up.

Unless, of course, the query is executed against tables in a read-only tablespace. The initial query will perform physical reads; all successive runs of the same query will not:

SQL> select *
2 from dept_ro;

DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Execution Plan

Plan hash value: 161506846

| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT_RO | 4 | 120 | 3 (0)| 00:00:01 |

Note

dynamic sampling used for this statement

Statistics

         68  recursive calls
          0  db block gets
         14  consistent gets
          5  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL> / DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Execution Plan

Plan hash value: 161506846

| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT_RO | 4 | 120 | 3 (0)| 00:00:01 |

Note

dynamic sampling used for this statement

Statistics

          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL> / DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Execution Plan

Plan hash value: 161506846

| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT_RO | 4 | 120 | 3 (0)| 00:00:01 |

Note

dynamic sampling used for this statement

Statistics

          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL> / DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Execution Plan

Plan hash value: 161506846

| 0 | SELECT STATEMENT | | 4 | 120 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT_RO | 4 | 120 | 3 (0)| 00:00:01 |

Note

dynamic sampling used for this statement

Statistics

          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

Since the tablespace (qtest in this case) is read-only no data changes can occur, thus the cached result set for the query being repeated is sufficient to satisfy the requests. In a lightly-loaded system this data may persist over several queries of different tables, and may not need to be refreshed.

I'll accept the fact that this is a contrived example, and one not considered by the OP. And, in the absence of read-only data, Oracle will return to the source to return a consistent result set, thus causing the table to be read again to either return changed rows or to verify no changes have occurred.

David Fitzjarrell Received on Tue Aug 01 2006 - 15:29:37 CDT