oracle分区作用和实现方式

入坑玩家_
231 阅读5分钟

一 什么是分区？

  1. 分区就是把一张表的数据拆分为用多个小表装，每张小表就是单独的一份数据。一个或多个小表都可以组合成一个分区。
  2. oracle数据库的数据都存储在块中（oracle数据块是数据存储的最小存储单位），我们写入表中的数据都对应存储在一个或多个块，当查询数据时候，其实就是扫描数据块的数据。下图画得比较丑，希望大家别介意!!

image.png

二 分区作用

  1. 上面的文字看不懂没关系，先说分区效果。分区可以让我们查询的时候，可以精确定位分区查找数据，变相的提高查询效率。

  2. 举个例子：我们查询数据时通常都给表加上索引，那么索引的作用，这个大家都知道。那么分区的作用，也可以理解为一个索引。

  3. 假如我有10W数据，这些数据分布在100个块中，我使用ID作为分区键，然后把ID在1W以下的数据分配到数据块10以下，1W以上-2W以下的分配到数据库10以上-20以下，后面以此类推。。。

  4. 当我要查询ID为5000的数据时，首先会定位这20个数据块，然后扫描这20个块再根据索引查找ID为5000的数据。这样讲是不是很好理解？如果没有分区，那么会扫描100个数据块，再根据索引定位ID为5000的数据，这无形中就多扫描的80个数据块。

  5. 如果数据在千万级别以上，某些复杂查询时是非常消耗性能的，查询时间很长，就算走了索引都有可能超过30秒。

三 分区的种类

分区后多种，分为：

  • 范围分区：按照指定的范围将数据分散到不同的分区中，例如按照时间、价格等范围进行分区。
  • 列分区：按照指定的列值将数据分散到不同的分区中，例如按照地区、部门等列值进行分区。
  • 散列分区：按照散列算法将数据分散到不同的分区中，例如按照客户ID进行分区。
  • 复合分区：将多种分区方式结合起来，例如按照时间和地区进行分区。
  • 桶分区：将数据按照指定的范围和间隔进行分区，例如按照时间间隔进行分区。

四 分区示例

每种分区方式都有使用场景，这里我以范围分区作为示例，数据库用oracle,其他分区方式大家可以自己尝试。

  1. 首先表自己准备！！！

  2. 分区有两种方式，第一种默认，数据存放在同一个物理地址。第二种需要手动指定分区物理地址。

默认分区方式
    -- 解释下以下分区
    -- 分区键用时间字段 WORK_DATE，p1，p2,p3 表示分区名。
    -- p1后面的to_date('2023-01-01', 'yyyy-MM-dd')表示日期小于2023-01-01的数据都放在p1里
    -- p2则存放小于日期2023-05-01，且大于日期2023-01-01的数据
    -- p3存放大于日期2023-05-01后的数据

    ALTER TABLE DEMO_T
        MODIFY
            PARTITION BY RANGE (WORK_DATE) (
            PARTITION r1 VALUES LESS THAN (to_date('2023-01-01', 'yyyy-MM-dd')) ,
            PARTITION r2 VALUES LESS THAN (to_date('2023-05-01', 'yyyy-MM-dd')) ,
            PARTITION r3 VALUES LESS THAN (maxvalue )
            );
手动分区方式
  1. 创建物理文件，文件名，路径，大小需要自己定义
CREATE TABLESPACE 物理地址名,自己定义
DATAFILE '/opt/oracle/oradata/data/TEST_DATA.dbf' 
SIZE 200M;

例如：
CREATE TABLESPACE my_file
DATAFILE '/opt/oracle/oradata/data/TEST_DATA.dbf' 
SIZE 200M;
  1. 给创建的物理地址授权，授权需要有DBA权限,也就是使用root用户。
alter user schemam名 quota unlimited on 表空间名;


例如：
alter user shou quota unlimited on my_file;
  1. 创建分区，并指定分区的存放物理地址


    物理地址名就是第一步创建物理地址时自定义的名字,my_file是上面创建的物理文件地址名
    ALTER TABLE DEMO_T
        MODIFY
            PARTITION BY RANGE (WORK_DATE) (
            PARTITION r1 VALUES LESS THAN (to_date('2023-01-01', 'yyyy-MM-dd')) tablespace my_file, -- my_file是上面创建的物理文件地址名
            PARTITION r2 VALUES LESS THAN (to_date('2023-05-01', 'yyyy-MM-dd'))  tablespace my_file ,
            PARTITION r3 VALUES LESS THAN (maxvalue ) tablespace my_file
            );
  1. 分区完成后再执行执行以下命令查看分区情况
     select TABLE_NAME as 表名,PARTITION_NAME as 分区名,TABLE_OWNER as 服务名, HIGH_VALUE as 分区键信息  from ALL_TAB_PARTITIONS where TABLE_OWNER = 'schema名';

image.png

分区合并

  1. 分区后还可以对分区进行合并，此功能大家自己测试，难度不大。
//以下是把 q1和q2的分区合并到q2中
ALTER TABLE DEMO_T MERGE PARTITIONS q1,q2 INTO partition q2;

删除分区

ALTER TABLE DEMO_T DROP PARTITION q1;

总结

  1. 分区实际可以看做是加了一层数据块查询的索引
  2. 删除或合并分区后，所有分区索引失效，需要重新创建
  3. 如果表中只剩最后一个分区，将无法删除这个分区
  4. 表分区后，查询数据不要全查，否则效率比没分区前全查还要慢，因为跨区查询原因。
  5. 使用 ALTER TABLE 命令创建分区时，Oracle 数据库会自动创建分区索引，而不会改变原始索引的名称或基础结构，也就是说索引会帮你弄好。
  6. 分区后是不可逆的操作，不能回退未分区前操作
  7. 分区可以把不同数据存放在不同区上，不同区的物理地址可以不同。
avatar
文章
阅读
粉丝