Database Reference
In-Depth Information
6 begin
7 delete from t;
8 commit;
9 end;
10 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> print x
USERNAME CREATED
------------------------------ ---------
DEVACCT 02-SEP-13
OPS$MELANIE 17-JUL-13
SCOTT 03-JUL-13
OPS$TKYTE 02-SEP-13
APEX_040200 28-JUN-13
APEX_PUBLIC_USER 28-JUN-13
...
AUDSYS 28-JUN-13
SYS 28-JUN-13
36 rows selected.
In this example, we created a test table,
T
, and loaded it with some data from the
ALL_USERS
table. We opened a
cursor on that table. We fetched
no data
from that cursor: we just opened it and have kept it open.
■
Bear in mind that oracle does not “pre-answer” the query. it does not copy the data anywhere when you
open a cursor—imagine how long it would take to open a cursor on a one-billion-row table if it did. the cursor opens
instantly and it answers the query as it goes along. in other words, the cursor just reads data from the table as you
fetch from it.
Note
In the same session (or maybe another session would do this; it would work as well), we proceed to delete all
data from the table. We even go as far as to
COMMIT
work on that delete action. The rows are gone—but are they? In
fact, they are retrievable via the cursor (or via a
FLASHBACK
query using the
AS OF
clause). The fact is that the resultset
returned to us by the
OPEN
command was preordained at the point in time we opened it. We had touched not a single
block of data in that table during the open, but the answer was already fixed in stone. We have no way of knowing
what the answer will be until we fetch the data; however, the result is immutable from our cursor's perspective. It is
not that Oracle copied all of the preceding data to some other location when we opened the cursor; it was actually the
DELETE
command that preserved our data for us by placing it (the before image copies of rows as they existed before
the
DELETE
) into a data area called an
undo
or
rollback segment
.
