dm不同表对象的性能测试

1 表介绍

1.1 B树表（默认表类型）

特点​​

1. ​​逻辑ROWID​​
   • 默认以自增逻辑ROWID作为聚集索引键，数据按B+树有序存储。
   • 显式指定聚集索引时，数据按索引列排序（如 CREATE TABLE ... CLUSTER PRIMARY KEY）。
2. 索引组织​​
   • 每张表有且仅有一个聚集索引（物理有序）。
   • 支持二级索引（非聚集索引），通过ROWID回表查询。
3. ​​参数控制​​
   • PK_WITH_CLUSTER=1 时，主键自动转为聚集索引。

​​适用场景​​

• 需要范围查询、排序、分组的业务表（如订单表按日期查询）。
• 需通过索引快速定位数据的OLTP场景。

1.2 堆表（Heap Table）

特点​​

1. ​​物理ROWID​​
   • 使用文件号+页号+页内偏移生成物理地址，无需存储ROWID值，节省空间。
   • 插入时直接定位物理位置，​​并发插入性能高​​（支持最多128个分支链表）。
2. 存储结构​​
   • 采用“扁平B树”结构（非传统B+树），数据页通过链表管理。
   • 分支类型：
     • ​​并发分支（BRANCH）​​：事务按ID随机选择链表插入，减少锁竞争。
     • ​​非并发分支（NOBRANCH）​​：顺序选择空闲链表插入。
3. 限制​​
   • ❌ 不支持聚集索引（系统自动创建根页信息，但无实际排序功能）。
   • ❌ 不支持列存储。
   • ❌ 不支持DPC集群环境。

​​适用场景​​

• 高频插入、低查询需求的场景（如日志表）。
• 需节省存储空间的表（ROWID不占物理空间）。

1.3 分区表

特点​​

1. ​​数据分治​​
   • 将大表拆分为多个子表（分区），支持​​水平分区​​（范围/列表/哈希）和​​垂直分区​​（LOB分离存储）。
2. ​​LOB存储分离​​
   • 支持为LOB列（如BLOB/CLOB）指定独立表空间：

CREATE TABLE t1 (id INT, content CLOB) 
  STORAGE(LOB ON lob_ts);  -- LOB存储在lob_ts表空间

• 支持迁移已有LOB数据：

ALTER TABLE t1 MOVE LOB ON new_lob_ts;  -- 迁移LOB列到新表空间

3. ​​分区级操作​​
   • 可对单个分区备份、重建索引，减少维护开销。

​​限制​​

• ❌ DPC集群不支持LOB存储分离。
• ❌ 分区模板表不支持独立LOB表空间。

​​适用场景​​

• 超大规模表（如亿级数据）的存储优化。
• LOB数据独立存储（如分离图片、文档等大字段）。
• 历史数据归档（按时间范围分区）。

1.4 核心区别对比

​​特性​​	​​堆表​​	​​B树表​​	​​分区表​​
​​存储结构​​	扁平B树+物理ROWID	B+树+逻辑ROWID	子表（堆表/B树表）+分区键
​​插入性能​​	⭐⭐⭐⭐（高并发）	⭐⭐（需维护索引有序）	依赖子表类型
​​范围查询效率​​	⭐（需全扫描）	⭐⭐⭐⭐（有序访问）	依赖分区剪枝+子表类型
​​LOB分离存储​​	❌ 不支持	❌ 不支持	✅ 支持
​​适用场景​​	高频插入日志类表	事务处理表（OLTP）	海量数据表、历史数据管理

2 不同表的插入查询测试对比

2.1 表结构与插入查询操作

首先需要设置同一种表结构，设置id、name、create_time、grade、city不同列，不同的表对象通过STORAGE()来修改。

    id INT PRIMARY KEY,  
    name VARCHAR(50),
    create_time DATETIME,       
    grade INT,
    city VARCHAR(20)

2.2 监控插入时的sql

对于不同的表来说使用level来按照id对于每个表循环插入100万条数据。

INSERT INTO table_name (id, name, create_time, grade, city)
SELECT 
    LEVEL AS id,
    'User_' || LEVEL AS name,
    SYSDATE - MOD(LEVEL, 365) AS create_time,  -- 随机日期
    MOD(LEVEL, 100) AS grade,                 -- 成绩0-99
    DECODE(MOD(LEVEL, 4), 0,'北京', 1,'上海', 2,'广州', 3,'深圳') AS city
FROM DUAL
CONNECT BY LEVEL <= 1000000;  -- 生成10万行[1,6](@ref) 

2.2.1 三种表的插入监测

对B树表、堆表在插入100万行数据时的性能监控报告横向对比分析。基于SQL Monitoring Report数据，结合存储结构特性，以下是关键性能指标对比表：

