[20130813]Global Index Maintenance 11G.txt

参考链接：
http://richardfoote.wordpress.com/2013/07/26/global-index-maintenance-pre-12c-unwashed-and-somewhat-slightly-dazed/

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

1.测试环境:

SQL> @ver

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

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;

commit;

exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

create index muse_id_i on muse(id);

create index muse_code_i on muse(code) global partition by range(code)
(partition code_p1 values less than (50000), partition code_p2 values less than (maxvalue));

SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status

from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO'

union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status

from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS

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

MUSE_CODE_I                    CODE_P1                           1500000       4224        4137 USABLE

MUSE_CODE_I                    CODE_P2                           1500000       4352        4177 USABLE

MUSE_ID_I                                                        3000000       9216        8633 VALID

SQL> select n.name, s.value from v$mystat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') ;

NAME                      VALUE

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

db block gets                 3

redo size                   776

SQL> alter table muse drop partition muse1;
Table altered.

SQL> select n.name, s.value from v$mystat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') ;

NAME                      VALUE

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

db block gets                71

redo size                 14240

-- 删除分区索引很快。

SQL> select index_name, null partition_name, num_rows, leaf_blocks, status

from dba_indexes i where table_name='MUSE' and partitioned = 'NO'

union select index_name, i.partition_name, num_rows, leaf_blocks, status

from dba_ind_partitions i where index_name like 'MUSE%';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS LEAF_BLOCKS STATUS

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

MUSE_CODE_I                    CODE_P1                           1500000        4137 UNUSABLE

MUSE_CODE_I                    CODE_P2                           1500000        4177 UNUSABLE

MUSE_ID_I                                                        3000000        8633 UNUSABLE

--但是索引全部失效。redo size也很小。

3.采用另外的方式，要重新建表看看。

SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status

from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO'

union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status

from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS

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

MUSE_CODE_I                    CODE_P1                           1500000       4224        4137 USABLE

MUSE_CODE_I                    CODE_P2                           1500000       4352        4177 USABLE

MUSE_ID_I                                                        3000000       9216        8633 VALID

SQL> select n.name, s.value from v$mystat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') ;

NAME                      VALUE

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

db block gets                 3

redo size                   776

SQL> alter table muse drop partition muse1 update global indexes;

SQL> select n.name, s.value from v$mystat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') ;

NAME                      VALUE

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

db block gets             56376

redo size              44149272

--可以发现产生的redo size很多。

SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status 

from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO'

union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status 

from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS

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

MUSE_CODE_I                    CODE_P1                           1500000       4224        4137 USABLE

MUSE_CODE_I                    CODE_P2                           1500000       4352        4177 USABLE

MUSE_ID_I                                                        3000000       9216        8633 VALID

--但是索引保持有效。

SQL> set autot traceonly ;

SQL> select min(id) from muse;

Execution Plan

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

Plan hash value: 2104594370

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

| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT           |           |     1 |     6 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE            |           |     1 |     6 |            |          |

|   2 |   INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     6 |     3   (0)| 00:00:01 |

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

Statistics

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

          0  recursive calls

          0  db block gets

       2787  consistent gets

          0  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

--可以发现由于前面的空块没有重用，查询最小值，从left扫描，要读许多空块。

--简单的做一个插入操作看看。

SQL> insert into muse select rownum+3000000, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 1000000;
1000000 rows created.

SQL> commit ;
Commit complete.

SQL> set autot traceonly ;

SQL> select min(id) from muse;

Execution Plan

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

Plan hash value: 2104594370

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

| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT           |           |     1 |     6 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE            |           |     1 |     6 |            |          |

|   2 |   INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     6 |     3   (0)| 00:00:01 |

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

Statistics

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

          0  recursive calls

          0  db block gets

        154  consistent gets

          0  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

--一些块已经重用，这样再执行min查询，扫描的块就减少许多。
--这些是11G的情况，而12c将发生什么变化呢，请看后面的blog。
    So that was how things kinda worked in 11g and beforehand. However, with Oracle 12c, things have now changed as
we'll see in the next post …