资讯详情

Oracle 分区表、MD5 函数

Oracle 分区表概述

Oracle 分区表新建

按区划新建分区表 & List 列表分区

按年度新建分区表 & range 范围分区

按年度 区划新建分区表

按区划 年度 新建分区表

Oracle 查询分区表信息

Oracle 查询分区表数据

Oracle 附加分区，删除分区，截断分区

Oracle 拆分分区，合并分区

Oracle 重命名表分区

Oracle更新分区关键字列

Oracle MD5 函数提取摘要

Oracle 分区表概述

1、分区表对应多个物理表。如果查询条件包含分区字段，则 Oracle 可快速定位分区物理表，缩小搜索范围，提高查询性能。

2.分区表的优点:

1.提高查询性能:查询分区对象只能搜索自己关心的分区，提高检索速度。

2.增强可用性:如果表的某个分区出现故障，表在其他分区的数据仍然可用；

3.维护方便:如果表的某个分区出现故障，需要修复数据，只修复分区；

4、均衡I/O：不同的分区可以映射到磁盘平衡I/O，提高整个系统的性能。

当表数据量较大时，可使用分区表。

现有的表不能直接转换为分区表。

5、关键词用于主分区 PARTITION、关键词用于子分区 SUBPARTITION，关键字用于列表分区 List 与 VAUES (X,X,X)，关键词用于范围分区 range 与 VALUES LESS THAN (X)。

Oracle 分区表新建

1.如果有员工表，由于数据量大，分区划分和年度划分。现在新的分区表。

按区划新建分区表 & List 列表分区

