Oracle分表分区实战（含索引，检查处理）

置顶 Ruby丶彬

已于 2023-04-14 14:55:11 修改

阅读量5.5k

点赞数 15

分类专栏： Oracle 文章标签：数据库

于 2020-08-04 16:19:49 首次发布

本文链接：https://blog.csdn.net/qq_38425803/article/details/107785338

版权

Oracle 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

文章目录

背景

业务需要，未来会对线上环境的一张表每月存储2000w+的数据。一年就达到上亿级？？数据量其大可想而知，然而这张表又是单库单表存储，这样的方式肯定是不足以支撑千万甚至上亿万级的数据，大数据量的表还会导致查询随着数据量的增大而不断性能变低，甚至会导致数据库宕机。那么如何处理上千万级，甚至上亿级的数据呢？采用的是分表分区的方式（还可以分库）。遂写次博客记录一下。

前言

公司用的是Oracle数据库，那么Oracle数据库是怎样的存储结构呢？？里面用户，表，表空间，dbf文件的关系又是怎样的呢？？这里有必要先了解一下，便以后续理解。如已明白可跳过。如下图所示：

表，表空间，dbf文件的关系

表空间：存储数据的仓库
1.一个Oracle实例对应一个或者多个表空间
2.一个表空间存在一个或者多个dbf文件存储数据
3.一个表空间存储一张或者多张表数据
分区：分区就是指定表在不同的表空间存储数据，即在不同的dbf文件存储。(一个dbf文件存储的数据量有上限)
在这里插入图片描述

用户与表，表空间的关系

1.一个用户可以有多张表，一张表只属于一个用户。但可以存储在多个表空间
2.表空间不属于任何用户，只能说一个用户对应着哪些表空间存储数据
3.每个用户都有一个默认的表空间存储数据

Oracle表空间数据量大小限制

查资料得出的数据：
1.表空间数据文件容量与DB_BLOCK_SIZE有关，在初始建库时，DB_BLOCK_SIZE要根据实际需要，设置为 4K,8K、16K、32K、64K等几种大小，ORACLE的物理文件最大只允许4194304个数据块（由操作系统决定），表空间数据文件的最大值为 4194304×DB_BLOCK_SIZE/1024M。
即：
4k最大表空间为：16384M
8K最大表空间为：32768M
16k最大表空间为：65536M
32K最大表空间为：131072M
64k最大表空间为：262144M
2.Oracle建议单表存储500w数据

–查看Oracle数据块和操作系统块大小（这里8192指8K）
show parameter db_block_size
在这里插入图片描述

分表分区优势

1.分表：按月份每月创建一个表，每年的数据拆分为12个表存储。数据分散。
2.分区：一个表空间对应一个或者多个dbf文件，单个dbf文件存储数据量有上限。月表指定不同的表空间存储，即不同的dbf文件存储。则不会因为dbf文件满了而出现问题。
Oracle数据库提供对表或索引的分区方法有几种（收集到四种）：

    1、范围分区

    2、列表分区

    3、Hash分区（散列分区）（本文采用的）

    4、复合分区

正文

存储过程基于原表分表分区（创建次月表）

逻辑步骤

原表名:TS_TIMESTAMP 新表名：TS_TIMESTAMP_202001（后缀年份+月份）
逻辑步骤：
1.在数据库系统取得原表的建表语句sql
2.字符串更改建表语句sql，修改表名，约束的名字为新表的表名，约束
3.循环创建多个分区
4.字符串拼接建表sql添加指定分区（散列hash分区）
5.执行建表sql创建新表
6.在数据库系统取得原表的建索引语句sql
7.字符串更改建索引语句sql的索引名字和表名为新表创建索引

注意：
oracle存储过程v_sql语句执行若报错无效字符?
v_sql语句末尾不要带分号

相关字符串处理可以看我整理的博文：
Oracle字符串处理
 Oracle日期处理
 Oracle查询用户，表结构，表索引等

–创建表空间 size 1m autoextend on next 5m maxsize unlimited指：初始1m大小，达到1m自动扩容5M
create tablespace tablespace_name datafile ‘/home/oracle/oradata/orcl/test1.dbf’ size 1m autoextend on next 5m maxsize unlimited;

–删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;

– 查询默认表空间
select * from database_properties where property_name=‘DEFAULT_PERMANENT_TABLESPACE’;

– 查询表空间对应的dbf文件
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

存储过程代码

create or replace procedure proc_create_monthly_table
 AUTHID CURRENT_USER IS

