【数据库】Oracle表分区

1.1 表空间及分区表的概念

表空间是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表， 所以称作表空间，以下是我们所有的表空间
表空间：TBS_FQSM_DATA 索引表空间：TBS_FQSM_IDX

表分区：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
表分区后，分区变成各自的段。

1.2 表分区分类

1.2.1 范围分区

范围分区((range分区)将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期

每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中

所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值

在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值

1.2.1.1 日分区

create table orders
(
    .,
    .,
    .,
    DATA_DATE    DATE         not null,
    .,
    .,
    .,
    .,
    .,
    .,
    .,
    .,
    constraint PK_ORDERS
        primary key (, , ,)
)
partition by range(DATA_DATE)
(
  partition P20221130 values less than (TO_DATE('2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     tablespace TBS_FQSM_DATA
     pctfree 10
     initrans 1
     maxtrans 255
     storage
     (
       initial 64K
       next 1M
       minextents 1
       maxextents unlimited
     ),
  partition P20221201 values less than (TO_DATE('2022-12-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     tablespace TBS_FQSM_DATA
     pctfree 10
     initrans 1
     maxtrans 255
     storage
     (
       initial 64K
       next 1M
       minextents 1
       maxextents unlimited
     )
);

1.2.1.2 月分区

create table orders
(
    .,
    .,
    .,
    DATA_DATE    DATE         not null,
    .,
    .,
    .,
    .,
    .,
    .,
    .,
    .,
    constraint PK_ORDERS
        primary key (, , ,)
)
partition by range(DATA_DATE)
(
  partition P202210 values less than (TO_DATE('2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     tablespace TBS_FQSM_DATA
     pctfree 10
     initrans 1
     maxtrans 255
     storage
     (
       initial 64K
       next 1M
       minextents 1
       maxextents unlimited
     ),
  partition P202211 values less than (TO_DATE('2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     tablespace TBS_FQSM_DATA
     pctfree 10
     initrans 1
     maxtrans 255
     storage
     (
       initial 64K
       next 1M
       minextents 1
       maxextents unlimited
     )
);

1.2.1.3 自动分区

自动分区的名字都是随机的，后期都不知道是个啥维护很麻烦，只告知有该实现，不在日常开发中使用

create table orders
(
    .,
    .,
    .,
    DATA_DATE    DATE         not null,
    .,
    .,
    .,
    .,
    .,
    .,
    .,
    .,
    constraint PK_ORDERS
        primary key (, , ,)
)
partition by range(DATA_DATE)
INTERVAL(NUMTODSINTERVAL(1, 'day'))
(
	partition P20190722 values less than (TO_DATE('2019-07-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     tablespace TBS_FQSM_DATA
)

1.3 分区表操作

1.3.1 查询分区

SELECT * FROM USER_TAB_PARTITIONS;

1.3.2 删除分区

ALTER TABLE orders DROP PARTITION P20221202;

1.3.3 新增分区

ALTER TABLE orders ADD PARTITION P20221202 VALUES LESS THAN (TO_DATE(' 2022-12-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE TBS_FQSM_DATA;

1.3.4 重命名分区

ALTER TABLE orders RENAME partition P20221202 to P20221202-1;

1.4 表空间大小及每个表所占空间的大小

1.4.1 所有的表空间以及表空间所占空间的大小

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME

1.4.2 表空间物理文件的名称及大小

SELECT TABLESPACE_NAME,
       FILE_ID,
       FILE_NAME,
       ROUND(BYTES/(1024*1024),0) TOTAL_SPACE
  FROM DBA_DATA_FILES
 ORDER BY TABLESPACE_NAME

1.4.3 查询所有表空间以及每个表空间的大小，已用空间，剩余空间，使用率和空闲率

select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from 
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

1.4.4 查询表所占空间的大小

SELECT t1.SEGMENT_NAME, 
       t1.SEGMENT_TYPE, 
       SUM(t1.BYTES / 1024 / 1024) "占用空间(M)"
  FROM DBA_SEGMENTS t1
WHERE t1.SEGMENT_TYPE='TABLE'
  AND OWNER IN ('用户1','用户2','用户3','用户4','用户5')
GROUP BY OWNER, 
         t1.SEGMENT_NAME, 
         t1.SEGMENT_TYPE;