-- 按区划新建分区表 & List 列表分区 -- 最好设置默认(default)分区，否则如果插入数据 mof_div_code 值不在分区列表中，- -- 插入会报错： ORA-14400:插入的分区关键词没有映射到任何分区 create table EMP_PART_BY_MOF_DIV (   empno        NUMBER(4) not null,   ename        VARCHAR2(10),   job          VARCHAR2(9),   mgr          NUMBER(4),   hiredate     DATE,   sal          NUMBER(7,2),   comm         NUMBER(7,2),   deptno       NUMBER(2),   mof_div_code VARCHAR2(9),   fiscal_year  VARCHAR2(4) ) partition by list(mof_div_code) (    partition mof4300 values ('430000000'),    partition mof4301 values ('430100000','430101000'),    partition mof4302 values (43020000    partition mof4310 values ('431000000'),    partition mof4312 values ('431200000'),    partition mofDefault      values (default) ) ;

按年度新建分区表 & range 范围分区

-- 按年度新建分区表 & range 范围分区 create table EMP_PART_BY_YEAR (   empno        NUMBER(4) not null,   ename        VARCHAR2(10),   job          VARCHAR2(9),   mgr          NUMBER(4),   hiredate     DATE,   sal          NUMBER(7,2),   comm         NUMBER(7,2),   deptno       NUMBER(2),   mof_div_code VARCHAR2(9),   fiscal_year  VARCHAR2(4) ) partition by range(fiscal_year) (   Partition year2020 values less than(2021),   Partition year2021 values les than(2022),
  Partition year2022 values less than(2023),
  Partition year2023 values less than(2024),
  Partition year2024 values less than(2025),
  Partition year2025 values less than(2026),
  Partition year2026 values less than(2027),
  Partition yearMax values less than(maxvalue)
);
comment on column EMP_PART_BY_YEAR.mof_div_code is '区划';
comment on column EMP_PART_BY_YEAR.fiscal_year  is '年度';
alter table EMP_PART_BY_YEAR add constraint EMP_PART_BY_YEAR_PK primary key (EMPNO) ;

按年度 + 区划新建分区表

-- 按年度 + 区划新建分区表，年度做主分区(范围)，区划做子分区(列表)
create table EMP_PART_BY_YEAR_MOF (
  empno        NUMBER(4) not null,
  ename        VARCHAR2(10),
  job          VARCHAR2(9),
  mgr          NUMBER(4),
  hiredate     DATE,
  sal          NUMBER(7,2),
  comm         NUMBER(7,2),
  deptno       NUMBER(2),
  mof_div_code VARCHAR2(9),
  fiscal_year  VARCHAR2(4)
)
partition by range(fiscal_year) subpartition by list (MOF_DIV_CODE) 
-- 子分区模板
subpartition template (
   subpartition mof4300 values ('430000000'),
   subpartition mof4301 values ('430100000','430101000'),
   subpartition mof4302 values ('430200000','430201000'),
   subpartition mof4310 values ('431000000'),
   subpartition mof4312 values ('431200000'),
   subpartition mofDefault      values (default)
)
-- 主分区
(
  Partition year2020 values less than(2021),
  Partition year2021 values less than(2022),
  Partition year2022 values less than(2023),
  Partition year2023 values less than(2024),
  Partition year2024 values less than(2025),
  Partition year2025 values less than(2026),
  Partition year2026 values less than(2027),
  Partition yearMax values less than(maxvalue)
);

按区划 + 年度 新建分区表

-- 按区划 + 年度 新建分区表，区划做主分区(列表), 年度做子分区(范围)
create table EMP_PART_BY_MOF_YEAR (
  empno        NUMBER(4) not null,
  ename        VARCHAR2(10),
  job          VARCHAR2(9),
  mgr          NUMBER(4),
  hiredate     DATE,
  sal          NUMBER(7,2),
  comm         NUMBER(7,2),
  deptno       NUMBER(2),
  mof_div_code VARCHAR2(9),
  fiscal_year  VARCHAR2(4)
)
partition by list (MOF_DIV_CODE) subpartition by range(fiscal_year) 
-- 子分区模板
subpartition template (
   subpartition year2020 values less than(2021),
   subpartition year2021 values less than(2022),
   subpartition year2022 values less than(2023),
   subpartition year2023 values less than(2024),
   subpartition year2024 values less than(2025),
   subpartition year2025 values less than(2026),
   subpartition year2026 values less than(2027),
   subpartition yearMax values less than(maxvalue)
)
-- 主分区
(
  Partition mof4300 values ('430000000'),
  Partition mof4301 values ('430100000','430101000'),
  Partition mof4302 values ('430200000','430201000'),
  Partition mof4310 values ('431000000'),
  Partition mof4312 values ('431200000'),
  Partition mofDefault      values (default)
);
comment on column EMP_PART_BY_MOF_YEAR.mof_div_code is '区划';
comment on column EMP_PART_BY_MOF_YEAR.fiscal_year  is '年度';
alter table EMP_PART_BY_MOF_YEAR add constraint EMP_PART_BY_MOF_YEAR_PK primary key (EMPNO) ;

select * from  USER_PART_TABLES; 查看当前用户的分区表信息
select * from  ALL_PART_TABLES; 查看当前用户可访问的所有分区表信息
select * from  dba_part_tables; 查看数据库所有分区表信息
select * from  DBA_TAB_PARTITIONS; 查看数据库所有分区表的详细分区信息
select * from  ALL_TAB_PARTITIONS; 查看当前用户可访问的所有分区表的详细分区信息
select * from  USER_TAB_PARTITIONS; 查看当前用户的所有分区表的详细分区信息
select * from  DBA_TAB_SUBPARTITIONS; 查看数据库所有组合分区表的子分区信息
select * from  ALL_TAB_SUBPARTITIONS; 查看前用户可访问的所有组合分区表的子分区信息
select * from  USER_TAB_SUBPARTITIONS; 查看前用户的所有组合分区表的子分区信息
select * from  DBA_PART_KEY_COLUMNS; 查看数据库所有分区表的分区列信息
select * from  ALL_PART_KEY_COLUMNS; 查看当前用户可访问的所有分区表的分区列信息
select * from  USER_PART_KEY_COLUMNS; 查看当前用户的所有分区表的分区列信息

1、查询格式：select * from 分区表名 partition(分区名) T WHERE 条件;

-- 查询 mof4300 分区中 年度等于 2022 的数据
select * from EMP_PART_BY_MOF_DIV partition(mof4300) T WHERE t.fiscal_year=2022;
-- 等价于
select * from EMP_PART_BY_MOF_DIV t where t.mof_div_code= '430000000' and t.fiscal_year=2022;

Oracle 追加分区、删除分区、截断分区

-- 给 EMP_PART_BY_YEAR_MOF 表添加一个分区/主分区: YEAR2028
-- 注意，如果主分区是range分区，且设置了最大值maxvalue，或者是list分区，设置了default，则直接追加会报错，此时必须'拆分分区'：
-- ORA-14074: 分区界限必须调整为高于最后一个分区界限
-- ORA-14621: 在 DEFAULT 子分区已存在时无法添加子分区
ALTER TABLE EMP_PART_BY_YEAR_MOF ADD PARTITION year2027 VALUES LESS THAN (2028);

-- 添加子分区(为主分区year2027追加子分区year2027_mof4322)
-- 注意，如果子分区是range分区，且设置了最大值maxvalue，或者是list分区，设置了default，则直接追加会报错，此时必须'拆分分区'：
-- ORA-14074: 分区界限必须调整为高于最后一个分区界限
-- ORA-14621: 在 DEFAULT 子分区已存在时无法添加子分区
ALTER TABLE EMP_PART_BY_YEAR_MOF MODIFY PARTITION year2027 ADD SUBPARTITION year2027_mof4322 VALUES ('432200000','432201000');

-- 删除指定名称的(主)分区(表中主分区对应的数据也会一并被删除)
-- 如果被删除的分区是表中唯一的分区，则无法直接删除(此时报错)，必须删除表：ORA-14083: 无法删除分区表的唯一分区
ALTER TABLE EMP_PART_BY_YEAR_MOF DROP PARTITION YEAR2020 ;
--删除指定名称的子分区(表中子分区对应的数据也会一并被删除)
ALTER TABLE EMP_PART_BY_YEAR_MOF DROP SUBPARTITION YEAR2026_MOF4312;

-- 截断某个分区是指删除某个分区中的数据，并不删除分区，也不会删除其它分区中的数据。
ALTER TABLE EMP_PART_BY_YEAR_MOF TRUNCATE PARTITION YEAR2028;
-- 截断子分区 YEAR2028_MOF4312(删除子分区中的数据，保留分区)
ALTER TABLE EMP_PART_BY_YEAR_MOF TRUNCATE SUBPARTITION YEAR2028_MOF4312;

Oracle 拆分分区、合并分区

1、拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

2、分区拆分之后，default 以及 maxvalue 中的数据也会重新整理，会自动进入匹配的新分区中。

3、比如范围分区一开始有范围，p1:(1)，p2:(2)，p3:(3)，pDafaule，其中 pDafaule中有数据 4，当将 pDafaule 拆分为 p4:(4) 和 pDafaule时，原先 pDafaule 中的数据4会自动进入了范围 p4 中。

-- 创建 RANGE 分区表的时候指定了 maxvalue (最大值)时，追加分区时，必须 split(分割/拆分) 。
-- 如下所示：表示将最大值分区 yearMax 使用 2028 进行分割，2028 分配给分区 year2027，maxvalue 仍然分配给自己(yearMax)
alter table EMP_PART_BY_YEAR_MOF split partition yearMax at (2028) into (partition year2027, partition yearMax);

-- 创建 List 分区表的时候指定了 default (默认值)时，追加分区时，必须 split(分割/拆分) 。
-- 如下所示：表示将子分区 year2027_mofDefault 拆分为 ('432200000','432201000') 与 default，前者分配给 year2027_mof4322，后者分配给 year2027_mofDefault
ALTER TABLE EMP_PART_BY_YEAR_MOF SPLIT SUBPARTITION year2027_mofDefault VALUES ('432200000','432201000') INTO (SUBPARTITION year2027_mof4322, SUBPARTITION year2027_mofDefault);

-- 将主分区 YEAR20281(2029) 的值拆分为 2028，2029，前者分配给 YEAR2027 ，后者分配给 YEAR2028
ALTER TABLE EMP_PART_BY_YEAR_MOF split PARTITION YEAR20281 AT(2028) INTO (PARTITION YEAR2027,PARTITION YEAR2028);

3、合并分区是将相邻分区合并成一个分区，结果分区将采用较高分区的界限。

4、注意不能将分区合并到界限较低的分区，否则报错：ORA-14275: 不能将下界分区作为结果分区重用。

-- 合并主分区(YEAR2026,YEAR2027,YEAR2028)-> YEAR2028(新分区名称可以随意定义)
ALTER TABLE EMP_PART_BY_YEAR_MOF MERGE PARTITIONS YEAR2026,YEAR2027,YEAR2028 INTO PARTITION YEAR2028;

-- 合并子分区(YEAR20281_MOF4300,YEAR20281_MOF4301)-> YEAR20281_MOF4301(新分区名称可以随意定义)
ALTER TABLE EMP_PART_BY_YEAR_MOF MERGE SUBPARTITIONS YEAR20281_MOF4300, YEAR20281_MOF4301 INTO SUBPARTITION YEAR20281_MOF4301;

-- 将主分区 YEAR2028 重命名为 YEAR_2028
ALTER TABLE EMP_PART_BY_YEAR_MOF RENAME PARTITION YEAR2028 TO YEAR_2028 ;
-- 将子分区 SYS_SUBP3869 重命名为 YEAR2028_MOF4301
ALTER TABLE EMP_PART_BY_YEAR_MOF RENAME SUBPARTITION SYS_SUBP3869 TO YEAR2028_MOF4301 ;

Oracle 更新分区关键字列

1、Oracle 分区表创建之后，如果修改分区字段的值，默认就会报错：ORA-14402：更新分区关键字列将导致分区的更改

2、通常分区字段不需要修改，也不建议修改，如果一定要修改，则和 flashback 闪回数据一样，需要为表开启'行移动'功能。

    --为表开启行移动功能
    alter table tableName enable row movement;

Oracle MD5 函数提取摘要

1、Oracle 中也提供了函数可以直接获取数据的 MD5 摘要，无论长短，结果都是 32 位，语法如下：Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(input_string => '被提取摘要内容'))

