上一篇【第32篇】Oracle归档日志管理与LogMiner日志分析下一篇【第34篇】Oracle索引管理与优化详解摘要表Table是Oracle数据库中最重要的对象是数据存储的基础单元。随着数据量的增长普通表的查询和管理性能会显著下降此时需要引入**分区表Partitioned Table**技术。本文详细讲解Oracle表的创建与管理包括约束、压缩、高水位线、四种分区策略范围分区、列表分区、哈希分区、复合分区、分区操作与维护以及分区裁剪Partition Pruning的性能原理是DBA和开发人员处理大数据量场景的核心技能。一、表管理基础1.1 表的存储属性-- 创建表时指定完整存储属性CREATETABLEscott.orders(order_id NUMBER(10)PRIMARYKEY,customer_id NUMBER(6)NOTNULL,order_dateDATEDEFAULTSYSDATENOTNULL,statusVARCHAR2(20)DEFAULTPENDING,total_amt NUMBER(10,2)CHECK(total_amt0),notes VARCHAR2(4000))TABLESPACEusers_data PCTFREE15-- 保留15%给UPDATE行增长INITRANS4-- 预分配4个ITL槽位STORAGE(INITIAL1M-- 在字典管理TS中有效LMT中被忽略NEXT1M)NOLOGGING;-- 批量导入时减少重做日志生成1.2 高水位线High Water Mark高水位线是段曾经使用过的最高数据块位置全表扫描时会扫描高水位线以下的所有块即使其中大部分已删除。-- 查看表的高水位线通过段信息间接了解SELECTtable_name,blocks,empty_blocks,num_rows,ROUND(num_rows/DECODE(blocks,0,1,blocks),0)ASrows_per_blockFROMdba_tablesWHEREownerSCOTTANDtable_nameORDERS;-- 若 blocks 远大于 num_rows / avg_row_len 的理论值说明高水位线偏高-- 降低高水位线回收空闲块-- 方法1SHRINK SPACE需要ASSM表空间 ROW MOVEMENTALTERTABLEscott.ordersENABLEROWMOVEMENT;ALTERTABLEscott.orders SHRINK SPACECASCADE;-- 方法2MOVE TABLE重建表下调高水位线但会失效索引ALTERTABLEscott.orders MOVETABLESPACEusers_data;-- 之后需要重建索引ALTERINDEXscott.pk_orders REBUILD;1.3 表压缩Table Compression-- 基本压缩只对直接路径INSERT有效CREATETABLEscott.archive_orders(order_id NUMBER,statusVARCHAR2(20))COMPRESS;-- 高级压缩OLTP场景对所有DML有效11g企业版特性CREATETABLEscott.orders_compressed(order_id NUMBER,statusVARCHAR2(20))COMPRESSFOROLTP;-- 对现有表启用压缩仅影响新写入数据ALTERTABLEscott.orders COMPRESSFOROLTP;-- 重建已有数据为压缩格式ALTERTABLEscott.orders MOVE COMPRESSFOROLTP;二、分区表概述2.1 为什么需要分区表问题分区解决方案全表扫描慢数据量大分区裁剪只扫描相关分区DML 操作慢将数据分散到多个小表分区历史数据维护困难直接 DROP 旧分区秒级删除亿级数据备份恢复复杂按分区备份和恢复表维护锁定整表维护操作只锁定相关分区推荐使用分区的场景表数据量超过1000万行有明显的时间维度按日期查询、归档需要按某个维度地区、类别等进行批量维护2.2 分区表核心概念分区表Partitioned Table ├── 分区1Partition p2024_q1 │ └── 数据文件 /u01/.../orders_p1.dbf ├── 分区2Partition p2024_q2 │ └── 数据文件 /u01/.../orders_p2.dbf └── 分区NPartition p2024_qN分区表对应用透明SQL语句无需修改Oracle自动根据WHERE条件决定扫描哪个分区分区裁剪。三、范围分区RANGE Partitioning最常用的分区方式按列值范围划分分区适合时间维度数据。3.1 创建范围分区表CREATETABLEscott.sales_range(sale_id NUMBER(10),sale_dateDATENOTNULL,customer_id NUMBER(6),amount NUMBER(10,2),region VARCHAR2(20))PARTITIONBYRANGE(sale_date)(PARTITIONp2023_q1VALUESLESS THAN(DATE2023-04-01)TABLESPACEsales_q1_ts,PARTITIONp2023_q2VALUESLESS THAN(DATE2023-07-01)TABLESPACEsales_q2_ts,PARTITIONp2023_q3VALUESLESS THAN(DATE2023-10-01)TABLESPACEsales_q3_ts,PARTITIONp2023_q4VALUESLESS THAN(DATE2024-01-01)TABLESPACEsales_q4_ts,PARTITIONp2024_q1VALUESLESS THAN(DATE2024-04-01)TABLESPACEsales_current_ts,PARTITIONp_futureVALUESLESS THAN(MAXVALUE)-- 兜底分区TABLESPACEsales_current_ts);3.2 范围分区的查询裁剪-- 查询只扫描 p2024_q1 分区分区裁剪生效SELECT*FROMscott.sales_rangeWHEREsale_dateBETWEENDATE2024-01-01ANDDATE2024-03-31;-- 验证分区裁剪查看执行计划EXPLAINPLANFORSELECT*FROMscott.sales_rangeWHEREsale_dateBETWEENDATE2024-01-01ANDDATE2024-03-31;SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);-- 执行计划中应显示Pstart5 Pstop5只扫描第5个分区四、列表分区LIST Partitioning按列的离散值划分分区适合按固定类别分区的数据。CREATETABLEscott.sales_list(sale_id NUMBER(10),region VARCHAR2(20)NOTNULL,sale_dateDATE,amount NUMBER(10,2))PARTITIONBYLIST(region)(PARTITIONp_northVALUES(北京,天津,河北,山东)TABLESPACEsales_north_ts,PARTITIONp_southVALUES(广东,广西,海南,福建)TABLESPACEsales_south_ts,PARTITIONp_eastVALUES(上海,江苏,浙江,安徽)TABLESPACEsales_east_ts,PARTITIONp_otherVALUES(DEFAULT)-- 所有未指定的值TABLESPACEsales_other_ts);五、哈希分区HASH Partitioning适合没有明显范围或列表分区键的情况Oracle 用哈希算法均匀分散数据。-- 创建哈希分区表分区数建议为2的幂次CREATETABLEscott.large_orders(order_id NUMBER(10),customer_id NUMBER(6),amount NUMBER(10,2))PARTITIONBYHASH(order_id)PARTITIONS8-- 8个分区STOREIN(ts1,ts2,ts3,ts4);-- 指定表空间列表循环分配六、复合分区Composite Partitioning组合两种分区策略范围哈希Range-Hash或范围列表Range-List。-- 范围-哈希复合分区先按日期分区每个分区内再哈希分散CREATETABLEscott.sales_composite(sale_id NUMBER(10),sale_dateDATENOTNULL,customer_id NUMBER(6),amount NUMBER(10,2))PARTITIONBYRANGE(sale_date)SUBPARTITIONBYHASH(customer_id)SUBPARTITIONS4(PARTITIONp2024_q1VALUESLESS THAN(DATE2024-04-01)TABLESPACEsales_q1_ts,PARTITIONp2024_q2VALUESLESS THAN(DATE2024-07-01)TABLESPACEsales_q2_ts,PARTITIONp_futureVALUESLESS THAN(MAXVALUE)TABLESPACEsales_current_ts);七、分区维护操作7.1 添加分区-- 为范围分区表添加新分区必须比现有最大值更大ALTERTABLEscott.sales_rangeADDPARTITIONp2024_q2VALUESLESS THAN(DATE2024-07-01)TABLESPACEsales_q2_ts;-- 列表分区添加新分区ALTERTABLEscott.sales_listADDPARTITIONp_westVALUES(四川,重庆,云南,贵州)TABLESPACEsales_west_ts;7.2 删除分区立即回收空间-- 删除分区及其数据非常快直接删除数据块ALTERTABLEscott.sales_rangeDROPPARTITIONp2023_q1;-- 删除分区并更新全局索引ALTERTABLEscott.sales_rangeDROPPARTITIONp2023_q1UPDATEGLOBALINDEXES;7.3 截断分区只删数据保留分区结构-- 截断指定分区比 DELETE 快得多ALTERTABLEscott.sales_rangeTRUNCATEPARTITIONp2023_q1;7.4 拆分分区Split-- 将 p_future 分区拆分为 p2024_q2 和 p_futureALTERTABLEscott.sales_range SPLITPARTITIONp_future AT(DATE2024-07-01)INTO(PARTITIONp2024_q2TABLESPACEsales_q2_ts,PARTITIONp_futureTABLESPACEsales_current_ts);7.5 合并分区Merge-- 将相邻两个分区合并为一个连续的范围分区才能合并ALTERTABLEscott.sales_rangeMERGEPARTITIONS p2023_q1,p2023_q2INTOPARTITIONp2023_h1TABLESPACEsales_history_ts;7.6 交换分区Exchange将普通表的数据快速加载到分区表零复制只交换指针-- 创建与分区同结构的普通表用于装载数据CREATETABLEscott.sales_q3_loadASSELECT*FROMscott.sales_rangeWHERE10;-- 向普通表装载数据可用外部表、SQL*Loader等方式-- INSERT INTO scott.sales_q3_load SELECT ... FROM staging_table;-- 交换分区毫秒级完成ALTERTABLEscott.sales_range EXCHANGEPARTITIONp2024_q1WITHTABLEscott.sales_q3_loadWITHVALIDATION;八、分区表查询与监控-- 查看表的分区信息SELECTpartition_name,partition_position,tablespace_name,num_rows,blocks,high_valueFROMdba_tab_partitionsWHEREtable_ownerSCOTTANDtable_nameSALES_RANGEORDERBYpartition_position;-- 查询特定分区的数据SELECT*FROMscott.sales_rangePARTITION(p2024_q1);-- 查看分区表的分区键SELECTtable_name,partitioning_type,partition_countFROMdba_part_tablesWHEREownerSCOTT;九、最佳实践时间维度数据首选范围分区按月或按季度分区方便历史数据管理必须有兜底分区MAXVALUE或DEFAULT防止分区键超出范围报错分区与表空间对应每个分区对应独立表空间便于表空间级别的备份和恢复分区键列避免函数操作WHERE TRUNC(sale_date)...会导致分区裁剪失效应写WHERE sale_date BETWEEN ... AND ...本地分区索引优于全局索引LOCAL 索引与分区一一对应维护成本低十、总结Oracle表管理与分区表的核心要点表管理存储参数、高水位线收缩、表压缩范围分区按时间/数值范围最常用支持时间点数据归档列表分区按离散值如地区、类别哈希分区均匀分散数据适合无规律分区键复合分区组合两种策略兼顾查询和分散I/O分区操作ADD、DROP、TRUNCATE、SPLIT、MERGE、EXCHANGE分区裁剪WHERE条件命中分区键时只扫描相关分区上一篇【第32篇】Oracle归档日志管理与LogMiner日志分析下一篇【第34篇】Oracle索引管理与优化详解参考资料《Oracle 11g数据库管理员指南》— 刘宪军著Oracle官方文档Database VLDB and Partitioning GuideOracle官方文档Database Administrator’s Guide - Managing Tables