Oracle之表的设计

六月雨滴

已于 2023-04-13 10:07:05 修改

阅读量616

点赞数

分类专栏： Oracle 文章标签： oracle 数据库

于 2023-04-09 22:18:20 首次发布

本文链接：https://blog.csdn.net/qq_36620997/article/details/130049050

版权

Oracle 专栏收录该内容

21 篇文章 7 订阅

订阅专栏

文章探讨了数据库表设计的不同策略，包括普通堆表的优缺点，如日志开销，以及如何使用全局临时表和分区表来优化性能。提到了DML操作（插入、更新、删除）对日志的影响，并强调了索引和分区在减少访问路径和提高查询效率上的作用。全局临时表根据会话或事务保持数据，而分区表通过不同方式（范围、列表、哈希）分布数据，以优化IO。同时，文章还讨论了分区操作如交换、切割、合并和删除，以及索引组织表和簇表的利弊。

摘要由CSDN通过智能技术生成

表的设计
- 普通堆表
  - 优点
    - 语法简单，适用大部分应用场景
  - 缺点
    - 表更新日志开销较大
      - 可在适当场合选择全局临时表
      - 查看产生多少日志
        select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
      - 表的更新操作，无论是删除、插入还是修改，都会产生日志。更新操作产生的日志最多
      - 查看产生redo视图
        select * from v_redo_size;
    - Delete无法释放空间
      - 可在适当场合考虑全局临时表和分区表
      - delete删除并不能释放空间，虽将很多块的记录删除，但空块仍保留，Oracle在查询时依然会支查询空块。而truncate是一种高水平的动作，这个空块被回收，空间也就释放了。truncate是一种DDL操作而非DML操作，truncate后不带条件。
    - 表记录太大检索较慢
      - 可在适当场合选择分区表
      - 需求访问BLOCK的个数越少越好
      - Oracle提供了两种技术减少访问路径
        索引技术
        
        分区技术
    - 索引回表读开销很大
      - 可在适当场合选择索引组织表
    - 即便有序插入也难以有序读出
      - 可在适当场合选择簇表
- 全局临时表
  - 类型
    - 会话全局临时表
      - 基于SESSION
        on commit preserve rows
    - 事务全局临时表
      - 基于事务
        on commit delete rows
  - DML操作日志表及产生的情况
    - 插入
    - 更新
    - 删除
  - 优点
    - 高效删除
      - 基于SESSION
        退出SESSION，记录就删除
      - 基于事务
        COMMIT或退出SESSION，记录就删除
    - 产生日志少
      - 无论是插入更新还是删除，操作普通表产生的日志都比全局临时表要多。
    - 不同Session独立，不产生锁
      - 避免锁，利于并行
  - 缺点
    - 语法特别
    - 数据无法得到有效的保护
- 分区表
  - 分区表原理及类型
    - 原理
      - 不同分区，在不同的SEGMENT
      - 分区表会产生多个SEGMENT，而普通表仅有一个SEGMENT。
      - 化整为零，将大对象切割成多个小对象，从而使得指定的小对象定位到数据，最终减少访问路径，尽量少做事解决问题。
    - 类型
      - RANGE分区
        最常用，范围分区：range_part_tab
        partition by range
      - LIST分区
        列表分区：list_part_tab
        partition by list
      - HASH分区
        散列分区：hash_part_tab
        partition by hash
        
        缺点
        系统自行创建分区名称，无法将指定数据到指定的分区。
        
        优点
        将数据根据一定的HASH算法，均匀分布到不同分区，避免查询数据集中，改善IO。可精确匹配，无法范围扫描。
      - 组合分区
        Oracle 11g以前的版本只支持range_list和range_hash组合
        
        range_list_part_tab
  - 分区索引
    - 全局索引
      - 对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
  - 缺点
    - 语法复杂
    - 分区过多，对系统有一定影响
- 索引组织表
  - 最大的特点是表就是索引，索引就是表，这是一种特别的设计，无需访问表
  - 优点
    - 表就是索引，可以避免回表
  - 缺点
    - 语法复杂
    - 更新开销较大
- 簇表
  - 优点
    - 可以减少或避免排序
  - 缺点
    - 语法复杂
    - 更新开销大
此文章为个人做的笔记，如有写的不对或你在表设计方面还有什么高见，评论区见