Oracle索引重建到底会提高多少性能？(二)

2 - access("RN"=100)

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
-- 5 consistent gets
-- 1 physical reads
0 redo size
696 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select * from t1 where rn=1000;

RN
----------
NAME1
----------------------------------------------------------------------------------------------------
NAME2
----------------------------------------------------------------------------------------------------
1000
YTGFFEROGABGKFKQENMW
LBERYHDTRMAWGHV

已用时间: 00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4017 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 4017 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1 | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("RN"=1000)

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
-- 5 consistent gets
-- 4 physical reads
0 redo size
696 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter index i1 rebuild online;

索引已更改。

已用时间: 00: 00: 05.41
SQL> analyze index i1 validate structure;

索引已分析

已用时间: 00: 00: 00.22
SQL> select height,lf_rows,del_lf_rows,lf_blks,del_lf_rows btree_space,used_space,pct_used from index_stats;

HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ----------- ---------- ----------- ---------- ----------
3 499999 0 1113 0 7998149 90

已用时间: 00: 00: 00.03

SQL> select * from t1 where rn=1;

未选定行

已用时间: 00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4017 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 4017 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("RN"=1)

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
-----------------------------------------