数据库优化之：ocacle的分区表你确定不用一下？

问题：某表的数据以每天10万+的速度增加，且数据不会经常修改（甚至不修改只查），查询及其缓慢。

思路：oracle数据库有分区表的概念，使用分区表与索引结合的方式解决

分区表的创建

分区方式可以分为：范围分区、列表分区、哈希分区（散列分区）、组合分区

11g版本加入了可以自动分区的功能

最常用的就是范围分区，下边是根据时间自动来分区的实例

• 创建分区表

在创建表的后边加上下边的sql

-- NUMTODSINTERVAL是一个函数，后边的参数可以为'month'、'year'、'day' 方法分别为 年月：NUMTOYMINTERVAL 日：NUMTODSINTERVAL
PARTITION BY RANGE ("TIME") INTERVAL (NUMTODSINTERVAL (7, 'day'))
-- 小于设置的时间的数据会放到part_t01分区里
(partition part_t01 values less than(to_date('2020-08-10', 'yyyy-mm-dd')));

• 普通表转化分区表

备份表，然后新建这个表，拷贝数据，删除备份表。

-- 1. 重命名 name是要搞的表 temp是修改名称的临时表
alter table name rename to temp;

-- 2. 创建表
create table name
(
   字段
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t1 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

-- 3. 创建主键
alter table test_part add constraint test_part_pk_1 primary key (ID) using INDEX;

-- 4. 将 temp 表里的数据迁移到 name 表中
insert into name select * from temp;

-- 5. 删除老表
drop table temp;

• 分区查看

  select * from user_tab_partitions where table_name=‘表名’;

• 分区表的索引

分为本地索引与全局索引

1. 本地索引

   本地索引需要在创建索引语句后加关键字LOCAL

   CREATE INDEX "IDX_TEST_CREATE_TIME" ON "TEST" ("CREATE_TIME" DESC) LOCAL;
   

2. 全局索引

   全局索引需要在创建索引语句后加关键字GLOBAL

   CREATE INDEX "IDX_TEST_CREATE_TIME" ON "TEST" ("CREATE_TIME" DESC) GLOBAL;
   

两种索引的选择

• 本地索引：当你知道你要查的数据就在你的分区里，这时候请使用本地索引
• 全局索引：当要查的数据存在跨分区的时候，这时候的全局索引会快很多

分区表的查询问题

1. 当你要查询的条件就是分区的键的时候（简单来说就是根据要查的条件分的区），这时候查询不需要带分区条件，会自动到所在分区查

2. 当你条件不是键的时候，你需要在你的查询条件上加一个分区条件

   select * from 表名 PARTITION(分区名)
   

   这里就有个大问题了，如果我是自动创建的分区，我并不知道分区的名称怎么办？