纵有疾风起
人生不言弃

oracle分区技术提高查询效率


概述:

当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。

下面介绍如何使用分区增加查询效率

range分区:就是区域分区

CREATE TABLE SALE(    PRODUCT_ID VARCHAR2(5),    SALE_COUNT NUMBER(10,2))PARTITION BY RANGE (SALE_COUNT)(    PARTITION P1 VALUES LESS THAN (1000) TABLESPACE CUS_TS01,    PARTITION P2 VALUES LESS THAN (2000) TABLESPACE CUS_TS02)

查看分区语法:

  select * from user_tab_partitions;  –查询所有分区情况,可以接条件where table_name=’sale’查看分区表结构

  select * from sale partition(p1);  –查询某表的某一分区数据

分区后,新增数据的SALE_COUNT字段如果小于1000就存储到P1分区中,如果1000到2000存储到P2分区中。

但是这时如果我们新增的一条数据的SALE_COUNT字段值大于2000,将无法存储到表中。

我们可以扩展分区,语法如下:

  alter table sale add partition p4 values less than(maxvalue); –大于2000的都会存到此分区中,当然也可以增加更多的分区

同时可以删除分区,语法如下:

  alter table sale drop partition p4; –注意:删除分区会把分区内已有的数据同时删除

但还存在一个问题,如果现在update分区p1中的SALE_COUNT值为1500,是不会成功的,需要在update前增加以下语句:

  alter table sale enable row movement; –使其row能移动

这样再update就可以成功了

分区索引

分区之后虽然可以提高查询的效率,但也仅仅是提高了数据的范围,所以我们在有必要的情况下,需要建立分区索引,从而进一步提高效率。

分区索引大体上分为两大类,一类叫做local,一类叫做global。

local:在每个分区上建立索引(一般采用这种方式)

global:一种在全局上建立索引,这种方式分不分区都一样,一般不使用

下面进行语法演示:

注意:分区上建立的索引一定是分区字段

create index idx_count on sale(sale_count) local;–建立分区索引,在sale表的每个分区都建立了索引

select * from user_ind_partitions;–查询所有分区索引情况

全局索引global写法就是把上面的local替换成global,但不会使用

 

 

有些时候,如果你分区分为0~1000,1000~2000,这时如果说0~1500这个范围内的数据会被频繁查询,1500之后查询很少,那么就可以使用这种自定义的全局索引方式对0~1500建立索引,之后的设置maxvalue即可,语法与分区语法相似

global自定义全局索引方式(前缀索引):

create index idxname on table(field) global

  partition by range(field) 

  (

    partition p1 values less than(value),  …….

    partition pN values less than(maxvalue)

  );

 

其他分区介绍

1.hash分区

hash分区实现均匀的负载值分配,增加hash分区可以重新分布数据,简单理解就是分区直接平均分配

CREATE TABLE SALE(    PRODUCT_ID VARCHAR2(5),    SALE_COUNT NUMBER(10,2))PARTITION BY HASH (PRODUCT_ID)(    PARTITION P1,    PARTITION P2 )

2.list分区

该分区的特点是某列的值只有几个,基于这样的特点我们可以采用列表分区。

CREATE  TABLE  ListTable(    id    INT  PRIMARY  KEY ,    name  VARCHAR (20),    area  VARCHAR (10))PARTITION  BY  LIST (area)(    PARTITION  part1 VALUES ('guangdong','beijing') TABLESPACE  Part1_tb,    PARTITION  part2 VALUES ('shanghai','nanjing')  TABLESPACE  Part2_tb);

3.复合分区(用的不多)

create table student(    sno number,    sname varchar2(10))partition by range (sno)subpartition by hash (sname)subpartitions 4(    partition p1 values less than(1000),    partition p2 values less than(2000),    partition p3 values less than(maxvalue));

复合分区首先大体上分为三个分区p1,p2,p3,然后每一个分区内部再进行hash分区,分为4份

查询子分区的语句:select * from user_tab_subpartitions where table_name=’student’;

4.间隔分区(工作中常用)

是一种分区自动化的分区,可以指定时间间隔进行分区,这是oracle11g的新特性,实际工作中很常用。实际上是由range分区引申的,最终实现了range分区的自动化

create table interval_sale(sid int,sdate timestamp)partition by range(sdate)interval (numtoyminterval(1,'MONTH'))(partition p1 values less than (TIMESTAMP '2017-11-12 00:00:00.00'))

指定时间之前建立一个分区,之后每隔一个月建立一个分区

问题:如果我们drop掉了表,那么这个表的分区还存在吗?

答案是存在的,oracle提供了很强大的数据恢复功能,有一个类似回收站的机制,删除表后,分区以特殊的形式仍然存在user_tab_partitions中,使用purge recyclebin语法后,会清空回收站,使用flashback table 表名 to before drop语句可以恢复删除的表。

 

文章转载于:https://www.cnblogs.com/lm970585581/p/7874139.html

原著是一个有趣的人,若有侵权,请通知删除

未经允许不得转载:起风网 » oracle分区技术提高查询效率

分享到: 生成海报
avatar

评论 抢沙发

评论前必须登录!

立即登录   注册

切换注册

登录

忘记密码 ?

切换登录

注册

我们将发送一封验证邮件至你的邮箱, 请正确填写以完成账号注册和激活