[20130815]12c Asynchronous Global Index Maintenance Part III.txt
[20130815]12c Asynchronous Global Index Maintenance Part III.txt
參考連結:
更多的是重複作者的測試,加深理解:
1.測試環境:
2.測試建立:
SQL> create table muse (id number, code number, name varchar2(30))
partition by range (id)
(partition muse1 values less than (1000001),
partition muse2 values less than (2000001),
partition muse3 values less than (maxvalue));
SQL> insert into muse
select rownum, mod(rownum,100000), 'DAVID BOWIE' from
(select 1 from dual connect by level <= 3000) a,
(select 1 from dual connect by level<=1000) b;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');
SQL> create unique index muse_id_i on muse(id);
Index created.
SQL> alter table muse drop partition muse1 update global indexes;
Table altered.
SQL> analyze index muse_id_i validate structure;
Index analyzed.
SQL> insert into muse select rownum, 42, 'ZIGGY STARDUST' from dual connect by level <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL> analyze index muse_id_i validate structure;
Index analyzed.
--可以DEL_LF_ROWS=9999990,有10條記錄插入原來刪除的位置。
--插入不再刪除範圍的情況呢?
SQL> insert into muse select rownum+3000000, 42, 'ZIGGY STARDUST' from dual connect by level <= 1000000;
SQL> commit;
SQL> analyze index muse_id_i validate structure;
Index analyzed.
--可以發現,插入不再刪除範圍的值不會重用原來的塊。
We notice that the number of so-called deleted leaf entries remains the same after inserting the 1M new rows.
So in this scenario, the effectively "empty" leaf blocks containing nothing but orphaned unique index entries are
not re-cycled and reused by subsequent index block splits as they would have been had they contained nothing but deleted
index entries.
So Unique indexes in the unlikely event that such unique values are subsequently reinserted are an exception to the
general rule of orphaned global index entries having to be "cleaned out".
參考連結:
更多的是重複作者的測試,加深理解:
1.測試環境:
SQL> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
2.測試建立:
SQL> create table muse (id number, code number, name varchar2(30))
partition by range (id)
(partition muse1 values less than (1000001),
partition muse2 values less than (2000001),
partition muse3 values less than (maxvalue));
SQL> insert into muse
select rownum, mod(rownum,100000), 'DAVID BOWIE' from
(select 1 from dual connect by level <= 3000) a,
(select 1 from dual connect by level<=1000) b;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');
SQL> create unique index muse_id_i on muse(id);
Index created.
SQL> alter table muse drop partition muse1 update global indexes;
Table altered.
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> analyze index muse_id_i validate structure;
Index analyzed.
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> insert into muse select rownum, 42, 'ZIGGY STARDUST' from dual connect by level <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL> analyze index muse_id_i validate structure;
Index analyzed.
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
--可以DEL_LF_ROWS=9999990,有10條記錄插入原來刪除的位置。
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"<=100)
filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)--也就是講,對於唯一索引,oracle在插入舊鍵值是會重用原來的塊。
--插入不再刪除範圍的情況呢?
SQL> insert into muse select rownum+3000000, 42, 'ZIGGY STARDUST' from dual connect by level <= 1000000;
SQL> commit;
SQL> analyze index muse_id_i validate structure;
Index analyzed.
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
--可以發現,插入不再刪除範圍的值不會重用原來的塊。
We notice that the number of so-called deleted leaf entries remains the same after inserting the 1M new rows.
So in this scenario, the effectively "empty" leaf blocks containing nothing but orphaned unique index entries are
not re-cycled and reused by subsequent index block splits as they would have been had they contained nothing but deleted
index entries.
So Unique indexes in the unlikely event that such unique values are subsequently reinserted are an exception to the
general rule of orphaned global index entries having to be "cleaned out".
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-768640/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20130815]12c Asynchronous Global Index Maintenance Part II.txtIndexAINaN
- [20190920]Asynchronous Global Index Maintenance and Recycle Bin.txtIndexAINaN
- Maintenance of Global Partitioned IndexesAINaNIndex
- [20130813]Global Index Maintenance 11G.txtIndexAINaN
- global index & local index的區別Index
- 【Oracle】global index & local index的區別OracleIndex
- PostgreSQL DBA(170) - pgAdmin(Queries For Index Maintenance)SQLIndexAINaN
- 建立檢視和淺析LOCAL INDEX和GLOBAL INDEXIndex
- partition_global index補疑(一)Index
- 那上邊的到底是 global index還是partition indexIndex
- 分割槽索引之本地(local index)索引和全域性索引(global index)索引Index
- 12c 新特性之 Limiting the Size of the Program Global AreaMIT
- Asynchronous CommitMIT
- 非同步模式(Asynchronous)非同步模式
- 4、關於global nopartition index和普通index的概念區別,我認為是global nopartition index是它的底層表必須是分割槽,此時它才有這個概念意思,而普通indIndex
- Oracle 12c新特性之——TABLE ACCESS BY INDEX ROWID BATCHEDOracleIndexBAT
- Simple FSM 3(asynchronous reset)
- 2.3.3 Application MaintenanceAPPAINaN
- ALLOCATE CHANNEL FOR MAINTENANCE 命令AINaN
- Give root password for maintenanceAINaN
- SQL Database Maintenance WizardSQLDatabaseAINaN
- ID3d11asynchronous3D
- 非同步提交(Asynchronous COMMIT)非同步MIT
- Simple FSM1(asynchronous reset)
- Simple FSM2(asynchronous reset)
- 2.3.3.1 About Application MaintenanceAPPAINaN
- 11g auto maintenanceAINaN
- Cannot find folder "Maintenance Plans".AINaN
- Three commands for OCR maintenanceAINaN
- index leaf block索引葉子塊何時會刪除—part1IndexBloC索引
- asynchronous i/o (aio) on HP-UXAIUX
- Dynamics CRM Asynchronous Service Performance: Code ManiaORM
- Taming the asynchronous beast with ES7AST
- [20181127]12c Advanced Index Compression.txtIndex
- SCSS !globalCSS
- three predefined automated maintenance tasksAINaN
- 上海:International Release and Maintenance EngineerAINaN
- Recovery Catalog creation and MaintenanceAINaN