以下的文章主要主要是对Oracle数据获取的实际方式的测试，我们首先是通过建立典型的实验环境，来对Oracle数据获取的实际操作来进行详细的说明，以下就是文章的具体内容描述，望你浏览之后会有所收获。
首先建立实验环境
- create table test as select * from dba_objects where 0=1;
- 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>10000 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 |
- 1 | TABLE ACCESS FULL| TEST | 58650 | 229K | 239 (1)| 00:00:03 |
注意这是因为object_id列默认是可以为null的,如果修改成not null那么Oracle数据获取方式会变成什么方式?
- 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 |
- 1 | INDEX FAST FULL SCAN| IND_TEST_ID | 58650 | 229K| 66 (0)| 00:00:01 |
- Index Full Scan
- select/*+ index(test ind_TEST_ID)*/ object_id from test;
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- 1 | INDEX FULL SCAN| IND_TEST_ID | 58650 | 229K| 240 (1)| 00:00:03 |
- Index Range Scan
- select/*+ index(test ind_TEST_ID)*/ object_id from test where object_id < 68926;
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- 1 | INDEX RANGE SCAN| IND_TEST_ID | 57903 | 226K| 237 (1)| 00:00:03
- SQL> select object_id from test where rownum<11; INDEX FAST FULL SCAN
- OBJECT_ID
- 68917
- 68918
- 68919
- 68920
- 68921
- 68922
- 68923
- 68924
- 68925
- 68926
已选择10行。
- SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test where rownum<11; INDEX FULL SCAN
- OBJECT_ID
- 10001
- 10002
- 10003
- 10004
- 10005
- 10006
- 10007
- 10008
- 10009
- 10010
已选择10行。
- select * from test where rownum < 2;
- ....... 69554 .......
其他的不关注只关注OBJECT_ID列 。以上的相关内容就是对@@@@@@@的介绍，望你能有所收获。
【编辑推荐】
