Oracle数据库表分区

您所在的位置：网站首页 › oracle删除某个分区的数据 › Oracle数据库表分区

Oracle数据库表分区

2023-11-01 19:25| 来源: 网络整理| 查看: 265

一、Oracle数据库表分区概念和理解 1.1、已经存在的表没有方法可以直接转化为分区表。 1.2、不在分区字段上建立分区索引，在别的字段上建立索引相当于全局索引。效率低。 1.3、表空间概念：　　表空间指的是是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。 1.4、分区表概念当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。二、Oracle数据库表分区的几种类型 2.1、范围分区(Range分区) 范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期 1）每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。 2) 所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。 3) 在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。例子按时间划分

1 CREATE TABLE ORDER_ACTIVITIES 2 ( 3 ORDER_ID NUMBER(7) NOT NULL, 4 ORDER_DATE DATE, 5 TOTAL_AMOUNT NUMBER, 6 CUSTOTMER_ID NUMBER(7), 7 PAID CHAR(1) 8 ) 9 PARTITION BY RANGE (ORDER_DATE) 10 ( 11 PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')), 12 PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) , 13 PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) 14 );

例子MAXVALUE

1 CREATE TABLE RangeTable 2 ( 3 idd INT PRIMARY KEY , 4 iNAME VARCHAR(10), 5 grade INT 6 ) 7 PARTITION BY RANGE (grade) 8 ( 9 PARTITION part1 VALUES LESS THEN (1000) , 10 PARTITION part2 VALUES LESS THEN (MAXVALUE) 11 );

三、分区的一些操作 3.1、查看分区情况

1 select * from user_tab_partitions where table_name ='tableName';

3.2、查看分区数据

1 select * from tablename partiton(p1);

3.3、修改分区

1 添加：alter table tablename add partition p4 values less than(value); 2 删除：alter table tablename drop partiton p4; 3 截断分区截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。 4 alter table tablename truncate partiton p2;

3.4、合并分区合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：

1 ALTER TABLE TABLENAME MERGE PARTITIONS P1,P2 INTO PARTITION P2;

3.5、拆分分区拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

1 ALTER TABLE TABLENAME SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

更新数据是操作时不可以跨分区操作，会出现错误，需要设置可移动的分区才能进行跨分区查询。

1 alter table tablename enable row movement;

四、Oracle11g数据库新特性之间隔分区在Oracle10g中，没有定义间隔分区，只能通过范围分区实现间隔分区功能，如果要实现自动创建分区，只能通过创建JOB或者scheduler来实现；而在11g中，Oracle直接提供了间隔分区功能，大大简化了间隔分区的实现。语法主要通过INTERVAL关键字来实现

1 CREATE TABLE interval_sales 2 ( prod_id NUMBER(6) 3 , cust_id NUMBER 4 , time_id DATE 5 , channel_id CHAR(1) 6 , promo_id NUMBER(6) 7 , quantity_sold NUMBER(3) 8 , amount_sold NUMBER(10,2) 9 ) 10 PARTITION BY RANGE (time_id) 11 INTERVAL(NUMTOYMINTERVAL(1, \'MONTH\')) 12 ( PARTITION p0 VALUES LESS THAN (TO_DATE(\'1-1-2008\', \'DD-MM-YYYY\')), 13 PARTITION p1 VALUES LESS THAN (TO_DATE(\'1-1-2009\', \'DD-MM-YYYY\')), 14 PARTITION p2 VALUES LESS THAN (TO_DATE(\'1-7-2009\', \'DD-MM-YYYY\')), 15 PARTITION p3 VALUES LESS THAN (TO_DATE(\'1-1-2010\', \'DD-MM-YYYY\')) )

上述sql语句创建了4个不等区间的分区，分别是2008年1月1日之前的所有数据、08年到09年的所有数据、09年上半年的所有数据、09年下半年的所有数据；同时，它也制定了在2010年1月1日之后，每个月单独创建一个分区。需要注意一点，间隔分区的键值只能是一个列，并且该列只能是Date类型或者number类型。五、Oracle数据库表分区索引简单介绍分区之后虽然可以提高查询效率，但也仅仅是提供了数据的范围，所以我们在有必要的情况下，需要在分区内建立索引，进一步提高效率。分区索引分为两类。一类叫做local。一类叫做global。 local：每个分区上都建立索引(本地索引) global：一种是在全局上建立索引，这种方式分布分区都一样，一般不使用。

前缀索引:还有一种就是自定义数据区间的索引。也叫作前缀索引，这个是非常有意义的，自定义区间值必须使用MAXVALUE。另外一点是在分区上建立的索引必须是分区字段列。本地索引

1 create index grade_index on tablename(grade) local

例子:在p1和p2和p3三个分区上分别建立索引

1 create index grade_index on studentgrade(grade) 2 local --根据表分区创建本地索引分区 3 ( 4 partition p1, 5 partition p2, 6 partition p3 7 );

查看分区索引

1 select * from user_ind_partitions;

全局索引

1 create index idxname on tablename (field) global;

前缀索引

1 create index idxname on tablename(field) global partition by range(field){ 2 partition p1 values less than(value), 3 partition p2 values less than(maxvalue) 4 };

【本文地址】

Oracle数据库表分区

Oracle数据库表分区

今日新闻

推荐新闻