Redo and Undo - Oracle Database Transactions and Locking Revealed - page 109

The query initially takes no I/Os to full-scan the table:

EODA@ORA12CR1> select * from t;
no rows selected

EODA@ORA12CR1> set autotrace traceonly statistics
EODA@ORA12CR1> select * from t;
no rows selected

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads

EODA@ORA12CR1> set autotrace off

Now, that might surprise you at first—especially if you are an Oracle user dating back to versions before Oracle 11 g
Release 2—that there are zero I/Os against the table. This is due to a new Oracle 11 g Release 2 feature—deferred
segment creation.

■ The deferred segment creation feature is available only with the enterprise edition of oracle. This feature is
enabled by default in oracle 11g release 2 and higher. You can override this default behavior when creating the table.

Note

If you run this example in older releases, you'll likely see three or so I/O's performed. We'll discuss that in a moment,
but for now let's continue this example. Next, we'll add lots of data to the table. We'll make it “grow,” then roll it all back:

EODA@ORA12CR1> insert into t select * from all_objects;
18371 rows created.

EODA@ORA12CR1> rollback;
Rollback complete.

Now, if we query the table again, we'll discover that it takes considerably more I/Os to read the table this time:

EODA@ORA12CR1> select * from t;
no rows selected

EODA@ORA12CR1> set autotrace traceonly statistics
EODA@ORA12CR1> select * from t;
no rows selected

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
317 consistent gets
0 physical reads

EODA@ORA12CR1> set autotrace off