- 表的设计
- 普通堆表
- 优点
- 语法简单,适用大部分应用场景
- 缺点
- 表更新日志开销较大
- 可在适当场合选择全局临时表
- 查看产生多少日志
- 表的更新操作,无论是删除、插入还是修改,都会产生日志。更新操作产生的日志最多
- 查看产生redo视图
- select * from v_redo_size;
- Delete无法释放空间
- 可在适当场合考虑全局临时表和分区表
- delete删除并不能释放空间,虽将很多块的记录删除,但空块仍保留,Oracle在查询时依然会支查询空块。而truncate是一种高水平的动作,这个空块被回收,空间也就释放了。truncate是一种DDL操作而非DML操作,truncate后不带条件。
- 表记录太大检索较慢
- 可在适当场合选择分区表
- 需求访问BLOCK的个数越少越好
- Oracle提供了两种技术减少访问路径
- 索引技术
- 分区技术
- 索引回表读开销很大
- 可在适当场合选择索引组织表
- 即便有序插入也难以有序读出
- 可在适当场合选择簇表
- 表更新日志开销较大
- 优点
- 全局临时表
- 类型
- 会话全局临时表
- 基于SESSION
- on commit preserve rows
- 基于SESSION
- 事务全局临时表
- 基于事务
- on commit delete rows
- 基于事务
- 会话全局临时表
- DML操作日志表及产生的情况
- 插入
- 更新
- 删除
- 优点
- 高效删除
- 基于SESSION
- 退出SESSION,记录就删除
- 基于事务
- COMMIT或退出SESSION,记录就删除
- 基于SESSION
- 产生日志少
- 无论是插入更新还是删除,操作普通表产生的日志都比全局临时表要多。
- 不同Session独立,不产生锁
- 避免锁,利于并行
- 高效删除
- 缺点
- 语法特别
- 数据无法得到有效的保护
- 类型
- 分区表
- 分区表原理及类型
- 原理
- 不同分区,在不同的SEGMENT
- 分区表会产生多个SEGMENT,而普通表仅有一个SEGMENT。
- 化整为零,将大对象切割成多个小对象,从而使得指定的小对象定位到数据,最终减少访问路径,尽量少做事解决问题。
- 类型
- RANGE分区
- 最常用,范围分区:range_part_tab
- partition by range
- 最常用,范围分区:range_part_tab
- LIST分区
- 列表分区:list_part_tab
- partition by list
- 列表分区:list_part_tab
- HASH分区
- 散列分区:hash_part_tab
- partition by hash
- 缺点
- 系统自行创建分区名称,无法将指定数据到指定的分区。
- 优点
- 将数据根据一定的HASH算法,均匀分布到不同分区,避免查询数据集中,改善IO。可精确匹配,无法范围扫描。
- 散列分区:hash_part_tab
- 组合分区
- Oracle 11g以前的版本只支持range_list和range_hash组合
- range_list_part_tab
- RANGE分区
- 原理
- 分区索引
- 全局索引
- 对deal_date列建全局索引
- create index idx_part_tab_date on range_part_tab(deal_date);
- 局部索引
- 对deal_date列建局部索引
- create index idx_part_tab_area on rang_part_tab(area_code) loca;
- 查看索引表情况
- select index_name,status from user_indexes where index_name in(...)
- status是N/A表示是局部索引
- 全局索引
- 分区表相关易错点
- 有分区用不到
- 无法应用分区条件
- 分区索引失效
- 分区转移、切割、合并、增删等,也和分区truncate是类似的,都允许增加update global indexes关键字,从而避免全局索引失效。
- 分区索引效率低
- 有分区用不到
- 优点
- 有效的分区消除
- 操作落在指定分区,从而减少访问路径
- 高效的记录清理
- truncate partition p_xx
- 高效的记录转移
- exchange partition part_xx with table mid_table
- 分区交换,两张表的字段完全相同
- 分区表-->普通表
- alter table range_part_tab exchange partition p8 with table mid_table;
- 再执行一次普通表的数据就交换到分区表
- mid_table是无数据的,若存在数据则会交替交换
- 切割分区
- alter table range_part_tab split partition p_max at (To_DATE('2022-01-01','YYYY-MM-DD')) into (partition p2022_01,partition P_MAX);
- at部分说明了具体的范围,小于某个指定的值
- into部分说明分区被切割成两个分区,P_MAX是新的分区名,也可沿用已存在的分区名
- 分区合并
- alter table range_part_tab merge partitions p2022_01,P_MAX into partition p_max;
- merge后面跟着的是需要合并的两个分区名
- into部分为合并后的分区名,可以是新的分区名,也可以沿用已存在的分区名
- 分区的添加
- add partition
- 分区的删除
- drop partion
- 有效的分区消除
- 缺点
- 语法复杂
- 分区过多,对系统有一定影响
- 分区表原理及类型
- 索引组织表
- 最大的特点是表就是索引,索引就是表,这是一种特别的设计,无需访问表
- 优点
- 表就是索引,可以避免回表
- 缺点
- 语法复杂
- 更新开销较大
- 簇表
- 优点
- 可以减少或避免排序
- 缺点
- 语法复杂
- 更新开销大
- 优点
- 普通堆表
- 此文章为个人做的笔记,如有写的不对或你在表设计方面还有什么高见,评论区见
Oracle之表的设计
于 2023-04-09 22:18:20 首次发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)