ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130815]12c Asynchronous Global Index Maintenance Part III.txt

[20130815]12c Asynchronous Global Index Maintenance Part III.txt

原创 Linux操作系统 作者:lfree 时间:2013-08-16 09:46:15 0 删除 编辑
[20130815]12c Asynchronous Global Index Maintenance Part III.txt

参考链接:
http://richardfoote.wordpress.com/2013/08/07/12c-asynchronous-global-index-maintenance-part-iii-re-makere-model/

更多的是重复作者的测试,加深理解:

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2627
  • 访问量
    6391309