首页 > 数据库 >Oracle 第18章：分区技术

Oracle 第18章：分区技术

时间：2024-11-02 14:49:20浏览次数：3  
标签：18 分区 PARTITION DATE 2020 Oracle VALUES id

Oracle 分区技术详解

Oracle 数据库中的分区技术是一种将大表或索引物理地划分为更小、更易管理的部分的方法。这种技术不仅提高了查询性能，还简化了数据库的维护工作。下面我们将从分区的优势、应用场景以及如何创建和维护分区等方面进行详细介绍。

1. 分区的优势
  • 提高查询性能：对于包含大量数据的表，通过分区可以显著减少需要扫描的数据量，从而加快查询速度。
  • 增强可用性：即使某个分区不可用，其他分区仍然可以访问。这对于确保业务连续性非常重要。
  • 简化维护操作：如备份、恢复等操作可以在单个分区级别执行，而不是整个表，这可以大大减少维护时间和资源消耗。
  • 优化存储：不同的分区可以根据其使用模式被放置在不同类型的存储介质上，例如将较旧的数据存放在成本较低的存储上。
2. 应用场景
  • 历史数据归档：对于需要长期保存的历史数据，可以将其按照时间维度进行分区，比如每年或每月一个分区。
  • 大数据分析：在处理大量数据集时，分区可以帮助提高数据加载速度和查询效率。
  • 在线交易系统：对于高度活跃的在线应用，可以通过分区来分散热点，避免单点性能瓶颈。
  • 地理分布：根据地理位置对数据进行分区，以优化基于位置的查询性能。
3. 分区的创建与维护
创建分区

在 Oracle 中，可以通过多种方式对表进行分区，包括范围分区（Range Partitioning）、列表分区（List Partitioning）、哈希分区（Hash Partitioning）和组合分区（Composite Partitioning）等。

  • 范围分区：基于列值的范围来划分数据。例如，根据日期字段创建按月分区。

    CREATE TABLE sales (
    prod_id NUMBER(6),
    cust_id NUMBER,
    time_id DATE,
    channel_id CHAR(1),
    promo_id NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2020','DD-MON-YYYY')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2020','DD-MON-YYYY')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2020','DD-MON-YYYY')),
    PARTITION sales_q4 VALUES LESS THAN (MAXVALUE)
);

  • 列表分区：根据列值的具体列表来划分数据。适合于分类固定的场景。

    CREATE TABLE customers (
    cust_id NUMBER,
    cust_name VARCHAR2(50),
    cust_city VARCHAR2(30)
)
PARTITION BY LIST (cust_city) (
    PARTITION cust_northwest VALUES ('Seattle', 'Portland'),
    PARTITION cust_southwest VALUES ('San Francisco', 'Los Angeles')
);

  • 哈希分区：通过哈希函数自动分配数据到不同的分区中，适用于均匀分布数据的情况。

    CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    emp_dept VARCHAR2(30)
)
PARTITION BY HASH (emp_id)
PARTITIONS 4;

  • 组合分区：可以同时使用上述两种或以上的分区方法，如先按时间范围分区，再按部门列表分区。

    CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    customer_id NUMBER,
    product_id NUMBER
)
PARTITION BY RANGE (order_date) SUBPARTITION BY LIST (customer_id) (
    PARTITION orders_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')) (
        SUBPARTITION orders_2020_northwest VALUES (10, 20),
        SUBPARTITION orders_2020_southwest VALUES (30, 40)
    ),
    PARTITION orders_2021 VALUES LESS THAN (MAXVALUE) (
        SUBPARTITION orders_2021_northwest VALUES (10, 20),
        SUBPARTITION orders_2021_southwest VALUES (30, 40)
    )
);
维护分区

  • 添加新分区：随着业务的发展，可能需要为未来的数据创建新的分区。

    ALTER TABLE sales ADD PARTITION sales_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY'));

  • 删除过期分区：对于不再需要的历史数据分区，可以安全地删除。

    ALTER TABLE sales DROP PARTITION sales_2019;

  • 合并分区：当两个相邻的分区数据量都很小时，可以考虑将它们合并成一个分区。

    ALTER TABLE sales MERGE PARTITIONS sales_2019, sales_2020 INTO PARTITION sales_2019_2020;

  • 重新定义分区：如果现有的分区策略不再满足需求，可以重新定义分区。

    ALTER TABLE sales SPLIT PARTITION sales_q4 AT (TO_DATE('01-DEC-2020','DD-MON-YYYY')) INTO (PARTITION sales_q4, PARTITION sales_dec);

