Oracle数据获取方式测试

首先建立实验环境

create table test as select * from dba_objects where =;

create index ind_test_id on test(object_id);

insert into test select * from dba_objects

where object_id is not null and object_id> order by object_id desc;

analyze table test compute statistics for table for all columns for all indexes;

Table Access Full

SQL> set autotrace trace;

SQL> select object_id from test;

set autotrace trace;

select object_id from test;

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| TABLE ACCESS FULL| TEST | | K | ()| :: |

注意这是因为object_id列默认是可以为null的如果修改成not null那么获取方式会变成什么方式?

Index Fast Full Scan

alter table test modify(object_id not null);

select object_id from test;

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| INDEX FAST FULL SCAN| IND_TEST_ID | | K| ()| :: |

Index Full Scan

select/*+ index(test ind_TEST_ID)*/ object_id from test;

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| INDEX FULL SCAN| IND_TEST_ID | | K| ()| :: |

Index Range Scan

select/*+ index(test ind_TEST_ID)*/ object_id from test where object_id < ;

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| INDEX RANGE SCAN| IND_TEST_ID | | K| ()| ::

SQL> select object_id from test where rownum<; INDEX FAST FULL SCAN

OBJECT_ID

已选择行

SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test where rownum<; INDEX FULL SCAN

OBJECT_ID

已选择行

select * from test where rownum < ;

其他的不关注只关注OBJECT_ID列