2、特别注意'被提取摘要内容'不能为null或者为空''，否则报错:ORA-28231: 没有数据传递到 obfuscation 工具箱。

3、使用示例如下：

SQL> select Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(input_string => '蚩尤后裔')) as md5 from dual;
MD5
--------------------------------------------------------------------------------
24F5F37AF8F17C31C211BB8B39DF11E3
SQL>
SQL> select t.*,utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => t.DNAME)) as md5 from dept t;
    DEPTNO DNAME          LOC            MD5  
---------- -------------- -----------------------------------------------
        10 ACCOUNTING     NEW YORK     0CC1ED309BE6D32F09CEBEAF1D378C62
        20 RESEARCH       DALLAS       0B667B9480CB3A0FA45691C3B7B5EE1C
        30 SALES          CHICAGO      DA7C1B2519415D312411F058C3C72E86
SQL>

A、dbms_obfuscation_toolkit.md5：创建数据的 MD5 哈希值，接收RAW类型和VARCHAR2类型。 

B、utl_raw.cast_to_raw：将一个VARCHAR2类型转换成RAW类型，数据本身不做任何改动，只是数据类型，会转换成RAW。

4、如果需要频繁使用 md5 提取摘要，每次写一长串还是不太方便，而且为空时还报错，我们可以自定义函数，然后直接调用函数即可。

CREATE OR REPLACE FUNCTION MD5(source IN VARCHAR2) RETURN VARCHAR2 IS
    -- 自定义函数(MD5)，获取传入源(source)的md5摘要，返回32位的摘要信息
    v_md5 VARCHAR2(32) := '';
BEGIN
    -- 如果源内容为空或者为null，或者为空白字符串，则不处理，返回空/null.
    IF source IS NOT NULL AND TRIM(source) IS NOT NULL THEN
        v_md5 := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => source));
    END IF;
    RETURN v_md5;
END;

-- 调用自定义函数提取摘要
-- 43DC9F79656CF8170ECD0CDF0E8D95C4
select md5(440102199010230759) from dual;
-- 24F5F37AF8F17C31C211BB8B39DF11E3
select md5('蚩尤后裔') from dual;
-- 返回 null
select MD5(null),MD5('   ') from dual;
-- 对表中的 comm 值提取摘要
select t.*,MD5(t.COMM) from emp t;

标签： 贴片二极管丝印f17

锐单商城拥有海量元器件数据手册、 IC替代型号，打造 电子元器件IC百科大全！

锐单商城 - 一站式电子元器件采购平台