性能指标总览​

​指标​	​B树表​	​堆表​
​总耗时​	9.55s	6.96s
​INSERT2​	82.11% (7.84s)	75.39% (5.24s)
​逻辑读(页)​​	3,082,811	2,992,861
​物理读(页)​​	1,000,000	1,000,000
​数据生成耗时​	1.44s (15.11%)	1.45s (20.84%)
​执行次数​	3,336次	3,336次

INSERT2操作耗时差异

• ​B树表最高（7.84s）​​
  • ​根因​：B树表（聚集索引表）需严格按主键顺序插入数据，触发频繁的页分裂（SMO）​。每次分裂需复制数据、调整父节点指针，导致高并发锁竞争（Latch Contention）
  • ​影响​：索引维护成本占插入总耗时的80%以上。
• 堆表次之（5.24s）​​
  • ​根因​：数据无序插入，免去索引维护，但随机写入导致数据页碎片化，需频繁申请新页并重组数据。

2.2.2 性能差异的存储结构根源​

​存储结构​	​数据组织方式​	​写入特点​	​适用场景​
​B树表​	主键有序存储（索引组织表）	需维护B+树结构，页分裂代价高	高频率按主键查询的场景
​堆表​	无序存储（堆组织表）	插入快，但查询需额外索引支持	频繁插入且较少范围查询的场景

2.3 监控查询时的sql

对于不同的表都使用同一句sql查询，查询id=500000的情况

SELECT * FROM table_name WHERE id = 500000;

2.3.1 三表查询性能对比表

​性能指标​	​B树表​	​堆表​
​总耗时​	0.286ms	​0.148ms​
​逻辑读（页）​​	3	4
​物理读（页）​​	0	0
​执行计划关键操作	CSEK2（索引扫描）	SSEK2+BLKUP2（索引扫描+回表）
​扫描行数​	1	1
​耗时最高操作​	CSEK2 0.2101ms	NSET2 0.0548ms

2.3.2 性能差异根源分析

1. B树表：聚簇索引直接定位​
   • ​执行计划​：CSEK2（唯一索引扫描）
   • ​优势​：
     • 数据按主键物理有序存储（索引组织表），通过B+树直接定位数据页，​无回表开销。
     • 逻辑读仅需3页（根节点+中间节点+叶子节点/数据页）。
   • ​瓶颈​：无显著瓶颈，适合高频主键查询场景。
2. 堆表：二级索引扫描+回表
   • 执行计划​：SSEK2（索引扫描）→ BLKUP2（回表）
   • ​优势​：
     • 写入效率高（无索引维护），全内存访问时回表开销小（本次耗时0.000148s）。
   • ​劣势​：
     • ​二次寻址开销​：需先读索引页获取ROWID，再随机访问数据页，逻辑读（4页）高于B树表
     • ​碎片化风险​：更新/删除可能导致行迁移，进一步增加回表路径长度。
3. 分区表：全表扫描的灾难

• ​执行计划​：CSCN2（全表扫描）
• ​劣势根源​：
  • ​分区键与查询条件不匹配​：若分区键为city或create_time，查询id=500000无法触发分区裁剪，需扫描所有分区。
  • ​缺乏全局索引​：未对id创建全局索引，优化器退化为全表扫描
  • 统计信息失准​：预估扫描100万行 vs 实际300行，误判代价导致放弃索引。
  • ​并行执行无效​：小数据量查询的线程调度开销反成负担（PLL操作耗时0.000044s）。

2.3.3 关键差异的存储结构解释

​结构特性​	​B树表​	​堆表​
​数据物理组织​	主键有序存储	无序堆存储
​主键查询路径​	索引树直达数据	索引→ROWID→数据页
​非分区键查询代价​	低（索引高效）	中（需回表）
​适用场景​	高频主键查询	写密集型+随机写入

3 分区表堆表 VS 分区表索引组织表

3.1 两者表结构

特性​	​堆表（Heap-Organized）​​	​索引组织表（IOT）​​
​分区策略支持​	全部（Range/List/Hash/复合分区）	仅基础分区（Range/List/Hash）
​分区键与主键关系​	无需关联	分区键必须是主键的子集
​组合分区支持​	✅ Range-Hash,Range-List等	✅ 支持Range-Hash,List-Range
​数据物理存储​	无序堆	按主键有序的B树结构
​典型适用场景​	写密集型、无顺序要求	读密集型、强主键查询场景

3.2 两者插入对比

3.2.1 关键指标对比表

