oracle重建索引需要多长时间,Oracle索引重建到底会提高多少性能？-365比分足球官网-365比分足球官网-365app下载365足球网站-365比分网APP

工作中往往会观察到索引重建带来的空间释放和应用性能提升。空间释放比较容易理解，也非常容易度量，那么索引重建到底会对应用的性能有多少影响那？首先我们会问：索引重建为什么会带来性能的提升？毫无疑问，这是因为索引重建后，与索引有关的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

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 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;

----------

NAME1

----------------------------------------------------------------------------------------------------

NAME2

----------------------------------------------------------------------------------------------------

100

IWKRROMDHLNJMXVQYRHE

VPTNTMMUJYJJQCM

已用时间: 00: 00: 00.00

执行计划

----------------------------------------------------------

Plan hash value: 1704772559

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 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;

----------

NAME1

----------------------------------------------------------------------------------------------------

NAME2

----------------------------------------------------------------------------------------------------

1000

YTGFFEROGABGKFKQENMW

LBERYHDTRMAWGHV

已用时间: 00: 00: 00.01

执行计划

----------------------------------------------------------

Plan hash value: 1704772559

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 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;

----------

NAME1

----------------------------------------------------------------------------------------------------

NAME2

----------------------------------------------------------------------------------------------------

100

IWKRROMDHLNJMXVQYRHE

VPTNTMMUJYJJQCM

已用时间: 00: 00: 00.00

执行计划

----------------------------------------------------------

Plan hash value: 1704772559

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 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)

------------

已用时间: 00: 00: 00.00

执行计划

----------------------------------------------------------

Plan hash value: 3625400295

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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重建索引需要多长时间,Oracle索引重建到底会提高多少性能？

相关文章

问胸围36b是多少厘米

dnf剑魂带鱼雕好吗,DNF国服剑魂值不值得玩

dnf国服110级版本什么时候更新国服110级版本更新时间介绍

超忆症是否有治愈的可能

同道链接

相关文章

问胸围36b是多少厘米

dnf剑魂带鱼雕好吗,DNF国服剑魂值不值得玩

dnf国服110级版本什么时候更新 国服110级版本更新时间介绍

超忆症是否有治愈的可能

同道链接

dnf国服110级版本什么时候更新国服110级版本更新时间介绍