欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

通过案例学调优之--分区表基本管理

程序员文章站 2024-02-01 09:34:16
...

通过案例学调优之--分区表基本管理 1、建立tablespace并将数据文件存储到不同表空间(分散I/O) 15:15:14 SYS@ test1 select file_id,file_name,tablespace_name from dba_data_files order by 3; FILE_IDFILE_NAMETABLESPACE_NAME--------------------------

通过案例学调优之--分区表基本管理

1、建立tablespace并将数据文件存储到不同表空间(分散I/O)

15:15:14 SYS@ test1 >select file_id,file_name,tablespace_name from dba_data_files order by 3;

  FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
         5 /dsk1/oradata/test1/tbs1.dbf                       TBS1
         6 /dsk2/oradata/test1/tbs2.dbf                       TBS2
         8 /dsk3/oradata/test1/tbs3.dbf                       TBS3
         9 /dsk4/oradata/test1/tbs4.dbf                       TBS4

2、创建分区

创建range 分区:

15:26:04 SYS@ test1 >create table part_t1
15:27:35   2      PARTITION BY RANGE (object_id)
15:27:35   3      (partition p1 values less than (4000)  tablespace tbs1, 
15:27:35   4      partition p2 values less than (8000)  tablespace tbs2,
15:27:35   5      partition p3 values less than (12000) tablespace tbs3,
15:27:35   6      partition p4 values less than (maxvalue) tablespace tbs4)
15:27:35   7      as 
15:27:35   8      select owner,object_name,object_id,object_type,TIMESTAMP,status from dba_objects;
Table created.

查看分区信息:

15:27:38 SYS@ test1 >select count(*) from part_t1 partition(p1);
  COUNT(*)
----------
      3931

每个分区都是一个都是的segment:

15:34:42 SYS@ test1 >select segment_name,segment_type,tablespace_name,bytes,extents from dba_segments
15:35:22   2   WHERE segment_name='PART_T1';
SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME                     BYTES    EXTENTS
-------------------- ------------------ ------------------------------ ---------- ----------
PART_T1              TABLE PARTITION    TBS4                               131072          2
PART_T1              TABLE PARTITION    TBS3                               393216          6
PART_T1              TABLE PARTITION    TBS2                               393216          6
PART_T1              TABLE PARTITION    TBS1                               327680          5
15:31:38 SYS@ test1 >select table_name,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from dba_part_tables
15:32:21   2   where table_name='PART_T1';
TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
PART_T1                        RANGE                   4 VALID

15:38:23 SYS@ test1 >select table_name,PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions

15:38:44   2   where table_name ='PART_T1';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_T1                        P1                             TBS1
PART_T1                        P2                             TBS2
PART_T1                        P3                             TBS3
PART_T1                        P4                             TBS4

创建hash 分区:

15:43:33 SYS@ test1 >create table part_hash_t2
15:43:45   2       PARTITION BY HASH (OBJECT_TYPE)
15:43:45   3       partitions 4 store in (tbs1,tbs2,tbs3,tbs4)
15:43:45   4       as 
15:43:45   5       select owner,object_name,object_id,object_type,TIMESTAMP,status from dba_objects;
Table created.
15:43:46 SYS@ test1 >select segment_name,segment_type,tablespace_name,bytes,extents from dba_segments
15:44:39   2  where tablespace_name in ('TBS1','TBS2','TBS3','TBS4');
SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME                     BYTES    EXTENTS
-------------------- ------------------ ------------------------------ ---------- ----------
PART_T1              TABLE PARTITION    TBS1                               327680          5
PART_T1              TABLE PARTITION    TBS2                               393216          6
PART_T1              TABLE PARTITION    TBS3                               393216          6
PART_T1              TABLE PARTITION    TBS4                               131072          2
PART_HASH_T2         TABLE PARTITION    TBS1                               393216          6
PART_HASH_T2         TABLE PARTITION    TBS2                               458752          7
PART_HASH_T2         TABLE PARTITION    TBS3                               131072          2
PART_HASH_T2         TABLE PARTITION    TBS4                               262144          4
sql> select segment_name,segment_type,PARTITION_NAME,tablespace_name,bytes,extents from dba_segments
  2* where tablespace_name in ('TBS1','TBS2','TBS3','TBS4')
