调整ORACLE分区表的分区策略

起不了名字?

已于 2024-08-16 16:54:12 修改

阅读量888

点赞数 11

文章标签： oracle 数据库

于 2024-08-16 16:53:35 首次发布

本文链接：https://blog.csdn.net/weixin_50336269/article/details/141263391

版权

文章目录

背景说明
原始表结构(按月分区)
修改分区策略
总结

背景说明

表 t_login_log 目前使用的是按【月】分区策略，基于 login_date 字段。上线时DBA要求，需要将分区策略调整为按【天】分区，以便更精细地管理和查询数据。按天分区可以提高查询效率，尤其是在查询某一特定日期的数据时。

原始表结构(按月分区)

CREATE TABLE t_login_log
(
    id          NUMBER(11)              NOT NULL PRIMARY KEY,
    login_date  DATE                    NOT NULL,
    login_type  NUMBER(19)              NOT NULL,
    trace_id    VARCHAR2(64) DEFAULT '' NOT NULL,
    user_id     NUMBER(19)   DEFAULT 0  NOT NULL,
    user_type   NUMBER(3)    DEFAULT 0  NOT NULL,
    user_name   VARCHAR2(50) DEFAULT '' NOT NULL,
    user_ip     VARCHAR2(50)            NOT NULL,
    user_agent  VARCHAR2(512)           NOT NULL,
    create_time DATE         DEFAULT SYSDATE
)
    PARTITION BY RANGE (login_date)
    INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

-- 创建序列
CREATE SEQUENCE seq_t_login_log_id
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- 添加列注释
COMMENT ON COLUMN t_login_log.login_type IS '登录类型';
COMMENT ON COLUMN t_login_log.trace_id IS '链路追踪编号';
COMMENT ON COLUMN t_login_log.user_id IS '用户id';
COMMENT ON COLUMN t_login_log.user_type IS '用户类型';
COMMENT ON COLUMN t_login_log.user_name IS '用户名';
COMMENT ON COLUMN t_login_log.user_ip IS '用户 IP';
COMMENT ON COLUMN t_login_log.user_agent IS '浏览器 UA';
COMMENT ON COLUMN t_login_log.create_time IS '创建时间';

-- 创建索引
CREATE INDEX IDX_USER_ID ON t_login_log(user_id);

-- 插入测试数据
INSERT INTO t_login_log (id, login_date, login_type, trace_id, user_id, user_type, user_name, user_ip, user_agent, create_time)
VALUES (seq_t_login_log_id.NEXTVAL, SYSDATE, 1, 'trace001', 12345, 1, 'Alice', '192.168.1.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36', SYSDATE);

