【数据库】Oracle数据库优化经验总结（一）

1. Oracle分区表技术

分区是指表和索引可以被分成若干个部分，它们有相同的逻辑属性和数据结构。所有分区的字段和索引都是一样的。

分区表是将表数据分为若干个可以被单独管理的片，每个片就是一个分区，分一个分区都可以拥有自己的物理属性，比如表空间、事务槽、存储参数、最小区段数等，通过建分区语句指定，提升可用性和存储效率。

每个分区可以被单独管理，降低管理成本和备份成本，提高容错率，避免“一荣既荣，一损俱损”的问题。

2. 分区表的优缺点

  2.1优点

1. 可以通过指定分区提高查询性能；
2. 提高容错率，避免一荣既荣，一损俱损；
3. 降低管理成本；
4. 降低备份成本；

    2.2 缺点

     普通表和分区表不能直接转换，可以通过数据迁移，再重命名的方式实现，需要重建约束、索引，在创建表时可以添加关键字 “parallel compress” 并行执行，提高效率.

3. 何时分区

单表的数据量如果过大，会影响SQL的读写性能，我们可以通过分库分表的方式解决表性能的问题，Oracle的分区表是将一张大表在物理上分成几个较小的表，从逻辑上看仍然是一张完整的表。这样，每次DML操作只考虑其中一张分区表即可。

临界点是多少呢？

1. 数量量超过500万且空间占用超过2GB的时候必须分区；
2. 数量量高于100万，低于500万时建议分区；

注意：单个分区的数据可以超过500万，但存储空间不建议超过2GB。

​ 4. Oracle数据库表分区分类

范围分区（Range Partitioning）。根据指定的列值范围将数据分配到不同的分区。例如，按日期或数字范围进行分区。举例，可以进行2级分区，1级是按照年来分区，2级是按照月份来分区。

CREATE TABLE EVENTS .. PARTITION BY RANGE (time_id)

SUPARTITION BY LIST (region)

范围分区是自动匹配的，无需手动传入时间参数到SQL。

例子1：

create table graderecord

( sno varchar2(10),

sname varchar2(20),

dormitory varchar2(3),

grade int

)

partition by range(grade) (

partition bujige values less than(60), --不及格

partition jige values less than(85), --及格

partition youxiu values less than(maxvalue) --优秀

)

例子2：

CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    order_amount NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION jan VALUES LESS THAN (TO_DATE('2023-02-01', 'YYYY-MM-DD')),
    PARTITION feb VALUES LESS THAN (TO_DATE('2023-03-01', 'YYYY-MM-DD')),
    PARTITION mar VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD'))
);

使用：

SELECT * FROM orders WHERE order_date >= TO_DATE('2023-02-01', 'YYYY-MM-DD') AND order_date < TO_DATE('2023-03-01', 'YYYY-MM-DD');

哈希分区（Hash Partitioning）。基于哈希算法将数据平均分配到指定的几个分区中。这种方法主要用于负载均衡和数据随机分布。

列表分区（List Partitioning）。根据指定的列的离散值将数据分配到不同的分区。这种方法的分区值必须是明确的，且单个分区对应的值可以是多个。

复合分区（Composite Partitioning）。结合使用范围分区和哈希分区（或列表分区），首先根据范围将数据分配到不同的范围分区中，然后在每个范围分区内进一步使用哈希或列表分区。

桶分区（Bucket Partitioning）。类似于哈希分区，但可以根据指定的桶数进行分区。

根据不同的业务需求和数据特性来选择不同的分区方法，以优化查询性能、管理便利性以及数据维护的成本。 ​