Oracle索引重建到底會提高多少效能？

工作中往往會觀察到索引重建帶來的空間釋放和應用效能提升。空間釋放比較容易理解，也非常容易度量，那麼索引重建到底會對應用的效能有多少影響那？首先我們會問：索引重建為什麼會帶來效能的提升？毫無疑問，這是因為索引重建後，與索引有關的io操作得到了降低。那麼，索引io的降低在多大程度上影響了應用語句的執行效率？這恐怕需要具體問題具體分析了。

首先，我們來看一下多數情況下，索引重建的效果如何

SQL> create table t1 as select rownum rn,dbms_random.string('u',20) name1,dbms_random.string('u',15) name2 from dual connect by level < 1000000;

表已建立。

SQL> create index i1 on t1(rn);

索引已建立。

SQL> analyze index i1 validate structure;

索引已分析

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    999999  0 2226        0  16006445    90

SQL> delete from t1 where mod(rn,2) =1;

已刪除500000行。

SQL> commit;

提交完成。

SQL> analyze index i1 validate structure;

索引已分析

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    943027    443028 2226  443028  15094893    85

SQL> set timing on
SQL> set autotrace on

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)

統計資訊
----------------------------------------------------------
  0  recursive calls
  0  db block gets
 --  3  consistent gets
 --  3  physical reads
  0  redo size
 465  bytes sent via SQL*Net to client
 508  bytes received via SQL*Net from client
  1  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  0  rows processed

SQL> select * from t1 where rn=100;

 RN
----------
NAME1
----------------------------------------------------------------------------------------------------
NAME2
----------------------------------------------------------------------------------------------------
      100
IWKRROMDHLNJMXVQYRHE
VPTNTMMUJYJJQCM

經過時間:  00: 00: 00.00

執行計畫
----------------------------------------------------------
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"=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)

統計資訊
----------------------------------------------------------
  0  recursive calls
  0  db block gets
 --  3  consistent gets
 --  3  physical reads
  0  redo size
 465  bytes sent via SQL*Net to client
 508  bytes received via SQL*Net from client
  1  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  0  rows processed

SQL> select * from t1 where rn=100;

 RN
----------
NAME1
----------------------------------------------------------------------------------------------------
NAME2
----------------------------------------------------------------------------------------------------
      100
IWKRROMDHLNJMXVQYRHE
VPTNTMMUJYJJQCM

經過時間:  00: 00: 00.00

執行計畫
----------------------------------------------------------
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"=100)

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> select count(name1) from t1 where rn<100;

COUNT(NAME1)
------------
  49

經過時間:  00: 00: 00.00

執行計畫
----------------------------------------------------------
Plan hash value: 3625400295

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

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

  3 - access("RN"<100)

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

統計資訊
----------------------------------------------------------
  0  recursive calls
  0  db block gets
 --  4  consistent gets --
 --  4  physical reads --
  0  redo size
 531  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

更多詳情見請繼續閱讀下一頁的精彩內容：

由Oracle索引來理解ArcSDE索引

Oracle索引技術之如何建立最佳索引

Oracle索引列NULL值引發執行計畫該表的測試樣本

Oracle索引 主鍵影響查詢速度

Oracle索引掃描

1 2 下一頁