前几天看了篇日志自己一直没注意的小细节，利用开会儿的功夫做了个测试，因为维护的环境数据库中存在数万数十万的分区表， 平时维护时确实应该注意。环境11.2.0.4 这里就展示两个内容
1， 表上不同的分区初始化segment大小，在split partition时的segment大小继承方式。
2， truncate partition 会使手动unusable的分区索引变为usable.
alter session set deferred_segment_creation=false;
drop table anbob.mypart purge;
create table anbob.mypart(id number, name varchar2(100))
partition by range(id)
(partition p_100 values less than (101),
partition p_200 values less than(201),
partition p_300 values less than(301));
or
create table anbob.mypart1(id number, name varchar2(100))
partition by range(id)
(partition p_100 values less than (101) segment creation immediate,
partition p_200 values less than(201)segment creation immediate,
partition p_300 values less than(301) segment creation immediate);
SQL> @seg anbob.mypart1
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- --------- ---------------- --------------- -------------------- -------------------- ---------- ---------- ----------
8 ANBOB MYPART1 P_100 TABLE PARTITION USERS 1024 4 21905
8 ANBOB MYPART1 P_200 TABLE PARTITION USERS 1024 4 22929
8 ANBOB MYPART1 P_300 TABLE PARTITION USERS 1024 4 23953
SQL> @p large_extent
NAME VALUE
---------------------------------------- ----------------------------------------
_pga_large_extent_size 1048576
_uga_cga_large_extent_size 262144
_total_large_extent_memory 0
_partition_large_extents TRUE <<<<<<<<<<<<<<<< _index_partition_large_extents FALSE SQL> alter system set "_partition_large_extents"=false;
System altered.
SQL> create table anbob.mypart2(id number, name varchar2(100))
2 partition by range(id)
3 (partition p_100 values less than (101) segment creation immediate,
4 partition p_200 values less than(201)segment creation immediate,
5 partition p_300 values less than(301) segment creation immediate);
Table created.
SQL> @seg anbob.mypart2
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- ---------- ------------------ ----------------- -------------------- ---------------------- ---------- ---------- ----------
0 ANBOB MYPART2 P_100 TABLE PARTITION USERS 8 4 1594
0 ANBOB MYPART2 P_200 TABLE PARTITION USERS 8 4 1602
0 ANBOB MYPART2 P_300 TABLE PARTITION USERS 8 4 1610
tip:
初始化为1024个block， 这里每个分区段为8M. 如果数据库里有很多空分区，当然关掉large extent partition 特性会为你节约更多的空间。
create table anbob.mypart3(id number, name varchar2(100))
partition by range(id)
(partition p_100 values less than (101) segment creation immediate storage(initial 64k next 64k),
partition p_200 values less than(201)segment creation immediate storage(initial 4096k next 4096k),
partition p_300 values less than(301) segment creation immediate storage(initial 8192k next 8192k));
SQL> @seg anbob.mypart3
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------- ---------------- ------------------ -------------------- ----------------------- ---------- ---------- ----------
4 ANBOB MYPART3 P_200 TABLE PARTITION USERS 512 4 24963
8 ANBOB MYPART3 P_300 TABLE PARTITION USERS 1024 4 25489
0 ANBOB MYPART3 P_100 TABLE PARTITION USERS 8 4 1618
alter table anbob.mypart3 split partition P_100 at(51) into(partition p_50, partition p_100);
alter table anbob.mypart3 split partition P_200 at(151) into(partition p_150, partition p_200);
alter table anbob.mypart3 split partition P_300 at(251) into(partition p_250, partition p_300);
SQL> @seg anbob.mypart3
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------- --------------- ----------------- -------------------- --------------------- ---------- ---------- ----------
0 ANBOB MYPART3 P_50 TABLE PARTITION USERS 8 4 1626
0 ANBOB MYPART3 P_100 TABLE PARTITION USERS 8 4 1634
4 ANBOB MYPART3 P_150 TABLE PARTITION USERS 512 4 26499
4 ANBOB MYPART3 P_200 TABLE PARTITION USERS 512 4 27011
8 ANBOB MYPART3 P_250 TABLE PARTITION USERS 1024 4 27537
8 ANBOB MYPART3 P_300 TABLE PARTITION USERS 1024 4 28561
alter table anbob.mypart3 split partition P_300 at(276) into(partition p_275 storage(initial 64k next 64k) , partition p_300);
alter table anbob.mypart3 split partition P_275 at(261) into(partition p_260, partition p_275);
SQL> @seg anbob.mypart3
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- --------- ----------------- ----------------- -------------------- --------------------- ---------- ---------- ----------
0 ANBOB MYPART3 P_100 TABLE PARTITION USERS 8 4 1634
4 ANBOB MYPART3 P_150 TABLE PARTITION USERS 512 4 26499
4 ANBOB MYPART3 P_200 TABLE PARTITION USERS 512 4 27011
8 ANBOB MYPART3 P_250 TABLE PARTITION USERS 1024 4 27537
0 ANBOB MYPART3 P_260 TABLE PARTITION USERS 8 4 1642
0 ANBOB MYPART3 P_275 TABLE PARTITION USERS 8 4 1650
8 ANBOB MYPART3 P_300 TABLE PARTITION USERS 1024 4 29583
0 ANBOB MYPART3 P_50 TABLE PARTITION USERS 8 4 1626
8 rows selected.
tip:
一个表可以为每个分区初始化不同的segment 大小， 也可以在拆分区时指定， 如果不带初始化大小， 拆分区时是从被拆的分区继承initial 大小。
create index anbob.mypart3_id_idx on anbob.mypart3(id) local;
insert into anbob.mypart3 select rownum,'anbob'||rownum from dba_objects where rownum<300; SQL> @seg anbob.mypart3
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- ---------- ------------------- ------------------ -------------------- ---------------------- ---------- ---------- ----------
0 ANBOB MYPART3 P_100 TABLE PARTITION USERS 8 4 1634
4 ANBOB MYPART3 P_150 TABLE PARTITION USERS 512 4 26499
4 ANBOB MYPART3 P_200 TABLE PARTITION USERS 512 4 27011
8 ANBOB MYPART3 P_250 TABLE PARTITION USERS 1024 4 27537
0 ANBOB MYPART3 P_260 TABLE PARTITION USERS 8 4 1642
0 ANBOB MYPART3 P_275 TABLE PARTITION USERS 8 4 1650
8 ANBOB MYPART3 P_300 TABLE PARTITION USERS 1024 4 29583
0 ANBOB MYPART3 P_50 TABLE PARTITION USERS 8 4 1626
0 ANBOB MYPART3_ID_IDX P_100 INDEX PARTITION USERS 8 4 1658
0 ANBOB MYPART3_ID_IDX P_150 INDEX PARTITION USERS 8 4 24962
0 ANBOB MYPART3_ID_IDX P_200 INDEX PARTITION USERS 8 4 24970
0 ANBOB MYPART3_ID_IDX P_250 INDEX PARTITION USERS 8 4 24978
0 ANBOB MYPART3_ID_IDX P_260 INDEX PARTITION USERS 8 4 24986
0 ANBOB MYPART3_ID_IDX P_275 INDEX PARTITION USERS 8 4 24994
0 ANBOB MYPART3_ID_IDX P_300 INDEX PARTITION USERS 8 4 25002
0 ANBOB MYPART3_ID_IDX P_50 INDEX PARTITION USERS 8 4 1618
SQL> alter index anbob.MYPART3_ID_IDX unusable;
Index altered.
SQL> @indpart anbob.MYPART3_ID_IDX
PARTITION_POSITION INDEX_NAME PARTITION_NAME H LFBLKS NDK NUM_ROWS CLSTFCT LAST_ANALYZED STATUS
------------------ -------------------- ---------------- -- ---------- ------------- ---------- ---------- ----------------- --------
1 MYPART3_ID_IDX P_50 1 1 50 50 1 20160805 08:54:10 UNUSABLE
2 MYPART3_ID_IDX P_100 1 1 50 50 1 20160805 08:54:10 UNUSABLE
3 MYPART3_ID_IDX P_150 1 1 50 50 1 20160805 08:54:10 UNUSABLE
4 MYPART3_ID_IDX P_200 1 1 50 50 1 20160805 08:54:10 UNUSABLE
5 MYPART3_ID_IDX P_250 1 1 50 50 1 20160805 08:54:10 UNUSABLE
6 MYPART3_ID_IDX P_260 1 1 10 10 1 20160805 08:54:10 UNUSABLE
7 MYPART3_ID_IDX P_275 1 1 15 15 1 20160805 08:54:10 UNUSABLE
8 MYPART3_ID_IDX P_300 1 1 24 24 1 20160805 08:54:10 UNUSABLE
SQL> @seg anbob.mypart3
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- --------- ---------------- ----------------- -------------------- --------------------- ---------- ---------- ----------
0 ANBOB MYPART3 P_100 TABLE PARTITION USERS 8 4 1634
4 ANBOB MYPART3 P_150 TABLE PARTITION USERS 512 4 26499
4 ANBOB MYPART3 P_200 TABLE PARTITION USERS 512 4 27011
8 ANBOB MYPART3 P_250 TABLE PARTITION USERS 1024 4 27537
0 ANBOB MYPART3 P_260 TABLE PARTITION USERS 8 4 1642
0 ANBOB MYPART3 P_275 TABLE PARTITION USERS 8 4 1650
8 ANBOB MYPART3 P_300 TABLE PARTITION USERS 1024 4 29583
0 ANBOB MYPART3 P_50 TABLE PARTITION USERS 8 4 1626
8 rows selected.
SQL> alter table anbob.mypart3 truncate partition p_50;
Table truncated.
SQL> @seg anbob.mypart3
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- --------- ------------------ ------------------ -------------------- ---------------------- ---------- ---------- ----------
0 ANBOB MYPART3 P_100 TABLE PARTITION USERS 8 4 1634
4 ANBOB MYPART3 P_150 TABLE PARTITION USERS 512 4 26499
4 ANBOB MYPART3 P_200 TABLE PARTITION USERS 512 4 27011
8 ANBOB MYPART3 P_250 TABLE PARTITION USERS 1024 4 27537
0 ANBOB MYPART3 P_260 TABLE PARTITION USERS 8 4 1642
0 ANBOB MYPART3 P_275 TABLE PARTITION USERS 8 4 1650
8 ANBOB MYPART3 P_300 TABLE PARTITION USERS 1024 4 29583
0 ANBOB MYPART3 P_50 TABLE PARTITION USERS 8 4 1626
0 ANBOB MYPART3_ID_IDX P_50 INDEX PARTITION USERS 8 4 1618
9 rows selected.
SQL> @indpart anbob.MYPART3_ID_IDX
PARTITION_POSITION INDEX_NAME PARTITION_NAME H LFBLKS NDK NUM_ROWS CLSTFCT LAST_ANALYZED STATUS
------------------ ------------------- ---------------- -- ---------- ------------- ---------- ---------- ----------------- --------
1 MYPART3_ID_IDX P_50 1 1 50 50 1 20160805 08:54:10 USABLE
2 MYPART3_ID_IDX P_100 1 1 50 50 1 20160805 08:54:10 UNUSABLE
3 MYPART3_ID_IDX P_150 1 1 50 50 1 20160805 08:54:10 UNUSABLE
4 MYPART3_ID_IDX P_200 1 1 50 50 1 20160805 08:54:10 UNUSABLE
5 MYPART3_ID_IDX P_250 1 1 50 50 1 20160805 08:54:10 UNUSABLE
6 MYPART3_ID_IDX P_260 1 1 10 10 1 20160805 08:54:10 UNUSABLE
7 MYPART3_ID_IDX P_275 1 1 15 15 1 20160805 08:54:10 UNUSABLE
8 MYPART3_ID_IDX P_300 1 1 24 24 1 20160805 08:54:10 UNUSABLE
8 rows selected.
SQL> set autot trace exp
SQL> select * from anbob.mypart3 where id=50;
Execution Plan
----------------------------------------------------------
Plan hash value: 3948375606
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 65 | 1 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| MYPART3 | 1 | 65 | 1 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | MYPART3_ID_IDX | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=50)
SQL> select * from anbob.mypart3 where id=150;
Execution Plan
----------------------------------------------------------
Plan hash value: 888492884
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 18 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 65 | 18 (0)| 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | MYPART3 | 1 | 65 | 18 (0)| 00:00:01 | 3 | 3 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=150)
Tip:
有时你可能出于某种目的把索引设为unusable, 这样索引相关的segment也会被drop, 而后的DML 就不会再维护索引， 但时如果你在分区上做了TRUNCATE操作， 这时就要注意，之前手动设置的INDEX会变为usable,随后的DML都会维护对应的索引（无论是否是分区表）， 甚至有个SQL执行计划会改变，使用上该索引，如果这不是你希望做到的就一定注意。