Re: Can a query reuse data???


epokopac@xxxxxxxxxx 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

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------
| 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

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------
| 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

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------
| 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

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------
| 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

.

Relevant Pages