以下的文章主要是對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那麼獲取方式會變成什麼方式?
- 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 .......
