首页 » ORACLE » Little about partition segment

Little about partition segment

前几天看了篇日志自己一直没注意的小细节,利用开会儿的功夫做了个测试,因为维护的环境数据库中存在数万数十万的分区表, 平时维护时确实应该注意。环境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执行计划会改变,使用上该索引,如果这不是你希望做到的就一定注意。

打赏

对不起,这篇文章暂时关闭评论。