SEGMENT_NAME         SEGMENT_TYPE       PARTITION_NAME                 TABLESPACE_NAME           BYTES    EXTENTS
-------------------- ------------------ ------------------------------ -------------------- ---------- ----------
PART_T1              TABLE PARTITION    P1                             TBS1                     327680     5
PART_T1              TABLE PARTITION    P2                             TBS2                     393216     6
PART_T1              TABLE PARTITION    P3                             TBS3                     393216     6
PART_T1              TABLE PARTITION    P4                             TBS4                     131072     2
PART_HASH_T2         TABLE PARTITION    SYS_P41                        TBS1                     393216     6
PART_HASH_T2         TABLE PARTITION    SYS_P42                        TBS2                     458752     7
PART_HASH_T2         TABLE PARTITION    SYS_P43                        TBS3                     131072     2
PART_HASH_T2         TABLE PARTITION    SYS_P44                        TBS4                     262144     4

创建List分区:

16:07:00 SYS@ test1 >create table part_ls_t3
16:07:52   2      PARTITION BY list(owner)
16:07:52   3      (partition ls_p1 values ('SYS') tablespace tbs1,
16:07:53   4       partition ls_p2 values ('SCOTT') tablespace tbs2)
16:07:53   5       as 
16:07:53   6       select owner,object_name,object_id,object_type,TIMESTAMP,status from dba_objects
16:07:53   7       where owner in ('SYS','SCOTT');
Table created.
16:07:54 SYS@ test1 >select segment_name,segment_type,PARTITION_NAME,tablespace_name,bytes,extents from dba_segments
16:08:54   2  where tablespace_name in ('TBS1','TBS2','TBS3','TBS4');
SEGMENT_NAME         SEGMENT_TYPE       PARTITION_NAME                 TABLESPACE_NAME           BYTES    EXTENTS
-------------------- ------------------ ------------------------------ -------------------- ---------- ----------
PART_T1              TABLE PARTITION    P1                             TBS1                     327680     5
PART_T1              TABLE PARTITION    P2                             TBS2                     393216     6
PART_T1              TABLE PARTITION    P3                             TBS3                     393216     6
PART_T1              TABLE PARTITION    P4                             TBS4                     131072     2
PART_HASH_T2         TABLE PARTITION    SYS_P41                        TBS1                     393216     6
PART_HASH_T2         TABLE PARTITION    SYS_P42                        TBS2                     458752     7
PART_HASH_T2         TABLE PARTITION    SYS_P43                        TBS3                     131072     2
PART_HASH_T2         TABLE PARTITION    SYS_P44                        TBS4                     262144     4
PART_ls_T3           TABLE PARTITION    LS_P1                          TBS1                     720896         11
PART_ls_T3           TABLE PARTITION    LS_P2                          TBS2                      65536     1

3、管理分区

拆分分区(split):

16:29:47 SYS@ test1 >alter table part_t1 split partition p3 at(10000) into(partition p31 tablespace tbs1,partition p32 tablespace tbs2);
Table altered.
16:30:46 SYS@ test1 >select segment_name,segment_type,PARTITION_NAME,tablespace_name,bytes,extents from dba_segments
16:31:45   2  where tablespace_name in ('TBS1','TBS2','TBS3','TBS4')
16:31:55   3  /
SEGMENT_NAME         SEGMENT_TYPE       PARTITION_NAME                 TABLESPACE_NAME           BYTES    EXTENTS
-------------------- ------------------ ------------------------------ -------------------- ---------- ----------
PART_T1              TABLE PARTITION    P1                             TBS1                     327680     5
PART_T1              TABLE PARTITION    P2                             TBS2                     393216     6
PART_T1              TABLE PARTITION    P4                             TBS4                     131072     2
PART_HASH_T2         TABLE PARTITION    SYS_P41                        TBS1                     393216     6
PART_HASH_T2         TABLE PARTITION    SYS_P42                        TBS2                     458752     7
PART_HASH_T2         TABLE PARTITION    SYS_P43                        TBS3                     131072     2
PART_HASH_T2         TABLE PARTITION    SYS_P44                        TBS4                     262144     4
PART_T1              TABLE PARTITION    P31                            TBS1                     196608     3
PART_T1              TABLE PARTITION    P32                            TBS2                     196608     3
PART_LS_T3           TABLE PARTITION    LS_P1                          TBS1                     720896         11
PART_LS_T3           TABLE PARTITION    LS_P2                          TBS2                      65536     1
16:33:13 SYS@ test1 >select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions
16:34:03   2   where table_name='PART_T1';
TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ --------------------
PART_T1                        P1                                              1 TBS1
PART_T1                        P2                                              2 TBS2
PART_T1                        P31                                             3 TBS1
PART_T1                        P32                                             4 TBS2
PART_T1                        P4                                              5 TBS4

截断分区(truncate):

