SQL> create table sample nologging tablespace users as select rownum t1  from dual  connect by level<=900000;Table created.SQL> alter table sample add t2 number;Table altered.update sample set t2=dbms_random.value(1,999999);900000 rows updated.SQL> commit;Commit complete.SQL> create index ind_t1 on sample(t1) nologging tablespace users;Index created.SQL> create index ind_t2 on sample(t2) nologging tablespace users;Index created.SQL> exec dbms_stats.gather_table_stats(USER,'SAMPLE',cascade=>TRUE);PL/SQL procedure successfully completed.SQL> select blocks,NUM_ROWS from dba_tables where table_name='SAMPLE';    BLOCKS   NUM_ROWS---------- ----------      9107     902319SQL> select CLUSTERING_FACTOR,LEAF_BLOCKS,DISTINCT_KEYS,index_name from dba_indexes where table_name='SAMPLE';CLUSTERING_FACTOR LEAF_BLOCKS DISTINCT_KEYS INDEX_NAME----------------- ----------- ------------- ------------------------------             1370        2004        900000 IND_T1           899317        4148        900000 IND_T2alter session set events '10046 trace name context forever,level 12';set autotrace traceonly;alter system flush buffer_cache;alter session set "_optimizer_batch_table_access_by_rowid"=true; select /*+ index(sample ind_t2) */ * from sample where t2 between 1 and 999997; select /*+ index(sample ind_t2) */ *from sample where t2 between 1 and 999997call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch    60001      4.68       8.56      12754    1810330          0      899999------- ------  -------- ---------- ---------- ---------- ----------  ----------total    60003      4.68       8.56      12754    1810330          0      899999Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: SYSNumber of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max)  Row Source Operation---------- ---------- ----------  ---------------------------------------------------    899999     899999     899999  TABLE ACCESS BY INDEX ROWID BATCHED SAMPLE (cr=1810330 pr=12754 pw=0 time=20413784 us cost=903657 size=24300000 card=900000)    899999     899999     899999   INDEX RANGE SCAN IND_T2 (cr=63873 pr=4150 pw=0 time=4655140 us cost=4155 size=0 card=900000)(object id 92322)Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  SQL*Net message to client                   60001        0.00          0.32  Disk file operations I/O                        1        0.00          0.00  db file sequential read                     11388        0.00          1.70  SQL*Net message from client                 60001        0.00          8.95  db file parallel read                         197        0.00          0.00 alter system flush buffer_cache;alter session set "_optimizer_batch_table_access_by_rowid"=false; select /*+ index(sample ind_t2) */ * from sample where t2 between 1 and 999997; select /*+ index(sample ind_t2) */ *from sample where t2 between 1 and 999997call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch    60001      4.70       8.82      12754    1810333          0      899999------- ------  -------- ---------- ---------- ---------- ----------  ----------total    60003      4.70       8.82      12754    1810333          0      899999Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: SYSNumber of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max)  Row Source Operation---------- ---------- ----------  ---------------------------------------------------    899999     899999     899999  TABLE ACCESS BY INDEX ROWID SAMPLE (cr=1810333 pr=12754 pw=0 time=25464232 us cost=903657 size=24300000 card=900000)    899999     899999     899999   INDEX RANGE SCAN IND_T2 (cr=63874 pr=4150 pw=0 time=4404956 us cost=4155 size=0 card=900000)(object id 92322)Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  SQL*Net message to client                   60001        0.00          0.32  db file sequential read                     12754        0.00          1.85  SQL*Net message from client                 60001        0.00          8.95