INSERT INTO t_login_log (id, login_date, login_type, trace_id, user_id, user_type, user_name, user_ip, user_agent, create_time)
VALUES (seq_t_login_log_id.NEXTVAL, SYSDATE, 2, 'trace002', 12346, 2, 'Bob', '192.168.1.2', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.0.4 Safari/605.1.15', SYSDATE);

INSERT INTO t_login_log (id, login_date, login_type, trace_id, user_id, user_type, user_name, user_ip, user_agent, create_time)
VALUES (seq_t_login_log_id.NEXTVAL, SYSDATE, 3, 'trace003', 12347, 1, 'Charlie', '192.168.1.3', 'Mozilla/5.0 (Linux; Android 10; Pixel 3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.93 Mobile Safari/537.36', SYSDATE);

● 分区键：login_date 是分区键，用于确定数据存储在哪个分区。
● 分区类型：按月分区，每个分区代表一个月的数据。
● 分区间隔：使用INTERVAL子句设置，每月自动创建一个新的分区。NUMTOYMINTERVAL(1, 'MONTH') 指定了分区间隔为一个月。
● 初始分区：P1 是初始分区，包含所有 login_date 小于 2024-01-01 的数据。

修改分区策略

将按月分区改为按天分区

创建临时表 t_login_log_tmp

为了避免直接修改现有表影响业务，首先创建一个与原表结构相同的临时表 t_login_log_tmp，但使用按【天】分区策略。新的分区策略将根据 login_date 按【天】分区。

CREATE TABLE t_login_log_tmp
(
    id          NUMBER(11)              NOT NULL PRIMARY KEY,
    login_date  DATE                    NOT NULL,
    login_type  NUMBER(19)              NOT NULL,
    trace_id    VARCHAR2(64) DEFAULT '' NOT NULL,
    user_id     NUMBER(19)   DEFAULT 0  NOT NULL,
    user_type   NUMBER(3)    DEFAULT 0  NOT NULL,
    user_name   VARCHAR2(50) DEFAULT '' NOT NULL,
    user_ip     VARCHAR2(50)            NOT NULL,
    user_agent  VARCHAR2(512)           NOT NULL,
    create_time DATE         DEFAULT SYSDATE
)
PARTITION BY RANGE (login_date)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(
  PARTITION P1 VALUES LESS THAN (TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

-- 创建序列
CREATE SEQUENCE seq_t_login_log_tmp_id
  START WITH 1
  INCREMENT BY 1
  NOCACHE
  NOCYCLE;

-- 添加列注释
COMMENT ON COLUMN t_login_log_tmp.login_type IS '登录类型';
COMMENT ON COLUMN t_login_log_tmp.trace_id IS '链路追踪编号';
COMMENT ON COLUMN t_login_log_tmp.user_id IS '用户id';
COMMENT ON COLUMN t_login_log_tmp.user_type IS '用户类型';
COMMENT ON COLUMN t_login_log_tmp.user_name IS '用户名';
COMMENT ON COLUMN t_login_log_tmp.user_ip IS '用户 IP';
COMMENT ON COLUMN t_login_log_tmp.user_agent IS '浏览器 UA';
COMMENT ON COLUMN t_login_log_tmp.create_time IS '创建时间';

● 分区键：依然使用 login_date 作为分区键。
● 分区类型：调整为按天分区。PARTITION BY RANGE (login_date) 表示数据将按 login_date 的范围进行分区。
● 分区间隔：使用 NUMTODSINTERVAL(1, 'DAY') 指定分区间隔为一天。每当有数据插入且 login_date 超出现有分区范围时，Oracle 将自动创建一个新分区。
● 初始分区：P1 是初始分区，包含所有 login_date 小于 2024-01-01 的数据。

数据迁移

将原表t_login_log中的数据迁移到新表t_login_log_tmp 中。此步骤将所有现有数据导入到新的分区表中。

INSERT INTO t_login_log_tmp
SELECT * FROM t_login_log;

数据验证

在删除原表之前，必须对迁移后的数据进行验证，以确保所有数据正确迁移且无误。

验证方法

● 行数对比：使用以下SQL语句验证新旧表中的数据行数是否一致：

SELECT COUNT(*) FROM t_login_log;
SELECT COUNT(*) FROM t_login_log_tmp;

数据完整性检查：随机抽取数据样本进行比对，确保新表中的数据与原表一致：

SELECT * FROM t_login_log WHERE ROWNUM <= 10;
SELECT * FROM t_login_log_tmp WHERE ROWNUM <= 10;

删除原表并重命名新表

确认数据验证无误后，可以删除原表 t_login_log，并将临时表 t_login_log_tmp 重命名为 t_login_log。

DROP TABLE t_login_log;

ALTER TABLE t_login_log_tmp RENAME TO t_login_log;

重建索引和约束

由于表结构发生了变化，所有在原表上定义的索引和约束需要在新表上重新创建。

示例：重新创建索引

CREATE INDEX IDX_USER_ID ON t_login_log(user_id);

插入数据

插入一些测试数据，确认数据按新的分区策略正确分布在相应分区中。

INSERT INTO t_login_log (id, login_date, login_type, trace_id, user_id, user_type, user_name, user_ip, user_agent, create_time)
VALUES (seq_t_login_log_id.NEXTVAL, TO_DATE('2024-08-02 15:30:00', 'YYYY-MM-DD HH24:MI:SS'), 1, 'trace001', 12378, 1, 'Alice', '192.168.1.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36', SYSDATE);

INSERT INTO t_login_log (id, login_date, login_type, trace_id, user_id, user_type, user_name, user_ip, user_agent, create_time)
VALUES (seq_t_login_log_id.NEXTVAL, TO_DATE('2024-08-03 16:45:00', 'YYYY-MM-DD HH24:MI:SS'), 2, 'trace002', 12379, 2, 'Bob', '192.168.1.2', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.0.4 Safari/605.1.15', SYSDATE);

INSERT INTO t_login_log (id, login_date, login_type, trace_id, user_id, user_type, user_name, user_ip, user_agent, create_time)
VALUES (seq_t_login_log_id.NEXTVAL, TO_DATE('2024-08-04 09:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3, 'trace003', 12380, 1, 'Charlie', '192.168.1.3', 'Mozilla/5.0 (Linux; Android 10; Pixel 3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.93 Mobile Safari/537.36', SYSDATE);

分区策略变更验证

使用以下查询检查分区策略是否正确生效，并确认新表结构符合预期。

表名一定要大写!!!

-- 查询表的分区信息，检查分区名称、范围和存储的表空间。
SELECT
    PARTITION_NAME,
    HIGH_VALUE,
    TABLESPACE_NAME
FROM
    USER_TAB_PARTITIONS
WHERE
    TABLE_NAME = 'T_LOGIN_LOG';

-- 查询分区键的详细信息 验证分区类型和间隔是否符合预期。
SELECT
    pk.COLUMN_NAME,
    pt.PARTITIONING_TYPE,
    pt.INTERVAL
FROM
    USER_PART_TABLES pt
JOIN
    USER_PART_KEY_COLUMNS pk ON pt.TABLE_NAME = pk.NAME
WHERE
    pt.TABLE_NAME = 'T_LOGIN_LOG';

同时，使用 DBMS_METADATA.GET_DDL 提取表的DDL语句，确认分区定义与预期一致。

SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_LOGIN_LOG') FROM DUAL;

总结

经过上述操作，表 t_login_log 的分区策略已成功从按月分区修改为按天分区，所有数据迁移及验证操作均已完成。

起不了名字?

关注

11
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