16:34:14 SYS@ test1 >select count(*) from part_t1 partition(p32);
  COUNT(*)
----------
      1993

16:36:26 SYS@ test1 >alter table part_t1 truncate partition p32;
Table truncated.

16:36:53 SYS@ test1 >select count(*) from part_t1 partition(p32);
  COUNT(*)
----------
         0

移动分区(move):

12:40:45 SYS@ test1>alter table part_t1 move partition p3 tablespace system;
Table altered.

12:44:02 SYS@ test1>select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions
12:44:09   2  where table_name='PART_T1';

TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1                        P1                                              1 TBS1
PART_T1                        P2                                              2 TBS2
PART_T1                        P3                                              3 SYSTEM
PART_T1                        P4                                              4 TBS4
PART_T1                        P_MAX                                           5 TBS4
16:40:36 SYS@ test1 >select count(*) from part_t1 partition(p3);
  COUNT(*)
----------
      1874
Elapsed: 00:00:00.01
12:44:14 SYS@ test1>alter table part_t1 move partition p3 tablespace tbs3;
Table altered.
Elapsed: 00:00:00.13
12:44:50 SYS@ test1>select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions where table_name='PART_T1';
TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1                        P1                                              1 TBS1
PART_T1                        P2                                              2 TBS2
PART_T1                        P3                                              3 TBS3
PART_T1                        P4                                              4 TBS4
PART_T1                        P_MAX                                           5 TBS4

合并分区(merge):


12:44:57 SYS@ test1>alter table part_t1 split partition p3 at (10000) into (partition p3a tablespace tbs3,partition p3b tablespace tbs3);
Table altered.

12:47:21 SYS@ test1>select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions where table_name='PART_T1';
TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1                        P1                                              1 TBS1
PART_T1                        P2                                              2 TBS2
PART_T1                        P3A                                             3 TBS3
PART_T1                        P3B                                             4 TBS3
PART_T1                        P4                                              5 TBS4
PART_T1                        P_MAX                                           6 TBS4
12:50:23 SYS@ test1>alter table part_t1 merge partitions p3a,p3b into partition p3 tablespace tbs3;
Table altered.
Elapsed: 00:00:00.20
12:51:03 SYS@ test1>select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions where table_name='PART_T1';
TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1                        P1                                              1 TBS1
PART_T1                        P2                                              2 TBS2
PART_T1                        P3                                              3 TBS3
PART_T1                        P4                                              4 TBS4
PART_T1                        P_MAX                                           5 TBS4

删除分区(drop):

16:44:28 SYS@ test1 > alter table part_t1 drop partition p4;
16:44:18 SYS@ test1 >select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions
16:44:24   2  where table_name='PART_T1';
TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ --------------------
PART_T1                        P1                                              1 TBS1
PART_T1                        P2                                              2 TBS2
PART_T1                        P3                                              3 TBS3

增加新分区(add):

12:33:32 SYS@ test1>alter table part_t1 add partition p5 values less than (maxvalue) tablespace tbs4;
alter table part_t1 add partition p5 values less than (maxvalue) tablespace tbs4
                                  *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
 如果设定了max values 参数,增加新分区,应用split 拆分
12:38:13 SYS@ test1>alter table part_t1 split partition p4 at(20000) into (partition p4 tablespace tbs4,partition p_max tablespace tbs4);
Table altered.
12:40:24 SYS@ test1>select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions
12:40:35   2  where table_name='PART_T1';
TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1                        P1                                              1 TBS1
PART_T1                        P2                                              2 TBS2
PART_T1                        P3                                              3 TBS3
PART_T1                        P4                                              4 TBS4
PART_T1                        P_MAX                                           5 TBS4

16:47:03 SYS@ test1 >select count(*) from part_t1 partition(p4);
  COUNT(*)
----------
         0
         
16:47:58 SYS@ test1 >select count(*) from part_t1 partition(p5);
  COUNT(*)
----------
         0

分区表交换(exchange):

16:49:46 SYS@ test1 >create table t5_obj 
16:50:59   2  as 
16:51:01   3   select owner,object_name,object_id,object_type,TIMESTAMP,status from dba_objects
16:51:26   4  where object_id >12000 and object_id select count(*) from t5_obj;
  COUNT(*)
----------
       996

16:54:23 SYS@ test1 >alter table part_t1 exchange partition p4 with table t5_obj;
Table altered.

16:55:22 SYS@ test1 >select count(*) from part_t1 partition(p4);
  COUNT(*)
----------
       996













上一篇: 区块链溯源的可行性分析

下一篇: