Oracle 表分区与优化
一. 分区
Oracle 数据库是最早提出物理表分区的数据库,分区的功能是由Oracle8.0的版本中推出的
作用:
(1)提高查询效率
(2)提高应用程序的可用性
查看当前用户所有的主分区与子分区
select * from user_tba_partitions;
select * from user_tba_subpartitions;
表分区的分类:
(1)范围分区:根据指定字段的值的范围将数据分成多个分区,在创建表的时候分区
关键字:range
语法:
create table 表名(
列名 数据类型;
...
) partition by range(列名)(
partition 分区名1 values less than (上限) tablespace 表空间名1,
partition 分区名2 values less than (上限) tablespace 表空间名2,
partition 分区名3 values less than (上限) tablespace 表空间名3,
...
);
注意:分区名不能相同,不包括上限,范围分区使用的字段一般是时间类型和数字类型,可以用maxvalue来查找
使用:select * from 表名 partition(分区名);
(2)列表分区:如果表中的某一些字段的值是固定的并且可以枚举出来则可以使用列表分区
关键字:list
语法:
create table 表名(
列名 数据类型,
...
) partition by list(列名)(
partition 分区名1 values (值1,值2,值3,....) tablespace 表空间名1,
partition 分区名2 values (值1,值2,值3,....) tablespace 表空间名2,
partition 分区名3 values (值1,值2,值3,....) tablespace 表空间名3,
...
);
使用:select * from 表名 partition(分区名);
(3)散列分区:hash分区,根据指定字段的值的hash码值对分区的数量进行取余,余数就代表所在的分区
关键字:hash
语法:
create table 表名(
列名 数据类型,
...
) partition by hash(列名)(
partition 分区名1 tablespace 表空间名1,
partition 分区名2 tablespace 表空间名2,
partition 分区名3 tablespace 表空间名3,
...
);
使用:select * from 表名 partition(分区名);
在进行散列分区的时候可以让系统自动设置分区的名称
语法:
create table 表名(
列名 数据类型,
...
) partition by hash(列名) partitions n store in(表空间名1,表空间名2,表空间名3,...)
使用:select * from 表名 partition(分区名);
(4)混合分区:其实就是将以上的三种分区进行自由组合
常见的组合方式:
范围----范围
列表----范围
范围----散列
范围----列表
例子:
列表----散列
create table 表名(
列名 数据类型,
...
) partition by list(列名) subpartition by hash(列名)(
partition 主分区名1 values (值1,值2,值3,....) tablespace 表空间名1(
subpartition 子分区名11,
subpartition 子分区名12,
...
),
partition 主分区名2 values (值1,值2,值3,....) tablespace 表空间名2(
subpartition 子分区名21,
subpartition 子分区名22,
...
),
partition 主分区名3 values (值1,值2,值3,....) tablespace 表空间名3(
subpartition 子分区名31,
subpartition 子分区名32,
...
)
...
);
使用:select * from 表名 partition(主分区名);
select * from 表名 subpartitions(子分区名);
(5)间隔分区:是 Oracle 数据库11g版本中添加的新功能,间隔分区还是范围分区,只是会根据添加数据来字自动创建分区,间隔分区必须先存在一个分区
关键字:interval
create table 表名(
列名 数据类型;
...
) partition by range(列名) interval(时间间隔函数)(
partition 分区名1 values less than (上限) tablespace 表空间名1,
...
);
时间间隔函数:
1. numtoyminterval(1,'year'|'month') 根据年份或者月份进行间隔
2. numtodsinterval(1,'day|hour|minute|seconds')根据天,小时,分钟,秒进行间隔
(6)数据库表分区的维护:
1. 新增分区:新增的分区必须要在现有的分区之外
新增主分区:alter table 表名 add partition 分区名 values less than(上限)|values(值) tablespace 表空间名
新增子分区:alter table 表名 modify partition 主分区名 add subpartition 子分区名 values less than(上限)|values(值) tablespace 表空间名
2. 删除分区:
如果删除了某一个分区,则分区中的数据也会消失。
分区表中最少要存在一个分区,如果要删除最后一个分区则只能删除表
语法:
alter table 表名 drop partition 主分区名
alter table 表名 drop subpartition 子分区名
3. 合并分区:在进行合并的时候不能将高分区合并到低分区,只能将低分区合并到高分区
语法:
alter table 表名 merge partitions 分区名1,分区名2 into partition 分区名1;
注意:散列分区不支持新增,删除,合并分区
二. 优化
1. SQL 语句的优化
(1)在查询表中的所有字段的时候尽量避免使用*
(2)在删除表中的所有数据的时候尽量使用truncate来替换delete
(3)在编写SQL语句的时候,在保证数据完整性的情况下尽量多使用commit
(4)合理使用in和exists
如果子查询表的数据量比较小则建议使用in
如果外查询表的数据量比较小建议使用exists
(5)合理编写 where 后面的过滤条件的顺序
因为 sql 语句中 where 语句后面的过滤条件是从后往前执行,所以建议将过滤掉多的一些数据的过滤条件写在后面
(6)避免进行全表扫描
以下情况会进行全表扫描:
1. 在查询的时候没有where条件
2. 在查询的时候使用like关键字
3. 没有提供任何索引
4. 虽然表中有索引,但是索引失效
失效情况:(1)在查询的时候没有根据索引字段过滤
(2)如果在索引字段上使用is null 或者 is not null
(3)如果在where条件中索引列上使用了函数(除非创建的索引就是基于该函数的索引)
(4)如果在索引字段上使用了一些不等式的运算符也会导致索引失效
(7)合理使用索引
1. 不建议在小表上创建索引
2. 如果某一个字段经常会出现在 where 语句后面,则可以在该字段上创建索引
3. 限制表上索引的个数
4. 如果表中的某一些字段要经常进行排序操作,可以创建索引
5. 一般在表进行关联的时候提高关联效率,会在关联的字段上创建索引
6. 如果一个表经常进行DML操作,很少查询,则不建议创建索引
7. 如果索引和表放在同一个表空间,则方便管理,如果他们放在不同的表空间,查询效率会比较高
三. 执行计划的
使用F5快捷键来查询一条sql语句的执行计划
执行计划中常用的字段:
1. rows(基数):Oracle 预估最后返回的行数
2. Bytes(字节):Oracle 执行该步骤之后返回的字节数
3. cost(耗费):主要是预估CPU耗费,理论上CPU耗费越小越好(该值和实际情况有一定出入)
4. time(时间):Oracle预估当前的操作所花费的时间
主要查看的内容:
1. 表扫描方式:
(1)table access full:全表扫描,数据量太大时不建议使用,除非要查询的数据超过了全表数据的20%以上
(2)table access by rowID:通过rowID来扫描表中的数据
(3)table access by index scan:通过索引来扫描
2. 表连接方式:
(1)sort merge join(排序—合并连接):先将关联的表按照关联的字段进行排序,然后生成两张临时表,随机取一张表中的一条数据和
优点:比较适合关联字段上有索引的一些表连接
缺点:由于在连接之前需要进行排序,所以消耗比较大,内存需求也大
(2)hash join(哈希连接):先选择一张表作为驱动表,然后将驱动表中的关联字段的hash值以及表中的其他数据保存到缓存中,然后再计算另外一张表中关联字段的hash值,通过hash值进行匹配关联
优点: 性能比较好,可以大大减少关联次数
缺点:对内存的要求比较高,所以一般将小表作为驱动表
(3)nested loop join(嵌套循环连接):先从驱动表中拿出一条数据,然后和关联表进行一个一个匹配,如果匹配到了则将结果放到缓存中
优点:内存占用比较少
缺点:由于不停的从硬盘中读取数据,所以效率比较低
修改执行计划:
hints 是 Oracle 提供的一种特殊的sql语法,允许在sql语句中插入一些相关的语法来改变sql语句的执行方式
常见的hints语法:*和+之间不能有空格,起了别名必须使用别名
1. /*+ use_parellel(表名,并行数)*/ 设置表查询的时候并行数量
2. /*+ use_full(表名)*/ 指定表进行全表扫描
3. /*+ use_hash(表名,表名)*/ 设置表的连接方式为hash join
4. /*+ use_nl(表名,表名)*/ 设置表的连接方式为nested loop join
5. /*+ use_merge(表名,表名)*/ 设置表的连接方式为sort merge join
6. /*+ ordered*/ 设置表的连接顺序,代表表连接的顺序就是表书写的顺序
7. /*+ index(索引名,......)*/ 设置表在扫描的时候使用索引扫描,如果设置多个索引则系统默认或选择最优的索引,如果没有指定的索引则会自动忽略
使用: selelct [执行方式] * from 表名