【Oracle数据库指南】第35篇：Oracle特殊对象——簇与索引组织表（IOT）

上一篇【第34篇】Oracle索引管理与优化详解下一篇【第36篇】Oracle用户与权限管理详解完整版明日更新敬请期待摘要除了普通堆组织表Heap-Organized Table之外Oracle还提供了两种特殊的表存储结构**簇Cluster**将频繁一起查询的多张表的相关行存储在同一数据块中消除连接操作的物理I/O**索引组织表IOTIndex-Organized Table**将行数据直接存储在B树索引结构中适合主键访问的应用场景。本文详细讲解这两种结构的原理、创建方式、适用场景与注意事项。一、簇Cluster概述1.1 什么是簇簇是一种将相关表的相关行物理存储在同一数据块的存储结构。当两张表经常通过某个公共列进行连接查询时簇能将两表中相同连接键值对应的行存放在一起从而减少连接操作的I/O次数。普通表的连接问题查询SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno d.deptno 普通表 EMP 表块Block 1 (empno7369,deptno20) | Block 2 (empno7499,deptno30) | ... DEPT 表块Block 10(deptno20,RESEARCH) | Block 11(deptno30,SALES) | ... → 查询时需要访问 EMP 的块再访问 DEPT 的块至少两次块读取簇的解决方案簇按 deptno 将两表行存储在一起 Block 1DEPT deptno20 所有 deptno20 的 EMP 行 Block 2DEPT deptno30 所有 deptno30 的 EMP 行 → 连接查询只需读取一次块即可获取两表数据1.2 簇的类型类型说明索引簇Index Cluster用B树索引管理簇键适合等值查询哈希簇Hash Cluster用哈希函数定位行等值查询最快二、索引簇Index Cluster2.1 创建索引簇-- 步骤1创建簇指定簇键列和每个键值对应的块大小CREATECLUSTER scott.emp_dept_cluster(deptno NUMBER(2)-- 簇键列连接键)SIZE600-- 每个簇键值预计占用的字节数TABLESPACEusers_data;-- 步骤2为簇创建索引必须先创建索引才能插入数据CREATEINDEXscott.idx_emp_dept_clusterONCLUSTER scott.emp_dept_cluster;-- 步骤3在簇中创建 DEPT 表CREATETABLEscott.dept_clustered(deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13))CLUSTER scott.emp_dept_cluster(deptno);-- 指定簇键-- 步骤4在簇中创建 EMP 表CREATETABLEscott.emp_clustered(empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),sal NUMBER(7,2),deptno NUMBER(2))CLUSTER scott.emp_dept_cluster(deptno);-- 同一个簇键-- 步骤5先插入DEPT数据再插入EMP数据INSERTINTOscott.dept_clusteredSELECT*FROMscott.dept;INSERTINTOscott.emp_clusteredSELECTempno,ename,job,sal,deptnoFROMscott.emp;COMMIT;2.2 使用簇查询-- 连接查询簇表的连接性能最优SELECTe.empno,e.ename,e.job,d.dnameFROMscott.emp_clustered eJOINscott.dept_clustered dONe.deptnod.deptnoWHEREe.deptno20;-- Oracle 可能使用 CLUSTER 访问路径比普通表连接少很多 I/O2.3 删除簇-- 删除簇必须先删除簇中的表DROPTABLEscott.emp_clustered;DROPTABLEscott.dept_clustered;DROPCLUSTER scott.emp_dept_cluster;-- 或者一步删除CASCADE CONSTRAINTS 同时删除外键约束DROPCLUSTER scott.emp_dept_cluster INCLUDINGTABLESCASCADECONSTRAINTS;三、哈希簇Hash Cluster3.1 哈希簇原理哈希簇用哈希函数直接计算出行所在的数据块查询时无需读索引直接计算目标块地址实现一次I/O读取最理想情况。3.2 创建哈希簇-- 创建哈希簇CREATECLUSTER scott.orders_hash_cluster(order_id NUMBER(10)-- 哈希键列)HASHKEYS100000-- 预计的不同键值数量影响哈希桶数SIZE300-- 每个哈希键值预计字节数HASHISorder_id-- 可选直接用键值做哈希键值必须为正整数TABLESPACEusers_data;-- 在哈希簇中创建表不需要额外建索引CREATETABLEscott.orders_hashed(order_id NUMBER(10),customer_id NUMBER(6),order_dateDATE,amount NUMBER(10,2))CLUSTER scott.orders_hash_cluster(order_id);3.3 哈希簇的适用场景适合哈希簇不适合哈希簇主键等值查询WHERE order_id 10086范围查询WHERE order_id 1000键值数量稳定不大幅增长键值数量动态增长导致冲突增加高频率单行读取全表扫描比普通表更差四、索引组织表IOT4.1 什么是IOT普通表堆表中数据行存储在数据段中索引存储在独立的索引段中通过 ROWID 关联。IOT 不同将整行数据存储在B树主键索引的叶节点中没有独立的数据段。普通堆表Heap Table 数据段(7369, SMITH, 800, ...) ← 无序存储 索引段empno7369 → ROWID:AAABcc... ← 需要两次访问 IOTIndex-Organized Table 主键索引叶节点empno7369 SMITH 800 ... ← 按主键顺序存储一次访问4.2 创建IOT-- 创建IOT必须指定主键主键是IOT的组织键CREATETABLEscott.zip_codes(zip_code VARCHAR2(10)PRIMARYKEY,city VARCHAR2(50)NOTNULL,province VARCHAR2(50),latitude NUMBER(8,5),longitude NUMBER(8,5))ORGANIZATIONINDEX-- 关键语法指定为IOTTABLESPACEusers_data PCTTHRESHOLD50-- 行数据超过块大小50%时溢出到溢出段INCLUDING longitude-- 从此列开始的列放入溢出段可选OVERFLOWTABLESPACEusers_data;-- 溢出段表空间4.3 IOT 的特点优点主键查询只需一次I/O无需回表行按主键顺序存储范围查询高效不需要独立的主键索引减少存储空间缺点非主键列的查询不如普通表无额外索引时需全表扫描行较宽时会有溢出段增加管理复杂度不能直接获取 ROWID有 UROWID但不同于普通 ROWIDDML 操作时需要维护 B 树结构代价较高4.4 IOT的适用场景字典/代码表数据量小、以主键查询为主如邮编、国家代码历史数据只按主键查询、很少更新数据同步目标以主键为唯一标识的参考数据表4.5 在IOT上创建二级索引-- 为IOT创建二级索引提高非主键列的查询性能CREATEINDEXscott.idx_zip_cityONscott.zip_codes(city)TABLESPACEusers_index;-- 注意IOT的二级索引的行地址是逻辑ROWID包含主键猜测值五、查看特殊对象的数据字典-- 查看所有簇SELECTcluster_name,cluster_type,hashkeys,functionFROMdba_clustersWHEREownerSCOTT;-- 查看簇中包含的表SELECTtable_name,cluster_nameFROMdba_tablesWHEREownerSCOTTANDcluster_nameISNOTNULL;-- 查看IOTSELECTtable_name,iot_typeFROMdba_tablesWHEREownerSCOTTANDiot_typeIOT;-- 查看IOT溢出段SELECTtable_name,iot_typeFROMdba_tablesWHEREownerSCOTTANDiot_typeIOT_OVERFLOW;六、普通表 vs 簇 vs IOT 对比特性普通堆表索引簇哈希簇IOT主键查询两次I/O两次I/O一次I/O最优一次I/O范围查询索引回表索引扫描不支持全扫直接范围扫描多表连接两次I/O一次I/O最优——DML性能高中中低B树维护全表扫描高中差中适用场景通用频繁连接的关联表高频等值查询主键查询为主七、最佳实践簇适合极少变化的主从关系表如 DEPT/EMP 这类数据量不大、连接频繁的表哈希簇适合高频等值主键查询事先预估好键值数量HASHKEYS过小会产生碰撞IOT 适合小型字典/代码表行宽不超过一个块的30%-40%最佳避免频繁溢出大多数场景使用普通堆表最灵活DML 性能最好配合合理索引已足够评估收益再使用特殊结构虽有针对性优势但管理复杂度和限制也更多八、总结Oracle 特殊对象的核心要点索引簇多表共享同一物理块消除连接 I/O适合稳定的关联表哈希簇等值主键查询一次 I/O需预估键值数量IOT行数据存储在主键B树叶节点主键查询无回表选型依据查询模式是主键等值、范围查询、多表连接还是通用CRUD大多数场景普通堆表 合理索引是最优选择上一篇【第34篇】Oracle索引管理与优化详解下一篇【第36篇】Oracle用户与权限管理详解完整版明日更新敬请期待参考资料《Oracle 11g数据库管理员指南》— 刘宪军著Oracle官方文档Database Administrator’s Guide - Managing ClustersOracle官方文档Database Concepts - Index-Organized Tables