oracle常用表设计优化

前言

本文章是我学习oracle优化的笔记，我看的课程放在这里了：
oracle优化

表设计优化:

oracle常用优化方法

由于内存优化与程序无关，所以不做介绍。

表空间

数据库的逻辑划分，一个表空间只属于一个数据库，所有的数据库对象都存放在指定的表空间中，但主要存放的是表，所以成为表空间。
作用：
1）控制用户所占用的表空间配额
2）控制数据库所占用磁盘空间
3）灵活放置表空间，提高数据哭的输入输出性能
4）控制数据库部分数据的可用性
5）日志文件和数据文件分开放，提高数据库安全性

分区表

分区表是将大表的数据分成称为分区的许多小的子集。
物理上分离，逻辑上统一，均衡io，所辖访问范围，易于管理。

作用：
1）改善查询性能：仅搜索关心的分区，提高检索速度
2）增强可用性：数据分散到哥哥愤怒，减少数据损坏的可能性、
3）维护方便：可以对单独的分区进行回复和备份
4）性能改善：可以将分区映射到不同物理磁盘，分散I/O

举例：
分区表就像一栋楼，分区就像是楼层，如果能知道要查的数据在几层，就能很快找到数据。
反之，如果没有分区就得按照索引或者全表扫描去找，必然很慢，数据量会完全不同。
分区表市分局某个字段值做区分，如果sql中引用了分区字段，且查询的量在一定范围（比如在一个分区或两个分区内获得数据），sql还是会使用分区消减的，不会扫描全表，整个过程是oracle内部算法完成的。

什么时候使用分区表？
1）表的大小超过2GB
2）表中包含历史数据，新的数据被增加在新的分区中
缺点：
已存在的表没有方法可以直接转化为分区表，不多oracle提供了在线重定义表的功能

索引

索引是一种数据库对象，可快速访问表中的特定信息。对表中一列或者多列的值进行排序的一种结构。
简单讲：索引是一种排好序的能够快速查找的数据库对象

优点：
1）提高查询效率；唯一性索引可以保证数据的唯一性
缺点：
1）创建和维护索引需要花费额外的时间和空间（索引的存在是一种空间换时间的策略，索引的存储是需要花费空间的）
2）降低数据的维护速度，例如对表进行增、删、改（索引会随着表的改变而随之改变，所以对于经常做增删改的字段上建立索引会影响速度，在这种情况下就不建议在该字段上建立索引）

索引的选择

那些情况下应该建立索引？
1）经常需要被查找的列（索引本身就是为了提高查询效率）
2）作为主键的列和经常在表之间连接的列
3）经常需要排序的列和经常使用where字句中的列（where过滤使用索引，提高效率）
那些情况不应该建索引？
1）很少使用的列和数据之很少的列（本身很少使用到的列创建索引会存在浪费空间的问题）
2）定义为blob的列（blob类型：图片类型）
3）修改性能远大于检索性能时

索引失效

有些情况会存在索引失效：
1）使用不等域操作符（<> !=）
2）is null和is not null
3）使用函数
4）不匹配日的数据类型

小知识：通过explain plan for sql语句，可以展示出来本剧sql使用索引的情况，通过展示出来的参数能够体现sql执行效率的高低。


使用！=的情况，导致索引失效：

使用函数导致索引失效：


使用不匹配数据类型：
示例：查找的字段是字符类型，但是在写的时候不使用单引号，会导致索引失效

索引的类型：

关于主键索引，唯一索引，唯一约束：
1）主键约束和唯一约束均会饮食创建同名的唯一索引，当主键约束或者唯一约束失效时，隐士创建的唯一索引会被删除；
2）主键约束要求列值非空，而唯一约束和唯一索引不要求列值为空；
3）相同字段序列不允许重复创建索引；

物化视图

物化视图的目的是为了提高查询效率，增加和删除物化视图不会影响应用程序中sql语句的正确性和有效性；
物化视图需要占用存储空间（空间换时间的策略），当基表发生变化时，物化视图也应当刷新。
物化视图的创建：
create materialized view 视图名 表空间
build deferred-- 延迟刷新不立即刷新
【refresh fore】 – 如果可以快速创建则进行快速刷新，否则完全刷新
【on demand】-- 按照制定方式刷新
start with to_date()–第一次刷新时间
next TRUNC()-- 刷新时间间隔
as select * from 表名;

普通视图：
视图是基于一个表或多个表视图的虚拟表，本身布包含数据。本质是存储在数据字典中的一条select语句。
优点：
1）使用视图，定制用户数据，聚焦特定数据

2）使用视图能能简化数据操作

3）使用视图，基表中的数据有一定的安全性

4）可以合并分离的数据，创建区分视图