以下的文章主要主要是對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列 。以上的相關內容就是對@@@@@@@的介紹，望你能有所收獲。