v_base_ddl varchar2(4000); -- 保存基表的sql
v_sql varchar2(4000);   -- 最终需要使用的sql
v_new_tableName varchar2(50);   -- 月表的表名
v_yyyy varchar2(10);    --月表的年变量
v_month number := 0;    --月表的月变量
v_yyyymm varchar2(10);   --下月的年份和月份
v_exists_flag number := 0;
v_base_table_name_upper varchar2(50);   -- 大写格式的基表名字

v_partition_ddl varchar2(500);  --创建分区的sql
v_partition_sql varchar2(500);  --创建表组装分区sql
v_partition_count number:=0;
v_tablespace_name varchar2(30); --表空间名字
v_partition_name varchar2(30); --分区名字

type cur_get_index is ref cursor; --声明一个动态游标类型，查询每个基表的索引
v_cur_get_index cur_get_index;  --声明一个动态游标变量

-- 创建一个游标查询原表的所有检查
cursor v_check_names is select CONSTRAINT_NAME  from  user_constraints where table_name='TS_TIMESTAMP';  
checkname user_constraints.CONSTRAINT_NAME%type;

v_index_ddl varchar2(4000); -- 建立索引的sql
v_old_index_name varchar2(40);
v_sec_index_name varchar2(30);

--自定义例外
no_base_table_found exception;
month_table_had_build exception;

