How much performance will Oracle index reconstruction improve?

During work, we often observe the space release and Application Performance Improvement brought about by index reconstruction. Space release is easy to understand and measure, so index reconstruction will be applied

During work, we often observe the space release and Application Performance Improvement brought about by index reconstruction. Space release is easy to understand and measure, so index reconstruction will be applied

During work, we often observe the space release and Application Performance Improvement brought about by index reconstruction. Space release is easy to understand and measure. How much will index reconstruction affect the performance of applications? First, we will ask: why does index reconstruction improve performance? Undoubtedly, this is because I/O operations related to indexes are reduced after index reconstruction. To what extent does the reduction in index io affect the execution efficiency of application statements? I am afraid this requires a detailed analysis of the problem.

First, let's take a look at how index reconstruction works in most cases.

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;

The table has been created.

SQL> create index i1 on t1 (rn );

The index has been created.

SQL> analyze index i1 validate structure;

Index analyzed

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 rows have been deleted.

SQL> commit;

Submitted.

SQL> analyze index i1 validate structure;

Index analyzed

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;

Unselected row

Used time: 00: 00: 00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1704772559

Bytes ------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ------------------------------------------------------------------------------------
| 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 |
Bytes ------------------------------------------------------------------------------------

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

2-access ("RN" = 1)

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

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
-- 3 consistent gets
-- 3 physical reads
0 redo size
465 bytes sent via SQL * Net to client
508 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL & gt; select * from t1 where rn = 100;

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

Used time: 00: 00: 00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1704772559

Bytes ------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ------------------------------------------------------------------------------------
| 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 |
Bytes ------------------------------------------------------------------------------------

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

2-access ("RN" = 100)

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

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
-- 5 consistent gets
-- 1 physical reads
0 redo size
696 bytes sent via SQL * Net to client
519 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL & gt; select * from t1 where rn = 1000;