create table fact
partition by range (dt)
   interval ( numtodsinterval(1, 'DAY') )
   (partition empty values less than (to_date('01-01-2001', 'dd.mm.yyyy')))
as
select * from big_tbl where dt = date '2012-09-11';

insert into fact select * from big_tbl where dt = date '2012-09-10';

commit;

select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-10');
select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-11');
select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-10',date '2012-09-11');

select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-10');
select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-11');
select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-10',date '2012-09-11');

select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-10');
select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-11');
select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-10',date '2012-09-11');

SQL> select sql_text
  2        ,elapsed_time
  3        ,executions
  4        ,buffer_gets
  5        ,disk_reads
  6    from v$sql
  7   where sql_text like '%tag$%' and not sql_text like '%v$sql%';

SQL_TEXT                                                                                        ELAPSED_TIME EXECUTIONS BUFFER_GETS DISK_READS
----------------------------------------------------------------------------------------------- ------------ ---------- ----------- ----------
select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-11')                                2353094          3      174253     174388
select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-10')                                1574495          3      200634          0
select /*+ tag$ */ count(*) from fact where dt in (date '2012-09-10',date '2012-09-11')              4853966          3      374727     174278

SQL> create table tst(n1 char(2000), n2 char(2000), n3 char(2000)) nologging;

Table created.

SQL>
SQL> insert /*+ append */   into tst select 1,1,1 from dual connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.


SQL> alter table tst add n4 char(2000) default '1';

Table altered.

SQL> alter system flush buffer_cache;

System altered.

QL> set timing on
SQL> select count(n4) from tst;

 COUNT(N4)
----------
     10000

Elapsed: 00:00:07.16


SQL> select EVENT, TOTAL_WAITS from v$session_event where sid = (select sid from v$mystat where rownum = 1) and total_waits != 0;

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
Disk file operations I/O                                                   2
enq: KO - fast object checkpoint                                           1
db file sequential read                                                10047
db file scattered read                                                    38
direct path read                                                          79
SQL*Net message to client                                                  9
SQL*Net message from client                                                8
events in waitclass Other                                                 19

8 rows selected.

Elapsed: 00:00:00.03
SQL> select name, value from v$mystat a, v$statname b where a.statistic# = b.statistic# and name = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                              9999

SQL> set timing on
SQL> select count(*) from tst;

  COUNT(*)
----------
     10000

Elapsed: 00:00:02.06
SQL> select name, value from v$mystat a, v$statname b where a.statistic# = b.statistic# and name = 'table fetch continued row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                                 0

Elapsed: 00:00:00.00
SQL> select EVENT, TOTAL_WAITS from v$session_event where sid = (select sid from v$mystat where rownum = 1) and total_waits != 0;

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
Disk file operations I/O                                                   2
enq: KO - fast object checkpoint                                           1
db file sequential read                                                   41
db file scattered read                                                    38
direct path read                                                         157
SQL*Net message to client                                                 11
SQL*Net message from client                                               10
events in waitclass Other                                                  1

8 rows selected.