比较指标	分区堆表 (partitioned_heap)	分区B树表 (partitioned_iot)	差异/分析
​表类型​	堆表	B树表（索引组织表）	B树表需维护索引结构，增加开销。
​SQL ID​	553	562	-
​执行持续时间​	2.484秒	9.975秒	​堆表快4.01倍​：B树表索引维护导致延迟。
​受影响行数​	1,000,000	1,000,000	相同。
​物理读 (页)​​	1,000,000	1,000,000	相同：数据生成阶段（CONNECT BY）类似。
​逻辑读 (页)​​	253,413	2,994,505	​B树表高11.8倍​：索引节点访问频繁（B树表需维护排序和分区索引）。
​INSERT操作时间​	0.7409s	8.2543s	​B树表高11.14倍​：INSERT在B树表中是主要瓶颈（索引维护）。
​投影操作时间(PRJT2)​​	1.4759s	1.455s	两者的投影操作时间相近（因为DECODE(MOD(LEVEL,4))）操作相同。
​数据生成效率​	0.0032s	0.0039s	在100万条的数据插入里面两者的数据生成效率相近。
​主要瓶颈​	投影计算（PRJT2）	INSERT操作	B树表索引维护使其不适合高频插入；堆表更适合批量写入。

3.2.2 关键结果

1. 性能差异​：
   • 分区堆表插入耗时 ​2.484秒，分区B树表插入耗时 ​9.975秒，​堆表比B树表快约4倍。
   • 主要瓶颈：B树表的INSERT操作占总时间的82.75%（索引维护开销），而堆表的INSERT仅占29.83%（数据直接写入）。
2. 资源开销​：
   • ​逻辑读（Logical Read）​​：B树表为2,994,505页，是堆表（253,413页）的11.8倍，表明B树表有大量索引节点访问和维护。
   • ​物理读（Physical Read）​​：两者均为1,000,000页（数据生成阶段相同）。
   • 字节分配和释放均为0，可能因APPEND提示使用了直接路径插入（Direct Path Insert），减少了空间分配开销。
3. ​执行计划差异​：

• 两者计划结构相似（均使用CONNECT BY生成数据），但操作权重不同：
• B树表：时间主要在INSERT操作（8.2543s），索引维护主导性能。

3.3 两者查询的对比

以下场景是在非分区键查询场景下：

3.3.1 关键指标对比表

指标​	​分区IOT表​ (partitioned_iot)	​分区堆表​ (partitioned_heap)	​差异原因分析​
​逻辑读(Logical Read)​​	12页	10页	IOT表需额外访问主键索引结构，增加逻辑读。
​实际扫描行数​	1500行 → 返回96行	1200行 → 返回74行	IOT表数据存储更分散，需扫描更多行才能满足grade>90条件。
​执行时间(Duration)​​	0.516ms	0.395ms	IOT表额外索引访问和分散存储导致耗时略高。
​CSCN2操作​	0.3045ms	0.2113ms	IOT表扫描阶段消耗更多资源。
​过滤效率​	1500行输入 → 96行输出 (6.4%)	1200行输入 → 74行输出 (6.2%)	两者过滤效率接近，但IOT需扫描更多数据。

3.3.2 关键分析

1. ​逻辑读差异（12页 vs 10页）​​
   • ​根本原因​：IOT（索引组织表）以主键为索引结构存储数据，​非分区键查询需遍历整个索引，而堆表数据物理连续。
   • ​具体表现​：
   • IOT表：CSCN2扫描索引结构（INDEX33555506），需访问更多逻辑页定位数据。
   • 堆表：CSCN2直接顺序扫描物理数据页（INDEX33555496），逻辑读更少。
2. ​扫描行数差异（1500行 vs 1200行）​​
   • ​IOT表问题​：
     数据按主键物理排序，但grade是非分区键，​高分数据分散存储​（如主键1和主键10000的grade>90记录可能位于不同索引页），导致扫描更多行。
   • 堆表优势​：
     数据物理存储相对连续，​相同分区的grade值可能更集中，减少无效扫描。
3. 执行效率差异​
   • ​IOT瓶颈​：
     CSCN2操作耗时0.3045ms（堆表为0.2113ms），说明IOT的索引扫描成本更高。
   • 优化器行为​：
     两者均使用PARALLEL并行扫描，但IOT表并行执行效率更低（PLL操作占比7.49% vs 堆表9.03%），因并行任务需协调分散的索引结构。

3.4 总结

• 分区堆表​：胜在写入灵活性与低成本维护，适合时序数据和大规模批量加载。
• ​分区IOT​：赢在主键查询与分区键过滤的极致性能，适合OLTP核心业务表。
• ​通用铁律​：
  1. IOT主键必须紧凑且有序​（如自增ID）；
  2. 堆表需为高频过滤字段添加全局索引；
  3. 定期收集统计信息：DBMS_STATS.GATHER_TABLE_STATS 。