本文共 2857 字，大约阅读时间需要 9 分钟。

Oracle?????????????????????

?????????????????????Oracle?????????????????????????????????????????????????????

????

1. ??????????

?????????????test???dba_objects????????

create table test as select * from dba_objects where 1 != 1;

???????????test????????idx_test_id???object_id???

create index idx_test_id on test(object_id);

2. ???????

??????dba_objects????object_id??1000?????????????object_id?????

insert into test select * from dba_objects where object_id is not null and object_id > 1000 order by object_id desc;

????????????????????????

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

3. ?????????

???????????????????10??????????????

select object_id from test where rownum < 11;

?????????????00:00:01???????INDEX FULL SCAN???

4. ?????

??????????????????????????????????????dump?

alter session set events 'immediate trace name treedump level 75981';

?????dump???????idx_test_id????????????????????INDEX FULL SCAN?????????????

????????

????????????????

Plan hash value: 3931117773--------------------------------------------------------------------------------| Id | Operation      | Name             | Rows | Bytes | Cost (%CPU)| Time ||----|---------------|-----------------|-----|------|------------|------|| 0  | SELECT STATEMENT|                  | 10  | 40   | 2 (0)     | 00:00:01 || 1  | COUNT STOPKEY |                  |    |      |            |      || 2  | TABLE ACCESS FULL| TEST              | 10  | 40   | 2 (0)     | 00:00:01 |--------------------------------------------------------------------------------

???????????

Predicate Information (identified by operation id):1 - filter(ROWNUM < 11)

???????

?????????????????????idx_test_id?????????????????????????????????????????????????????????

select /*+ index(test idx_test_id) */object_id from test where rownum < 11;

????????????

Plan hash value: 1062221432--------------------------------------------------------------------------------| Id | Operation      | Name             | Rows | Bytes | Cost (%CPU)| Time ||----|---------------|-----------------|-----|------|------------|------|| 0  | SELECT STATEMENT|                  | 10  | 40   | 2 (0)     | 00:00:01 || 1  | COUNT STOPKEY |                  |    |      |            |      || 2  | INDEX FULL SCAN | IDX_TEST_ID       | 10  | 40   | 2 (0)     | 00:00:01 |--------------------------------------------------------------------------------

?????????INDEX FULL SCAN??????????????

???????

?????????????????????

?????????????????????????????00:00:01???00:00:00.5???????????

??

???????????????Oracle???????????00:00:01????00:00:00.5??????????????????????????????????????????????????????????