begin
      -- 设置pl/sql缓冲区大小不限制
      DBMS_OUTPUT.ENABLE (buffer_size=>null);
      -- 取得当前年份
      select to_char(sysdate,'yyyy') into v_yyyy from dual;

      select upper('TS_TIMESTAMP') into v_base_table_name_upper from dual;
      -- dbms_output.put_line(v_base_table_name_upper);
      v_exists_flag := 0;
      select count(*) into v_exists_flag from user_tables where table_name = v_base_table_name_upper ;

      -- 如果基表不存在，抛异常
      if v_exists_flag = 0 then
        raise no_base_table_found;
      end if;

      -- 在数据库系统取得基表的建表语句
      SELECT DBMS_METADATA.GET_DDL('TABLE', v_base_table_name_upper) into v_base_ddl from dual;

      -- dbms_output.put_line(v_base_ddl);
      -- 创建下月分表 原表名:TS_TIMESTAMP  新表名：TS_TIMESTAMP_202001
      select to_char(add_months(trunc(sysdate),1),'yyyymm') into v_yyyymm from dual;
      v_new_tableName := v_base_table_name_upper||'_'||v_yyyymm;
      dbms_output.put_line(v_new_tableName);
      
      v_exists_flag := 0;
      select count(*) into v_exists_flag from  user_tables where table_name = upper(v_new_tableName)
      or table_name = '"'||v_new_tableName||'"';

      -- dbms_output.put_line(v_exists_flag);
      -- 如果月表已经创建，则不再创建
      if v_exists_flag > 0 then
          raise month_table_had_build;
      end if;

      -- 用月表的表名来替换建表语句中的基表表名
      SELECT REPLACE(v_base_ddl,v_base_table_name_upper,v_new_tableName) into v_sql from dual;
          
      -- 更改月表的创建检查约束名字        
      open v_check_names;
      loop
        fetch v_check_names into checkname;
        exit when v_check_names%notfound;
           
        SELECT REPLACE(v_sql,checkname,checkname||'_'||v_yyyymm) into v_sql from dual;
                        
      end loop;        
      close v_check_names;
      
      -- 创建分区
      v_partition_sql:='PARTITION BY HASH (ID)(';
      loop
        v_partition_count := v_partition_count+1;
        v_partition_name := 'part'||v_partition_count||'_'||v_yyyymm;
        v_tablespace_name := v_new_tableName||'_TS'||v_partition_count;
        dbms_output.put_line(v_tablespace_name);
        v_partition_ddl:='create tablespace '||v_tablespace_name||' datafile '||'''/home/oracle/oradata/
        orcl/'||v_tablespace_name||'.dbf'||''' '||' size 1m autoextend on next 5m maxsize unlimited';
        
        dbms_output.put_line(v_partition_ddl);
        execute IMMEDIATE v_partition_ddl;

        -- 拼接建分区语句到建表语句
        if v_partition_count =1
          then v_partition_sql:=v_partition_sql||'PARTITION '||v_partition_name||' TABLESPACE '
            ||v_tablespace_name;
        else
          v_partition_sql:=v_partition_sql||','||'PARTITION '||v_partition_name||' TABLESPACE '
            ||v_tablespace_name;
        end if;
        
        if v_partition_count >3
          then v_partition_sql:=v_partition_sql||')';
        end if;
        exit when v_partition_count >3;
      end loop;
      
      v_sql:=v_sql||v_partition_sql;
      dbms_output.put_line(v_partition_sql);
      dbms_output.put_line(v_sql);
           
      -- oracle 10g不支持延迟创建段 改为IMMEDIATE
      SELECT REPLACE(v_sql,'DEFERRED','IMMEDIATE') INTO v_sql from dual;
      dbms_output.put_line(v_sql);
      execute IMMEDIATE v_sql;

      -- 查找索引
      v_sql := 'SELECT DBMS_METADATA.GET_DDL('''||CHR(73)||'NDEX'', index_name),index_name
              FROM USER_INDEXES WHERE table_name = '''||v_base_table_name_upper||'''
              AND UNIQUEness = ''NONUNIQUE''';
      
      select to_char(add_months(trunc(sysdate),1),'mm') into v_month from dual;
      open v_cur_get_index for v_sql;    -- 打开游标，并且SQL执行结果存放到游标
      LOOP
          fetch v_cur_get_index into v_index_ddl,v_old_index_name;
          exit when v_cur_get_index%notfound;  -- 退出循环
                    
          -- 把建立索引的语句中的索引名字改掉
          SELECT REPLACE(v_old_index_name,'TS_','') into v_sec_index_name from dual;
          v_sec_index_name := v_sec_index_name||'_'||v_yyyymm;
          
          SELECT REPLACE(v_index_ddl,v_old_index_name,v_sec_index_name) into v_sql from dual;

          -- 把建立索引的语句中的表名改掉
          SELECT REPLACE(v_sql,v_base_table_name_upper,v_new_tableName) into v_sql from dual;
          
          execute IMMEDIATE v_sql;

      END LOOP; -- 创建索引的动态游标结束
      
      exception
        when no_base_table_found then dbms_output.put_line('没有找到基表');
        when month_table_had_build then dbms_output.put_line('下月月表已经创建');    
/*        when others then dbms_output.put_line('其他异常');*/
      
end proc_create_monthly_table;

验证是否分表成功（索引，检查与原表一致）

注意：索引和检查名字不能过长，Oracle对字符长度有限制。
1.索引：
在这里插入图片描述
2.检查：

验证是否分区成功

– 查询分表TS_TIMESTAMP_202009的所有分区信息
select * from user_tab_partitions where table_name=‘TS_TIMESTAMP_202009’;
在这里插入图片描述

– 查询某个分区的数据 PART1_202009 ：分区名字
SELECT * FROM TS_TIMESTAMP_202009 PARTITION(“PART1_202009”);
SELECT * FROM TS_TIMESTAMP_202009 PARTITION(“PART2_202009”);
SELECT * FROM TS_TIMESTAMP_202009 PARTITION(“PART3_202009”);
SELECT * FROM TS_TIMESTAMP_202009 PARTITION(“PART4_202009”);
在这里插入图片描述
物理存储：
4个分区，4个表空间，4个dbf文件。每个文件初始大小1M，随数据增大每次自动扩容5M.

定时器每月固定时间执行存储过程创建下月分表

定时器每月最后一天早上8点半创建下月分表：
定时器用法：Oracle定时器用法

declare
job number;
BEGIN
DBMS_JOB.SUBMIT( 
JOB => job, /*自动生成JOB_ID*/ 
WHAT => 'proc_create_monthly_table;', /*需要执行的存储过程名称或SQL语句*/ 
NEXT_DATE => sysdate+3/(24*60), /*初次执行时间-下一个3分钟*/ 
INTERVAL => 'TRUNC(to_date(last_day(sysdate))) + （8*60+30）/(24*60)' /*每月底最后一天早上8点半执行一次*/
); 
commit;
end;

参考博文：此博文功能为按月份创建12个月的分表：https://www.cnblogs.com/babyha/p/11596902.html

Ruby丶彬

关注

15
点赞
踩
53

收藏

觉得还不错? 一键收藏
1
评论
Oracle分表分区实战（含索引，检查处理）

背景业务需要，未来会对线上环境的一张表每月存储2000w+的数据。一年就达到上亿级？？数据量其大可想而知，然而这张表又是单库单表存储，这样的方式肯定是不足以支撑千万甚至上亿万级的数据，大数据量的表还会导致查询随着数据量的增大而不断性能变低，甚至会导致数据库宕机。那么如何处理上千万级，甚至上亿级的数据呢？采用的是分表分区的方式（还可以分库）。遂写次博客记录一下。前言公司用的是Oracle数据库，那么Oracle数据库是怎样的存储结构呢？？里面用户，表，表空间，dbf文件的关系又是怎样的呢？？这里有必要先
复制链接

扫一扫

专栏目录