更新时间:2022-09-06 23:18:38
SQL> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SQL> select index_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE';
INDEX_NAME NUM_ROWS BLOCKS LEAF_BLOCKS STATUS ORPHANED_ENTRIES
-------------------- ---------- ---------- ----------- -------- --------------------
MUSE_ID_I 3000000 9216 8216 VALID YES
--ORPHANED_ENTRIES=yes.
SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;
NAME BLOCKS LF_ROWS DEL_LF_ROWS
-------------------- ---------- ---------- -----------
MUSE_ID_I 9216 3000000 1000000
SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;
NAME BLOCKS LF_ROWS DEL_LF_ROWS
-------------------- ---------- ---------- -----------
MUSE_ID_I 9216 3000000 999990
SQL> set autot traceonly ;
SQL> select * from muse where id between 1 and 100;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2515419874
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE | 1 | 23 | 4 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | MUSE_ID_I | 100 | | 3 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"
filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)--也就是讲,对于唯一索引,oracle在插入旧键值是会重用原来的块。
SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;
NAME BLOCKS LF_ROWS DEL_LF_ROWS
-------------------- ---------- ---------- -----------
MUSE_ID_I 11264 4000000 999990