以上就是关于 Oracle 分区技术的一些基本概念、优势、应用场景及创建维护方法的介绍。正确地设计和使用分区技术，可以极大地提升数据库系统的性能和可管理性。

Oracle 分区技术深入探讨

在上一部分中，我们已经介绍了 Oracle 分区技术的基本概念、优势、应用场景以及如何创建和维护分区。接下来，我们将进一步探讨一些高级特性和最佳实践，帮助您更好地利用分区技术来优化数据库性能和管理。

4. 高级特性
4.1 局部索引与全局索引

  • 局部索引：每个分区都有自己的索引，这些索引只覆盖该分区的数据。局部索引通常与分区表一起使用，可以提高查询性能并简化维护。

    CREATE INDEX sales_idx ON sales(time_id) LOCAL;

  • 全局索引：索引跨越所有分区，适用于非分区列上的索引。全局索引需要更多的维护工作，特别是在分区操作（如添加、删除、合并分区）时。

    CREATE INDEX cust_idx ON customers(cust_name) GLOBAL;
4.2 分区修剪

分区修剪是指 Oracle 在执行查询时能够自动识别哪些分区与查询条件无关，并跳过这些分区的过程。这是提高查询性能的关键因素之一。

例如，如果您有一个按年份分区的表，并且您的查询只涉及特定年份的数据，那么 Oracle 将仅扫描相关的分区，而不会扫描整个表。

SELECT * FROM sales WHERE time_id BETWEEN TO_DATE('01-JAN-2020','DD-MON-YYYY') AND TO_DATE('31-DEC-2020','DD-MON-YYYY');
4.3 并行查询

并行查询允许 Oracle 同时在多个处理器上执行查询操作，尤其是在处理大型分区表时非常有用。并行查询可以显著减少查询时间。

SELECT /*+ PARALLEL(sales, 4) */ * FROM sales WHERE time_id BETWEEN TO_DATE('01-JAN-2020','DD-MON-YYYY') AND TO_DATE('31-DEC-2020','DD-MON-YYYY');
5. 最佳实践
5.1 选择合适的分区键

选择合适的分区键是成功实施分区策略的关键。分区键应基于以下原则：

  • 查询频率：选择经常用于过滤条件的列作为分区键。
  • 数据分布：确保分区键能够均匀分布数据，避免某些分区过大或过小。
  • 维护成本：考虑分区键的变化频率，避免频繁的分区维护操作。
5.2 定期审查分区策略

随着业务的发展和数据的增长，原有的分区策略可能不再适用。定期审查和调整分区策略，以确保其始终符合当前的需求。

5.3 使用适当的索引类型

根据查询模式和数据访问方式选择合适的索引类型。对于范围查询，局部索引通常更有效；而对于点查询，全局索引可能更合适。

5.4 考虑存储优化

根据数据的访问频率和重要性，将不同的分区放置在不同类型的存储介质上。例如，将最近的数据放在高性能存储上，将历史数据放在低成本存储上。

6. 案例分析
案例 1：电子商务平台的订单管理

假设您正在管理一个电子商务平台的订单管理系统，每天处理数百万条订单记录。为了提高查询性能和管理效率，您可以按以下方式对订单表进行分区：

  • 分区策略：按订单日期范围分区，每季度一个分区。
  • 索引：为订单日期和客户ID创建局部索引。
  • 维护：定期添加新分区，删除过期分区。
CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    customer_id NUMBER,
    product_id NUMBER,
    quantity NUMBER,
    price NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION orders_q1_2020 VALUES LESS THAN (TO_DATE('01-APR-2020','DD-MON-YYYY')),
    PARTITION orders_q2_2020 VALUES LESS THAN (TO_DATE('01-JUL-2020','DD-MON-YYYY')),
    PARTITION orders_q3_2020 VALUES LESS THAN (TO_DATE('01-OCT-2020','DD-MON-YYYY')),
    PARTITION orders_q4_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY'))
);

CREATE INDEX orders_idx ON orders(order_date, customer_id) LOCAL;
案例 2：电信公司的通话记录管理

假设您正在管理一家电信公司的通话记录表，每天生成大量的通话记录。为了优化存储和查询性能，您可以按以下方式对通话记录表进行分区：

  • 分区策略：按通话日期范围分区，每月一个分区。
  • 索引：为通话日期和用户ID创建局部索引。
  • 维护：定期添加新分区，删除过期分区。
CREATE TABLE call_records (
    call_id NUMBER,
    call_date DATE,
    user_id NUMBER,
    duration NUMBER
)
PARTITION BY RANGE (call_date) (
    PARTITION calls_jan_2020 VALUES LESS THAN (TO_DATE('01-FEB-2020','DD-MON-YYYY')),
    PARTITION calls_feb_2020 VALUES LESS THAN (TO_DATE('01-MAR-2020','DD-MON-YYYY')),
    PARTITION calls_mar_2020 VALUES LESS THAN (TO_DATE('01-APR-2020','DD-MON-YYYY')),
    PARTITION calls_apr_2020 VALUES LESS THAN (TO_DATE('01-MAY-2020','DD-MON-YYYY'))
);

CREATE INDEX calls_idx ON call_records(call_date, user_id) LOCAL;

总结

Oracle 分区技术是一种强大的工具，可以帮助您管理和优化大型数据库。通过合理选择分区策略、索引类型和维护计划，您可以显著提高查询性能和管理效率。希望本文的介绍和案例分析能为您在实际工作中应用分区技术提供有益的参考。

Oracle 分区技术深入探讨（续）

在前一部分中，我们讨论了 Oracle 分区技术的高级特性和最佳实践，并提供了两个具体的案例分析。接下来，我们将进一步探讨一些更复杂的分区技术和管理技巧，以帮助您更全面地理解和应用分区技术。

7. 复杂分区策略
7.1 组合分区（Composite Partitioning）

组合分区结合了两种或多种分区方法，以实现更细粒度的数据组织和管理。常见的组合分区类型包括范围-哈希分区、范围-列表分区和哈希-列表分区。

  • 范围-哈希分区：首先按范围分区，然后在每个范围内按哈希值进一步分区。

    CREATE TABLE sales (
    prod_id NUMBER(6),
    cust_id NUMBER,
    time_id DATE,
    channel_id CHAR(1),
    promo_id NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
SUBPARTITION BY HASH (prod_id)
SUBPARTITIONS 4 (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2020','DD-MON-YYYY')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2020','DD-MON-YYYY')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2020','DD-MON-YYYY')),
    PARTITION sales_q4 VALUES LESS THAN (MAXVALUE)
);

  • 范围-列表分区：首先按范围分区，然后在每个范围内按列表值进一步分区。

    CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    customer_id NUMBER,
    product_id NUMBER
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY LIST (customer_id) (
    PARTITION orders_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')) (
        SUBPARTITION orders_2020_northwest VALUES (10, 20),
        SUBPARTITION orders_2020_southwest VALUES (30, 40)
    ),
    PARTITION orders_2021 VALUES LESS THAN (MAXVALUE) (
        SUBPARTITION orders_2021_northwest VALUES (10, 20),
        SUBPARTITION orders_2021_southwest VALUES (30, 40)
    )
);

  • 哈希-列表分区：首先按哈希值分区，然后在每个哈希分区中按列表值进一步分区。

    CREATE TABLE transactions (
    trans_id NUMBER,
    trans_date DATE,
    customer_id NUMBER,
    amount NUMBER
)
PARTITION BY HASH (trans_id)
SUBPARTITION BY LIST (customer_id) (
    PARTITION trans_hash_1 (
        SUBPARTITION trans_hash_1_northwest VALUES (10, 20),
        SUBPARTITION trans_hash_1_southwest VALUES (30, 40)
    ),
    PARTITION trans_hash_2 (
        SUBPARTITION trans_hash_2_northwest VALUES (10, 20),
        SUBPARTITION trans_hash_2_southwest VALUES (30, 40)
    )
);
8. 动态分区管理
8.1 自动分区管理

