[20130813]Global Index Maintenance 11G.txt

lfree發表於2013-08-15
IndexAINaN
[20130813]Global Index Maintenance 11G.txt

參考連結：


更多的是重複作者的測試，加深理解:

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 …


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-768592/，如需轉載，請註明出處，否則將追究法律責任。

相關文章