Database Reference
In-Depth Information
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.
See Chapter 10 for further details.
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