Oracle 提供了一些自动化工具和功能，帮助管理员更高效地管理分区。例如，DBMS_REDEFINITION 包可以用于在线重定义表，而 INTERVAL 分区则可以自动创建新的分区。

  • INTERVAL 分区：自动创建新的分区，适用于按时间范围分区的场景。

    CREATE TABLE sales (
    prod_id NUMBER(6),
    cust_id NUMBER,
    time_id DATE,
    channel_id CHAR(1),
    promo_id NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION sales_p1 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY'))
);

  • 在线重定义：在不影响现有业务的情况下，对表进行重新分区或结构调整。

    BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE(
        uname => 'SCHEMA_NAME',
        orig_table => 'SALES',
        int_table => 'SALES_INT'
    );

    -- Add necessary columns and constraints to the interim table

    DBMS_REDEFINITION.FINISH_REDEF_TABLE(
        uname => 'SCHEMA_NAME',
        orig_table => 'SALES',
        int_table => 'SALES_INT'
    );
END;
9. 监控和调优
9.1 监控分区表

使用 Oracle 提供的动态性能视图（如 DBA_TAB_PARTITIONS 和 DBA_TAB_SUBPARTITIONS）来监控分区表的状态和性能。

  • 查看分区信息

    SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES';

  • 查看子分区信息

    SELECT * FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME = 'SALES';
9.2 性能调优

  • 使用 SQL 调优顾问：Oracle SQL 调优顾问可以分析查询并提供优化建议。

    EXEC DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => 'SELECT * FROM sales WHERE time_id BETWEEN :start_date AND :end_date',
    bind_list => SQLTUNE_BIND_LIST(
        SQLTUNE_BIND(':start_date', TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
        SQLTUNE_BIND(':end_date', TO_DATE('31-DEC-2020', 'DD-MON-YYYY'))
    ),
    task_name => 'sales_tuning_task',
    scope => 'COMPREHENSIVE',
    time_limit => 60
);

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sales_tuning_task');

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sales_tuning_task') AS report FROM DUAL;

  • 使用执行计划：通过 EXPLAIN PLAN 查看查询的执行计划，确保分区修剪和并行查询生效。

    EXPLAIN PLAN FOR SELECT * FROM sales WHERE time_id BETWEEN TO_DATE('01-JAN-2020', 'DD-MON-YYYY') AND TO_DATE('31-DEC-2020', 'DD-MON-YYYY');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
10. 常见问题及解决方案
10.1 分区键的选择
  • 问题：选择不当的分区键可能导致数据分布不均，影响性能。
  • 解决方案：选择高基数、查询频率高的列作为分区键，并定期审查分区策略。
10.2 分区维护成本
  • 问题：频繁的分区维护操作可能增加管理成本。
  • 解决方案：使用自动分区管理功能（如 INTERVAL 分区），并定期评估和优化分区策略。
10.3 查询性能问题
  • 问题：查询性能未达到预期效果。
  • 解决方案：使用 SQL 调优顾问和执行计划分析查询，确保分区修剪和并行查询生效。调整索引策略，优化查询语句。

总结

Oracle 分区技术是一个强大而灵活的工具，可以帮助您管理和优化大型数据库。通过合理选择分区策略、索引类型和维护计划，您可以显著提高查询性能和管理效率。本文详细介绍了分区技术的高级特性和最佳实践，并提供了具体的案例分析和解决方案，希望能为您的实际工作提供有价值的参考。如果您有任何具体的问题或需要进一步的帮助，请随时联系我。

标签：18,分区,PARTITION,DATE,2020,Oracle,VALUES,id
From： https://blog.csdn.net/hummhumm/article/details/143429210

相关文章

  • P4898 [IOI2018] seats 排座位
    题目链接主要算法：线段树（虚假的），奇技淫巧（真正的）思路：1.初步:考虑如何保证一个区间坐好后是一个矩形，有一个思路从另一个题中启示我们维护\(xmin,xmax,ymin,ymax\),但是这样无法保证在中间挖一个空的情况（有一个别的题解，可以染色后维护四个角和一个判框的东西），但我们觉得就算可以维......
  • vue3 vue-i18n和pinia使用
    安装vue-i18n和Pinia(状态管理库)npminstallvue-i18n@10npminstallpinia 在main.js中引入i18n和piniaimport'./assets/main.css'import{createApp}from'vue'importAppfrom'./App.vue'importi18nfrom"./i18n";//引入pini......
  • CF1848B Vika and the Bridge
    思路:注意看，只有一次改变颜色，不要再苦苦打二分了！贪心地去求答案，对于每一种颜色记录两个点之间的距离的最大值和次大值，然后把最大值的那段区间的中点颜色更改成当前颜色。令最大值为maxx，次大值为max2。则min(⌊maxx/2​⌋,max2)  即为最优解。记得处理到n+1 号点的距......
  • 18-网络安全测评技术与标准
    18.1概况1）概念：指参照一定的标准规范要求，通过一系列的技术和管理方法，获取评估对象的网络安全状况信息，对其给出相应的网络安全情况综合判定。网络安全测评对象通常包括信息系统的组成要素或信息系统自身。2）发展1983年，美国国防部颁布《可信计算机系统评估准则TCSEC》1991年......
  • Apple Safari 18 - macOS 专属浏览器 (独立安装包下载)
    AppleSafari18-macOS专属浏览器(独立安装包下载)适用于macOSSonoma和macOSVentura的Safari浏览器18请访问原文链接：https://sysin.org/blog/apple-safari-18/查看最新版。原创作品，转载请保留出处。作者主页：sysin.org之前Safari浏览器伴随macOS更新一起发......
  • C语言内幕--全局变量(结合内存分区、汇编视角看类型、连接器)
    前言学习资源：b站up主：底层技术栈学过C语言都知道，全局变量可以再全局中使用，其实全局变量内部还是涉及到不少知识，这里从内存分区、汇编视角看类型、连接器等角度看待全局变量；由于涉及到底层技术，如果有错误，请各位大佬指点......
  • abc318_g Typical Path Problem 题解 圆方树
    题目链接：https://atcoder.jp/contests/abc318/tasks/abc318_g题目大意：给出一个有\(n\)个顶点和\(m\)条边的无向连通图\(G\)，没有重边和自环。顶点的编号为\(1\simn\)，边的编号为\(1\simm\)，第\(i\)条边连接顶点\(u_i\)和\(v_i\)。给出图上三个不同的顶点\(A,B,C......
  • CVE-2022-0185
    这是一个关于整型溢出的CVE。staticintlegacy_parse_param(structfs_context*fc,structfs_parameter*param){ structlegacy_fs_context*ctx=fc->fs_private; //[1]ctx与文件描述符相关 unsignedintsize=ctx->data_size; //[2]size——目前已经写......
  • 内存为什么要划分堆区、栈区？除了堆栈之外内存还有什么分区？
    最近在学习计算机底层相关的知识，看到内存这块内容时有个疑问，为什么要提出堆、栈的概念？当初是为了解决什么问题呢？除了堆栈外内存还存在其他分区吗？大学里学过微机原理涉及到一些相关内容但是到如今已经忘得差不多了。还是重新找资料记录一下学习过程吧！堆、栈的提出计算机在最开......
  • 【开源视频联动物联网平台】GB/T28181和SIP的区别
    【开源视频联动物联网平台】GB/T28181和SIP的区别-阿里云开发者社区在一些涉及系统融合的项目中，经常会有人把GB/T28181和SIP混淆，特别是在项目实施与配置的时候，视频监控联网的许多参数都被写成SIP，这让现场工程师感到困扰。 GB/T28181是专门针对视频监控联网的国家标准，为了满足......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99